优化器选择全分区扫描而非分区索引扫描问题求助

我有一张按dt列按月分区的表table1，已在dt列创建本地索引。执行以下查询时，发现优化器选择全分区扫描而非使用该dt列索引。

查询语句 #

WITH 
A AS 
(
    Select * from table1 
    WHERE  
    EXISTS (SELECT u_id FROM table2
    WHERE u_id=UPPER('ABC'))
)
SELECT DISTINCT 
A.id,
A.dt
FROM
A
WHERE 
A.dt BETWEEN timestamp '2022-04-01 00:00:00' AND timestamp '2022-04-01 23:59:59.999000000'

执行计划 #

----------------------------------------------------------------------------------------------------------------------
| Id  | 操作类型                | 名称              | 行数   | 字节数 |临时空间| 成本(%CPU)| 耗时     | 分区起始| 分区结束|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT语句               |                   |  755K |   67M  |        | 34447   (1)| 00:00:02 |        |        |
|   1 | 哈希去重                 |                   |  755K |   67M  |   75M  | 34447   (1)| 00:00:02 |        |        |
|*  2 | 过滤                     |                   |       |        |        |            |          |        |        |
|   3 | 单分区范围扫描           |                   |  755K |   67M  |        | 18298   (1)| 00:00:01 |     5  |     5  |
|*  4 |   全表扫描               | TABLE1            |  755K |   67M  |        | 18298   (1)| 00:00:01 |     5  |     5  |
|*  5 |   唯一索引扫描           | SYS_C0099684      |    1  |   15   |        |     0   (0)| 00:00:01 |        |        |
----------------------------------------------------------------------------------------------------------------------
 
谓词信息（按操作ID标识）：
---------------------------------------------------
 
   2 - 过滤条件( EXISTS (SELECT 0 FROM "TEST"."TABLE2" 
              "TABLE2" WHERE "U_ID"=U'ABC'))
   4 - 过滤条件("TABLE1"."DT"<=TIMESTAMP' 2022-04-02 23:59:59.999000000' AND 
              "TABLE1"."DT">=TIMESTAMP' 2022-04-02 00:00:00.000000000')
   5 - 访问条件("U_ID"=U'ABC')

核心原因 #

1. 谓词位置不合理：dt的过滤条件写在CTE外部，优化器无法提前利用分区裁剪和索引筛选数据，只能先全扫table1再过滤dt。
2. 成本估算决策：执行计划显示单分区预计返回755K行，优化器认为全扫单个分区的IO开销比走索引+回表的开销更低，因此选择全分区扫描。
3. 无关联的EXISTS条件：EXISTS子查询和table1没有关联字段，只要table2存在U_ID='ABC'，就会返回table1所有行，这种逻辑下优化器倾向于全表扫描后过滤。

优化方案 #

方案1：将dt过滤移到CTE内部 #

让优化器提前利用分区和索引筛选数据：

WITH 
A AS 
(
    Select * from table1 
    WHERE  
        EXISTS (SELECT u_id FROM table2 WHERE u_id=UPPER('ABC'))
        AND dt BETWEEN timestamp '2022-04-01 00:00:00' AND timestamp '2022-04-01 23:59:59.999000000'
)
SELECT DISTINCT 
A.id,
A.dt
FROM
A

方案2：简化查询，去掉CTE #

避免CTE带来的优化限制，直接关联查询：

SELECT DISTINCT 
    t1.id,
    t1.dt
FROM table1 t1
WHERE 
    t1.dt BETWEEN timestamp '2022-04-01 00:00:00' AND timestamp '2022-04-01 23:59:59.999000000'
    AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.u_id=UPPER('ABC'))

方案3：强制使用索引（仅优化器估算错误时用） #

通过索引提示强制优化器走dt列本地索引：

WITH 
A AS 
(
    Select /*+ INDEX(table1 idx_table1_dt) */ * from table1 
    WHERE  
        EXISTS (SELECT u_id FROM table2 WHERE u_id=UPPER('ABC'))
        AND dt BETWEEN timestamp '2022-04-01 00:00:00' AND timestamp '2022-04-01 23:59:59.999000000'
)
SELECT DISTINCT 
A.id,
A.dt
FROM
A

方案4：更新统计信息 #

若统计信息过时，优化器可能做出错误决策，执行以下命令更新：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的模式名', 'TABLE1', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的模式名', 'TABLE2', CASCADE => TRUE);

内容的提问来源于stack exchange，提问作者Soni Soni