sql - 使用 PL/SQL 表避免使用 SELECT 进行全表扫描

Question

测试数据

CREATE TABLE parent AS ( SELECT ROWNUM AS id, 'XXX' AS dummy FROM dual CONNECT BY ROWNUM <= 1000 );
CREATE UNIQUE INDEX idx_parent ON parent(id);

CREATE TABLE child AS ( SELECT CEIL(ROWNUM/5) AS id, 'XXX' AS dummy FROM dual CONNECT BY ROWNUM <= 5000 );
CREATE INDEX idx_child ON child(id);

EXEC dbms_stats.gather_table_stats(USER, 'parent');
EXEC dbms_stats.gather_table_stats(USER, 'child');

问题
即使考虑了 CARDINALITY 提示，以下查询也会对 child 执行全表扫描(包括 12.1 和 19.0)。当然，真正的查询需要来自 child 的一些额外数据。

SELECT child.id
FROM parent
JOIN
(
    SELECT child.id
    FROM child
    GROUP BY child.id
) child ON ( child.id = parent.id )
WHERE parent.id IN ( SELECT /*+ CARDINALITY( tab 1 ) */ COLUMN_VALUE FROM TABLE (sys.odcinumberlist(1) ) tab );

-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |     1 |    19 |    35   (3)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI                  |            |     1 |    19 |    35   (3)| 00:00:01 |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|            |     1 |     2 |    29   (0)| 00:00:01 |
|   3 |   NESTED LOOPS                         |            |  1000 | 17000 |     6  (17)| 00:00:01 |
|   4 |    VIEW                                |            |  1000 | 13000 |     6  (17)| 00:00:01 |
|   5 |     HASH GROUP BY                      |            |  1000 |  4000 |     6  (17)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                 | CHILD      |  5000 | 20000 |     5   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN                   | IDX_PARENT |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

如果我用这个替换 WHERE 子句，两个索引都按预期使用:

WHERE parent.id IN ( 1 );

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     5 |    35 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS       |            |     5 |    35 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN | IDX_PARENT |     1 |     4 |     1   (0)| 00:00:01 |
|   3 |   VIEW              |            |     5 |    15 |     1   (0)| 00:00:01 |
|   4 |    SORT GROUP BY    |            |     5 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| IDX_CHILD  |     5 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

当我删除 GROUP BY 时它也有效.

知道如何解决这个问题吗？

Answer 1

问题是 ID 列可以包含 NULL 值。如果将列定义为 NOT NULL，则使用索引。
索引不包含 NULL 值。但是 GROUP BY 必须包含这些数据。
当您在示例中将 parent.id 限制为 1 时，数据库可以使用具体值的索引。