您好,登錄后才能下訂單哦!
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可以查詢出相關(guān)表的大?。?/pre>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;
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。