概要
前回のSimple View Mergingに引き続き、今回はComplex View Mergingについて記載します。
変換の目的についてはSimple/Complexで大きく変わらないため今回は記載しません。
実行例
Complex view merging
WITH agg_q AS ( SELECT /*+ merge */ s.cust_id, s.prod_id, SUM (s.amount_sold) total_amt_sold FROM sh.sales s GROUP BY s.cust_id, s.prod_id) SELECT cust_id ,c.cust_first_name ,c.cust_last_name ,c.cust_email ,p.prod_name ,agg_q.total_amt_sold FROM agg_q JOIN sh.customers c USING (cust_id) JOIN sh.countries co USING (country_id) JOIN sh.products p USING (prod_id) WHERE co.country_name = 'Japan' AND prod_category = 'Photo' AND total_amt_sold > 20000; Plan hash value: 2615677739 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 484 | 79860 | 951 (2)| 00:00:01 | | | |* 1 | FILTER | | | | | | | | | 2 | HASH GROUP BY | | 484 | 79860 | 951 (2)| 00:00:01 | | | |* 3 | HASH JOIN | | 9672 | 1558K| 949 (2)| 00:00:01 | | | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 14 | 826 | 3 (0)| 00:00:01 | | | |* 5 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | 1 (0)| 00:00:01 | | | |* 6 | HASH JOIN | | 48360 | 5006K| 946 (2)| 00:00:01 | | | |* 7 | HASH JOIN | | 2921 | 262K| 426 (1)| 00:00:01 | | | |* 8 | TABLE ACCESS FULL | COUNTRIES | 1 | 27 | 3 (0)| 00:00:01 | | | | 9 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 3522K| 423 (1)| 00:00:01 | | | | 10 | PARTITION RANGE ALL | | 918K| 12M| 517 (2)| 00:00:01 | 1 | 28 | | 11 | TABLE ACCESS FULL | SALES | 918K| 12M| 517 (2)| 00:00:01 | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUM("S"."AMOUNT_SOLD")>20000) 3 - access("S"."PROD_ID"="P"."PROD_ID") 5 - access("P"."PROD_CATEGORY"='Photo') 6 - access("S"."CUST_ID"="C"."CUST_ID") 7 - access("C"."COUNTRY_ID"="CO"."COUNTRY_ID") 8 - filter("CO"."COUNTRY_NAME"='Japan')
変換されていない実行計画(NO_MERGE)
WITH agg_q AS ( SELECT /* no_merge */ s.cust_id, s.prod_id, SUM (s.amount_sold) total_amt_sold FROM sh.sales s GROUP BY s.cust_id, s.prod_id) SELECT cust_id ,c.cust_first_name ,c.cust_last_name ,c.cust_email ,p.prod_name ,agg_q.total_amt_sold FROM agg_q JOIN sh.customers c USING (cust_id) JOIN sh.countries co USING (country_id) JOIN sh.products p USING (prod_id) WHERE co.country_name = 'Japan' AND prod_category = 'Photo' AND total_amt_sold > 20000; Plan hash value: 2752099247 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 154 | 545 (6)| 00:00:01 | | | | 1 | NESTED LOOPS | | 1 | 154 | 545 (6)| 00:00:01 | | | | 2 | NESTED LOOPS | | 1 | 154 | 545 (6)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 107 | 544 (6)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 92 | 543 (6)| 00:00:01 | | | | 5 | VIEW | | 1 | 39 | 542 (6)| 00:00:01 | | | |* 6 | FILTER | | | | | | | | | 7 | HASH GROUP BY | | 1 | 14 | 542 (6)| 00:00:01 | | | | 8 | PARTITION RANGE ALL | | 918K| 12M| 517 (2)| 00:00:01 | 1 | 28 | | 9 | TABLE ACCESS FULL | SALES | 918K| 12M| 517 (2)| 00:00:01 | 1 | 28 | | 10 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 53 | 1 (0)| 00:00:01 | | | |* 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | | |* 12 | TABLE ACCESS BY INDEX ROWID | COUNTRIES | 1 | 15 | 1 (0)| 00:00:01 | | | |* 13 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | | 0 (0)| 00:00:01 | | | |* 14 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| 00:00:01 | | | |* 15 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 1 | 47 | 1 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - filter(SUM("S"."AMOUNT_SOLD")>20000) 11 - access("AGG_Q"."CUST_ID"="C"."CUST_ID") 12 - filter("CO"."COUNTRY_NAME"='Japan') 13 - access("C"."COUNTRY_ID"="CO"."COUNTRY_ID") 14 - access("AGG_Q"."PROD_ID"="P"."PROD_ID") 15 - filter("P"."PROD_CATEGORY"='Photo')
最適化後のSQL(10053トレース)
SELECT "C"."CUST_ID" "CUST_ID", "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME", "C"."CUST_LAST_NAME" "CUST_LAST_NAME", "C"."CUST_EMAIL" "CUST_EMAIL", "P"."PROD_NAME" "PROD_NAME", SUM("S"."AMOUNT_SOLD") "TOTAL_AMT_SOLD" FROM "SH"."SALES" "S", "SH"."CUSTOMERS" "C", "SH"."COUNTRIES" "CO", "SH"."PRODUCTS" "P" WHERE "CO"."COUNTRY_NAME" = 'Japan' AND "P"."PROD_CATEGORY" = 'Photo' AND "S"."PROD_ID" = "P"."PROD_ID" AND "C"."COUNTRY_ID" = "CO"."COUNTRY_ID" AND "S"."CUST_ID" = "C"."CUST_ID" GROUP BY "S"."CUST_ID", "S"."PROD_ID", "P".ROWID, "CO".ROWID, "C".ROWID, "P"."PROD_NAME", "C"."CUST_EMAIL", "C"."CUST_LAST_NAME", "C"."CUST_FIRST_NAME", "C"."CUST_ID" HAVING SUM("S"."AMOUNT_SOLD") > 20000
関連パラメータ
_complex_view_merging
optimizer_secure_view_merging
参考資料
Expert Oracle SQL
Expert Oracle SQL: Optimization, Deployment, and Statistics
- 作者: Tony Hasler
- 出版社/メーカー: Apress
- 発売日: 2014/06/24
- メディア: ペーパーバック
- この商品を含むブログを見る
Oracle(R) Database SQLチューニング・ガイド 18c
