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_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;
文章名称：查询复杂sql的表的大小
文章转载：
http://cdkjz.cn/article/pjppod.html
