概要

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