联合所有与基于函数的索引

Question

我有一个大表，具有相同的基于函数的索引。当查询一个表时，查询计划使用索引。当我加入第二张桌子的时候，我会进行全面的扫描。

我找到了一个工作解决方案(与表一起工作)，但它有一些限制：

• 我不能在合并为union的x查询中使用ROWNUM
• 我不能在查询中使用管道函数。

如何克服上述局限性？

我尝试了一些提示，比如基数( CARDINALITY )，以说明记录的数量很小，而有些重写(重写，PUSH_PRED)却没有成功。我不能使用分区，因为问题在Oracle SE上。

这里显示的问题简化了我的实际问题，即：

1. 我有巨大的数据集被划分成相同的表。
2. 每个表包含不同月份的数据。
3. 我认为哪一个合并了所有潜在的表
4. 是v8.0之前在甲骨文上练习的分区风格

实际上，我可能有一些特殊的查询，并与许多不同的表联接。因此，我不能简单地将连接推入一个联盟，这将是最简单的解决方案。

以下是DDL脚本。

    -- FULL SCAN FOR IN SUBQUERY
    SELECT * FROM (
              SELECT * FROM TEST_EXPV1
    UNION ALL SELECT * FROM TEST_EXPV2
    )
    WHERE DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) IN (SELECT Id FROM test_10r)

    -----------------------------------------------------------------------------------
    | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |            |   200K|  5078K|   916   (3)| 00:00:11 |
    |*  1 |  HASH JOIN           |            |   200K|  5078K|   916   (3)| 00:00:11 |
    |   2 |   VIEW               | VW_NSO_1   |    10 |   130 |     4  (25)| 00:00:01 |
    |   3 |    HASH UNIQUE       |            |    10 |    30 |     4  (25)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL| TEST_10R   |    10 |    30 |     3   (0)| 00:00:01 |
    |   5 |   VIEW               |            |  2000K|    24M|   902   (2)| 00:00:11 |
    |   6 |    UNION-ALL         |            |       |       |            |          |
    |   7 |     TABLE ACCESS FULL| TEST_EXPV1 |  1000K|  3906K|   451   (2)| 00:00:06 |
    |   8 |     TABLE ACCESS FULL| TEST_EXPV2 |  1000K|  3906K|   451   (2)| 00:00:06 |
    -----------------------------------------------------------------------------------

    -- CORRECT RANGE INDEX SCAN for bound value
    SELECT * FROM (
              SELECT * FROM TEST_EXPV1
    UNION ALL SELECT * FROM TEST_EXPV2
    )
    WHERE DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) = :b1001

    ----------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |              | 20000 |   253K|   979  (10)| 00:00:12 |
    |   1 |  VIEW                         |              | 20000 |   253K|   979  (10)| 00:00:12 |
    |   2 |   UNION-ALL                   |              |       |       |            |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID| TEST_EXPV1   |     2 |     8 |     3   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | I_TEST_EXPV1 |     2 |       |     1   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS BY INDEX ROWID| TEST_EXPV2   |     2 |     8 |     3   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN          | I_TEST_EXPV2 |     2 |       |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------

    -- WORKING JOIN WITH TABLE
    WITH x AS (SELECT Id FROM test_10r WHERE Id BETWEEN :a AND :b)
    SELECT /*+ FIRST_ROWS */ * FROM (
              SELECT * FROM TEST_EXPV1
    UNION ALL SELECT * FROM TEST_EXPV2
    ) U, x
    WHERE DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) = x.id

    -------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |              |     1 |    16 |   996  (11)| 00:00:12 |
    |*  1 |  FILTER                          |              |       |       |            |          |
    |*  2 |   HASH JOIN                      |              |   500 |  8000 |   996  (11)| 00:00:12 |
    |*  3 |    TABLE ACCESS FULL             | TEST_10R     |    10 |    30 |     3   (0)| 00:00:01 |
    |   4 |    VIEW                          |              |  5000 | 65000 |   993  (11)| 00:00:12 |
    |   5 |     UNION-ALL                    |              |       |       |            |          |
    |*  6 |      FILTER                      |              |       |       |            |          |
    |   7 |       TABLE ACCESS BY INDEX ROWID| TEST_EXPV1   |  2500 | 10000 |  4192   (1)| 00:00:51 |
    |*  8 |        INDEX RANGE SCAN          | I_TEST_EXPV1 |  4500 |       |    11   (0)| 00:00:01 |
    |*  9 |      FILTER                      |              |       |       |            |          |
    |  10 |       TABLE ACCESS BY INDEX ROWID| TEST_EXPV2   |  2500 | 10000 |  4192   (1)| 00:00:51 |
    |* 11 |        INDEX RANGE SCAN          | I_TEST_EXPV2 |  4500 |       |    11   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------

