查询复杂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_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://myzitong.com/article/pjppod.html