查询复杂sql的表的大小

1.先explain plan for 目标sql：

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可以查询出相关表的大小：

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_nameUNION ----table in the indexSELECT 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;