GAGA LIFE.

DBAブログ

Oracle 問合せ変換① OR拡張

概要

Oracleの問合せ変換の中の「OR拡張(Or Expansion)」について記載します。

目的

WHERE句にORを含むSQLを1つまたは複数のUNION ALL集合演算子を使用する複合問合せに変換することで、追加の索引アクセス・パスを有効にするために適用される変換

実行例

-- OR拡張クエリ

SELECT
  SUM (amount_sold) cnt
  FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Rows  | Bytes | Cost (%CPU)|    Time | Pstart | Pstop  |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |     1 |    13 |     469 (0)| 00:00:01 |       |        |
|   1 |  SORT AGGREGATE                                |                 |     1 |    13 |            |          |       |        |
|   2 |   VIEW                                         | VW_ORE_3E51BB93 | 13941 |   176K|     469 (0)| 00:00:01 |       |        |
|   3 |    UNION-ALL                                   |                 |       |       |            |          |       |        |
|   4 |     NESTED LOOPS                               |                 |  1188 | 24948 |      24 (0)| 00:00:01 |       |        |
|   5 |      PARTITION RANGE SINGLE                    |                 |  1188 | 20196 |      24 (0)| 00:00:01 |     5 |      5 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES           |  1188 | 20196 |      24 (0)| 00:00:01 |     5 |      5 |
|   7 |        BITMAP CONVERSION TO ROWIDS             |                 |       |       |            |          |       |        |
|*  8 |         BITMAP INDEX SINGLE VALUE              | SALES_TIME_BIX  |       |       |            |          |     5 |      5 |
|*  9 |      INDEX UNIQUE SCAN                         | PRODUCTS_PK     | 1     |     4 |       0 (0)| 00:00:01 |       |        |
|  10 |     NESTED LOOPS                               |                 | 12753 |   585K|     445 (0)| 00:00:01 |       |        |
|  11 |      NESTED LOOPS                              |                 | 12753 |   585K|     445 (0)| 00:00:01 |       |        |
|* 12 |       TABLE ACCESS FULL                        | PRODUCTS        |     1 |    30 |       3 (0)| 00:00:01 |       |        |
|  13 |       PARTITION RANGE ALL                      |                 |       |       |            |          |     1 |     28 |
|  14 |        BITMAP CONVERSION TO ROWIDS             |                 |       |       |            |          |       |        |
|* 15 |         BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX  |       |       |            |          |     1 |     28 |
|* 16 |       TABLE ACCESS BY LOCAL INDEX ROWID        | SALES           | 12753 |   211K|     445 (0)| 00:00:01 |     1 |      1 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 8 - access("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   9 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  12 - filter("PRODUCTS"."PROD_NAME"='Y Box')
  15 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  16 - filter(LNNVL("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

★12cR2から機能拡張され、実行計画上も「UNION-ALL」と出力されるようになりました。
   12cR1までは、「CONCATENATION(連結)演算子」と出力されます(後述)

-- 変換(OR拡張)されていない実行計画

SELECT /*+ no_expand */
  SUM (amount_sold) cnt
  FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';

--------------------------------------------------------------------------------------------------
| Id | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |          |     1 |    47 |     523 (2)| 00:00:01 |       |        |
|  1 |  SORT AGGREGATE       |          |     1 |    47 |            |          |       |        |
|* 2 |   HASH JOIN           |          | 13382 |   614K|     523 (2)| 00:00:01 |       |        |
|  3 |    TABLE ACCESS FULL  | PRODUCTS |    72 |  2160 |       3 (0)| 00:00:01 |       |        |
|  4 |    PARTITION RANGE ALL|          |   918K|    14M|     517 (2)| 00:00:01 |     1 |     28 |
|  5 |     TABLE ACCESS FULL | SALES    |   918K|    14M|     517 (2)| 00:00:01 |     1 |     28 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
       filter("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
             OR "PRODUCTS"."PROD_NAME"='Y Box')

-- USE_CONCATヒント付き

SELECT /*+ use_concat */
  SUM (amount_sold) cnt
  FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |     1 |    47 |     472 (0)| 00:00:01 |       |        |
|   1 |  SORT AGGREGATE                               |                |     1 |    47 |            |          |       |        |
|   2 |   CONCATENATION                               |                |       |       |            |          |       |        |
|   3 |    NESTED LOOPS                               |                | 12762 |   585K|     445 (0)| 00:00:01 |       |        |
|   4 |     NESTED LOOPS                              |                | 12762 |   585K|     445 (0)| 00:00:01 |       |        |
|*  5 |      TABLE ACCESS FULL                        | PRODUCTS       |     1 |    30 |       3 (0)| 00:00:01 |       |        |
|   6 |      PARTITION RANGE ALL                      |                |       |       |            |          |     1 |     28 |
|   7 |       BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |       |        |
|*  8 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |       |       |            |          |     1 |     28 |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID         | SALES          | 12762 |   211K|     445 (0)| 00:00:01 |     1 |      1 |
|* 10 |    HASH JOIN                                  |                |  1188 | 55836 |      27 (0)| 00:00:01 |       |        |
|* 11 |     TABLE ACCESS FULL                         | PRODUCTS       |    71 |  2130 |       3 (0)| 00:00:01 |       |        |
|  12 |     PARTITION RANGE SINGLE                    |                |  1188 | 20196 |      24 (0)| 00:00:01 |     5 |      5 |
|  13 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |  1188 | 20196 |      24 (0)| 00:00:01 |     5 |      5 |
|  14 |       BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |       |        |
|* 15 |       BITMAP INDEX SINGLE VALUE               | SALES_TIME_BIX |       |       |            |          |     5 |      5 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("PRODUCTS"."PROD_NAME"='Y Box')
   8 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  10 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  11 - filter(LNNVL("PRODUCTS"."PROD_NAME"='Y Box'))
  15 - access("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

★USE_CONCATヒントを使用した場合、12cR1と同様「CONCATENATION(連結)演算子」と出力されます。

最適化後のSQL(10053トレース)

SELECT
       SUM(VW_ORE_3E51BB93.ITEM_1) CNT
FROM  ((
                     SELECT
                             SALES.AMOUNT_SOLD ITEM_1
                     FROM
                             SH.PRODUCTS PRODUCTS,
                             SH.SALES SALES
                     WHERE
                             SALES.TIME_ID = TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
                     AND SALES.PROD_ID = PRODUCTS"."PROD_ID"
            ) UNION ALL(
           SELECT
                   SALES.AMOUNT_SOLD ITEM_1
           FROM
                   SH.PRODUCTS PRODUCTS,
                   SH.SALES SALES
           WHERE
                   PRODUCTS.PROD_NAME = 'Y Box'
           AND SALES.PROD_ID = PRODUCTS.PROD_ID
           AND LNNVL(
                           SALES.TIME_ID = TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
           ))) VW_ORE_3E51BB93

参考資料

Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)
https://docs.oracle.com/cd/E82638_01/TGSQL/toc.htm
http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-60-3763796-ja.html
津島博士のパフォーマンス講座 第60回 Oracle Database 12cR2で強化されたSQL処理について
Troubleshooting Oracle Performance, Second Edition
Expert Oracle SQL