Answer 1

您正在执行UNION，它正在对两个表进行全面扫描，以获得组合的结果集；然后对第三个表中的值进行筛选。

子查询保理的一个更常见的模式是在联合的每个分支中引用CTE：

WITH x AS (SELECT Id FROM test_10r)
          SELECT TEST_EXPV1.* FROM x JOIN TEST_EXPV1
          ON DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) = x.Id
UNION ALL SELECT TEST_EXPV2.* FROM x JOIN TEST_EXPV2
          ON DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) = x.Id;

----------------------------------------------------------------------------------------------                                                                                                          
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                          
----------------------------------------------------------------------------------------------                                                                                                          
|   0 | SELECT STATEMENT              |              |    32 |   224 |    66   (0)| 00:00:01 |                                                                                                          
|   1 |  UNION-ALL                    |              |       |       |            |          |                                                                                                          
|   2 |   NESTED LOOPS                |              |    16 |   112 |    33   (0)| 00:00:01 |                                                                                                          
|   3 |    TABLE ACCESS FULL          | TEST_10R     |    10 |    30 |     3   (0)| 00:00:01 |                                                                                                          
|   4 |    TABLE ACCESS BY INDEX ROWID| TEST_EXPV1   |     2 |     8 |     3   (0)| 00:00:01 |                                                                                                          
|*  5 |     INDEX RANGE SCAN          | I_TEST_EXPV1 |     2 |       |     1   (0)| 00:00:01 |                                                                                                          
|   6 |   NESTED LOOPS                |              |    16 |   112 |    33   (0)| 00:00:01 |                                                                                                          
|   7 |    TABLE ACCESS FULL          | TEST_10R     |    10 |    30 |     3   (0)| 00:00:01 |                                                                                                          
|   8 |    TABLE ACCESS BY INDEX ROWID| TEST_EXPV2   |     2 |     8 |     3   (0)| 00:00:01 |                                                                                                          
|*  9 |     INDEX RANGE SCAN          | I_TEST_EXPV2 |     2 |       |     1   (0)| 00:00:01 |                                                                                                          
----------------------------------------------------------------------------------------------                                                                                                          

Answer 2

将谓词推到UNION ALL中可能会让人动荡不安。试试这个：

SELECT /*+ PUSH_PRED(v) */ *
FROM   (SELECT * FROM test_expv1
        UNION ALL
        SELECT * FROM test_expv2) v INNER JOIN test_10r ON
        (DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value)) = test_10r.id;

下面是来自11.2.0.4实例的上述查询的结果，使用OP的DDL：

SQL_ID  df6dvkgjwjsq1, child number 1
-------------------------------------
SELECT /*+ PUSH_PRED(v) */ * FROM   (SELECT * FROM test_expv1         
UNION ALL         SELECT * FROM test_expv2) v INNER JOIN test_10r ON    
     (DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS 
NUMBER(38)), Value)) = test_10r.id

Plan hash value: 191389749

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     16 |00:00:00.01 |      69 |
|   1 |  NESTED LOOPS                  |              |      1 |   2000K|     16 |00:00:00.01 |      69 |
|   2 |   TABLE ACCESS FULL            | TEST_10R     |      1 |     10 |     10 |00:00:00.01 |      22 |
|   3 |   VIEW                         |              |     10 |     32 |     16 |00:00:00.01 |      47 |
|   4 |    UNION ALL PUSHED PREDICATE  |              |     10 |        |     16 |00:00:00.01 |      47 |
|   5 |     TABLE ACCESS BY INDEX ROWID| TEST_EXPV1   |     10 |    158 |      8 |00:00:00.01 |      24 |
|*  6 |      INDEX RANGE SCAN          | I_TEST_EXPV1 |     10 |      2 |      8 |00:00:00.01 |      16 |
|   7 |     TABLE ACCESS BY INDEX ROWID| TEST_EXPV2   |     10 |    158 |      8 |00:00:00.01 |      23 |
|*  8 |      INDEX RANGE SCAN          | I_TEST_EXPV2 |     10 |      2 |      8 |00:00:00.01 |      15 |
---------------------------------------------------------------------------------------------------------

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

   6 - access("TEST_EXPV1"."SYS_NC00002$"="TEST_10R"."ID")
   8 - access("TEST_EXPV2"."SYS_NC00002$"="TEST_10R"."ID")