1.先explain plan for 目標(biāo)sql：

成都創(chuàng)新互聯(lián)公司業(yè)務(wù)包括：成品網(wǎng)站、企業(yè)產(chǎn)品展示型網(wǎng)站建設(shè)、高端網(wǎng)站設(shè)計(jì)、電子商務(wù)型網(wǎng)站建設(shè)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)(多語(yǔ)言)、商城網(wǎng)站定制開(kāi)發(fā)、定制網(wǎng)站設(shè)計(jì)、網(wǎng)絡(luò)營(yíng)銷推廣等。效率優(yōu)先，品質(zhì)保證，用心服務(wù)是我們的核心價(jià)值觀，我們將繼續(xù)以良好的信譽(yù)為基礎(chǔ)，秉承穩(wěn)固與發(fā)展、求實(shí)與創(chuàng)新的精神，為客戶提供更全面、更優(yōu)質(zhì)的互聯(lián)網(wǎng)服務(wù)！

explain plan for WITH sales_countries AS
 (SELECT /*+ gather_plan_statistics */
   cu.cust_id, co.country_name
    FROM sh.countries co, sh.customers cu
   WHERE cu.country_id = co.country_id),
top_sales AS
 (SELECT p.prod_name,
         sc.country_name,
         s.channel_id,
         t.calendar_quarter_desc,
         s.amount_sold,
         s.quantity_sold
    FROM sh.sales s
    JOIN sh.times t
      ON t.time_id = s.time_id
    JOIN sh.customers c
      ON c.cust_id = s.cust_id
    JOIN sales_countries sc
      ON sc.cust_id = c.cust_id
    JOIN sh.products p
      ON p.prod_id = s.prod_id),
sales_rpt AS
 (SELECT prod_name product,
         country_name country,
         channel_id channel,
         substr(calendar_quarter_desc, 6, 2) quarter,
         SUM(amount_sold) amount_sold,
         SUM(quantity_sold) quantity_sold
    FROM top_sales
   GROUP BY prod_name,
            country_name,
            channel_id,
            substr(calendar_quarter_desc, 6, 2))
SELECT *
  FROM (SELECT product, channel, quarter, country, quantity_sold
          FROM sales_rpt) pivot(SUM(quantity_sold) FOR(channel, quarter) IN((5, '02') AS
                                                                            catalog_q2,
                                                                            (4, '01') AS
                                                                            internet_q1,
                                                                            (4, '04') AS
                                                                            internet_q4,
                                                                            (2, '02') AS
                                                                            partners_q2,
                                                                            (9, '03') AS
                                                                            tele_q3))
 46   ORDER BY product, country
 47  /
Explained.
Elapsed: 00:00:00.37

SQL>

2.用以下sql可以查詢出相關(guān)表的大小：

SELECT owner,

       segment_name,

       segment_type,

       SUM(bytes / 1024 / 1024) "Size(Mb)"

  FROM dba_segments

 WHERE owner IN (SELECT /*+ no_unnest */

                  object_owner

                   FROM plan_table)

   AND segment_name IN (SELECT /*+ no_unnest */

                         object_name

                          FROM plan_table)

 GROUP BY owner, segment_type, segment_name

UNION ----table in the index

SELECT owner,

       '*' || segment_name,

       segment_type,

       SUM(bytes / 1024 / 1024) "Size(Mb)"

  FROM dba_segments

 WHERE owner IN (SELECT table_owner

                   FROM dba_indexes

                  WHERE owner IN (SELECT /*+ no_unnest */

                                   object_owner

                                    FROM plan_table)

                    AND index_name IN (SELECT /*+ no_unnest */

                                        object_name

                                         FROM plan_table))

   AND segment_name IN

       (SELECT /*+ no_unnest */

         table_name

          FROM dba_indexes

         WHERE owner IN (SELECT /*+ no_unnest */

                          object_owner

                           FROM plan_table)

           AND index_name IN (SELECT /*+ no_unnest */

                               object_name

                                FROM plan_table))

 GROUP BY owner, segment_type, segment_name

 ORDER BY 3, 4;