GAGA LIFE.

DBAブログ

Oracle 問合せ変換②-2 ビュー・マージ(Complex View Merging)

概要

前回の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
https://docs.oracle.com/cd/E96517_01/tgsql/index.html
Oracle(R) Database SQLチューニング・ガイド 18c