sql - 避免在视图中使用子查询或分析函数进行全表扫描

问题描述

我可以使用 Oracle 11（请参阅SQL Fiddle）和 Oracle 12重现以下行为。

CREATE TYPE my_tab IS TABLE OF NUMBER(3);

CREATE TABLE test AS SELECT ROWNUM AS id FROM dual CONNECT BY ROWNUM <= 1000;
CREATE UNIQUE INDEX idx_test ON test( id );

CREATE VIEW my_view AS
  SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
  FROM test;

以下情况按预期使用索引：

SELECT * FROM my_view
WHERE id IN ( 1, 2 );

---------------------------------------------------------------------------------                                                                                                                       
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       
|   0 | SELECT STATEMENT     |          |     2 |    52 |     2   (0)| 00:00:01 |                                                                                                                       
|   1 |  VIEW                | MY_VIEW  |     2 |    52 |     2   (0)| 00:00:01 |                                                                                                                       
|   2 |   WINDOW BUFFER      |          |     2 |     8 |     2   (0)| 00:00:01 |                                                                                                                       
|   3 |    INLIST ITERATOR   |          |       |       |            |          |                                                                                                                       
|*  4 |     INDEX UNIQUE SCAN| IDX_TEST |     2 |     8 |     2   (0)| 00:00:01 |                                                                                                                       
---------------------------------------------------------------------------------

即使提供了基数提示，以下情况也不使用索引：

SELECT * FROM my_view
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab );

--------------------------------------------------------------------------------------------------                                                                                                      
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                      
--------------------------------------------------------------------------------------------------                                                                                                      
|   0 | SELECT STATEMENT                       |         |     1 |    28 |    33   (4)| 00:00:01 |                                                                                                      
|*  1 |  HASH JOIN RIGHT SEMI                  |         |     1 |    28 |    33   (4)| 00:00:01 |                                                                                                      
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|         |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                      
|   3 |   VIEW                                 | MY_VIEW |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                      
|   4 |    WINDOW SORT                         |         |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                      
|   5 |     TABLE ACCESS FULL                  | TEST    |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                      
--------------------------------------------------------------------------------------------------

编辑：

使用内联视图和 aJOIN而不是IN使用类似的计划：

SELECT /*+ CARDINALITY( tab, 2 ) */ *
FROM ( SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt FROM test ) t
JOIN TABLE( NEW my_tab( 1, 2 ) ) tab ON ( tab.COLUMN_VALUE = t.id );

-----------------------------------------------------------------------------------------------                                                                                                         
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                         
-----------------------------------------------------------------------------------------------                                                                                                         
|   0 | SELECT STATEMENT                       |      |     2 |    56 |    33   (4)| 00:00:01 |                                                                                                         
|*  1 |  HASH JOIN                             |      |     2 |    56 |    33   (4)| 00:00:01 |                                                                                                         
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                         
|   3 |   VIEW                                 |      |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                         
|   4 |    WINDOW SORT                         |      |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                         
|   5 |     TABLE ACCESS FULL                  | TEST |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                         
-----------------------------------------------------------------------------------------------

LEFT JOIN用with替换分析函数GROUP BY也无济于事：

SELECT *
FROM ( SELECT t.id, s.cnt
       FROM test t
       LEFT JOIN ( SELECT id, COUNT(*) AS cnt
                   FROM test
                   GROUP BY id
                 ) s ON ( s.id = t.id )
     )
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab );

-----------------------------------------------------------------------------------------------------                                                                                                   
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                   
-----------------------------------------------------------------------------------------------------                                                                                                   
|   0 | SELECT STATEMENT                         |          |     2 |    64 |    34   (6)| 00:00:01 |                                                                                                   
|*  1 |  HASH JOIN OUTER                         |          |     2 |    64 |    34   (6)| 00:00:01 |                                                                                                   
|   2 |   NESTED LOOPS                           |          |     2 |    12 |    30   (4)| 00:00:01 |                                                                                                   
|   3 |    SORT UNIQUE                           |          |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                   
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|          |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                   
|*  5 |    INDEX UNIQUE SCAN                     | IDX_TEST |     1 |     4 |     0   (0)| 00:00:01 |                                                                                                   
|   6 |   VIEW                                   |          |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                   
|   7 |    HASH GROUP BY                         |          |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                   
|   8 |     TABLE ACCESS FULL                    | TEST     |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                   
-----------------------------------------------------------------------------------------------------

用子选择替换 PL/SQL 集合似乎也无济于事。考虑了 CARDINALITY 提示（计划说 2 行），但索引仍然被忽略。

SELECT *
FROM ( SELECT id, cnt FROM my_view )
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ id FROM test tab );

---------------------------------------------------------------------------------                                                                                                                       
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       
|   0 | SELECT STATEMENT     |          |     2 |    60 |     4  (25)| 00:00:01 |                                                                                                                       
|   1 |  NESTED LOOPS        |          |     2 |    60 |     4  (25)| 00:00:01 |                                                                                                                       
|   2 |   VIEW               | MY_VIEW  |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                                       
|   3 |    WINDOW SORT       |          |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                                       
|   4 |     TABLE ACCESS FULL| TEST     |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                                       
|*  5 |   INDEX UNIQUE SCAN  | IDX_TEST |     1 |     4 |     0   (0)| 00:00:01 |                                                                                                                       
---------------------------------------------------------------------------------

添加WHERE tab.id <= 2到 in-list-subquery 会使用索引，因此当从具有分析函数（或另一个子选择）的视图中选择并按值列表进行过滤时，优化器似乎“没有认真对待 CARDINALITY 提示”。

如何使这些查询按预期使用索引？

标签： sqloracleperformanceoracle11goracle12c

我认为~~一个问题可能是当~~外部查询块包含 PL/SQL 函数（例如）时，优化器拒绝合并视图（并考虑基础表上的任何索引TABLE()）。

如果手动展开视图，直接查询表，可以正常访问索引：

SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
  FROM test
  WHERE id IN ( SELECT COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab )
 ;

----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |     1 |     6 |    31   (4)| 00:00:01 |
|   1 |  WINDOW SORT                            |          |     1 |     6 |    31   (4)| 00:00:01 |
|*  2 |   HASH JOIN SEMI                        |          |     1 |     6 |    30   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                      | IDX_TEST |  1000 |  4000 |     1   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

我不确定是否有办法覆盖这种行为，或者它是否是优化器的限制。我尝试将 TABLE 函数移动到 CTE，但这似乎没有帮助。

sql - 避免在视图中使用子查询或分析函数进行全表扫描

问题描述

解决方案

推荐阅读