Shulou(Shulou.com)06/01 Report--

1. Explain plan for the target sql first:

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 country 0015 00.37

SQL >

two。 You can query the size of related tables with the following 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.