如何使优化器对TDTEMP.FACT_ITEM_TEMP_T表执行索引扫描而非全表扫描？

我已为表TDTEMP.FACT_ITEM_TEMP_T创建了如下索引：

CREATE INDEX TDTEMP.FACT_ITEM_TMP_IDX 
  ON TDTEMP.FACT_ITEM_TEMP_T
  (ITEM_NO,ITEM_TYPE,SALE_START_DATE,SALE_END_DATE,BU_RU,BU_TYPE_RU)


CREATE INDEX TDTEMP.FACT_ITEM_TMP_IDX_01 
  ON TDTEMP.FACT_ITEM_TEMP_T
  (ITEM_NO,ITEM_TYPE,STATE_NO)

但执行以下查询的执行计划时，优化器仍执行全表扫描，请指导如何解决该问题以降低全表读取的成本。

查询语句如下：

( SELECT--+ parallel(IT,4)  
                    DISTINCT
                        IT.ITEM_NO              ,
                        IT.ITEM_TYPE            ,
                        DWFACT.REQ_DWP_NO       ,
                        DWFACT.REQ_DWP_ED       ,
                        DWFACT.BU_CODE          ,
                        DWFACT.BU_TYPE          ,
                        DWFACT.FROM_PACK_DATE   ,
                        DWFACT.TO_PACK_DATE     ,
                        IT.SALE_START_DATE      ,
                        IT.SALE_END_DATE        ,
                        IT.PIA_DELETE_DATE      ,
                        DWFACT.DELETE_DTIME AS DWP_DELETE_DATE      ,
                        IT.BU_RU        ,
                        IT.BU_TYPE_RU       ,
                        'PM' AS SOURCE          ,
                        'ACTIVE' STATUS
                    FROM 
                    TDTEMP.FACT_ITEM_TEMP_T IT,
                    TDTEMP.FACT_ACT_PM_T DWPPM ,                    
                    (SELECT t.ITEM_NO,t.ITEM_TYPE,t.REQ_DWP_NO,t.REQ_DWP_ED ,t.BU_CODE,t.BU_TYPE,t.FROM_PACK_DATE,t.TO_PACK_DATE,t.DELETE_DTIME FROM
                    (SELECT     ITEM_NO,
                                ITEM_TYPE,
                                REQ_DWP_NO,
                                REQ_DWP_ED ,
                                BU_CODE,
                                BU_TYPE,
                                FROM_PACK_DATE,
                                TO_PACK_DATE,
                                DELETE_DTIME,
                                ROW_NUMBER() OVER (PARTITION BY ITEM_NO,ITEM_TYPE,BU_CODE,BU_TYPE 
                                ORDER BY FROM_PACK_DATE DESC, REQ_DWP_NO DESC ,REQ_DWP_ED DESC) AS RowNumber
                            FROM TDTEMP.FACT_ACT_T 
                            WHERE   DATE '2023-07-11'  >= FROM_PACK_DATE
                            AND     DATE '2023-07-11' <= NVL(TO_PACK_DATE,DATE'9999-12-31')
                            AND     NVL(TRUNC(DELETE_DTIME), DATE '9999-12-31') >=  DATE '2023-07-11' ) t
                            WHERE RowNumber = 1) DWFACT
                            WHERE IT.STATE_NO = 100
                            AND IT.ITEM_NO                      = DWFACT.ITEM_NO
                            AND IT.ITEM_TYPE                    = DWFACT.ITEM_TYPE
                            AND DWFACT.ITEM_NO                  = DWPPM.ITEM_NO
                            AND DWFACT.ITEM_TYPE                = DWPPM.ITEM_TYPE
                            AND DWFACT.REQ_DWP_NO               = DWPPM.REQ_DWP_NO
                            AND DWFACT.REQ_DWP_ED               = DWPPM.REQ_DWP_ED
                            AND DWFACT.BU_CODE                  = DWPPM.BU_CODE
                            AND DWFACT.BU_TYPE                  = DWPPM.BU_TYPE
                            AND DWFACT.FROM_PACK_DATE           = DWPPM.FROM_PACK_DATE
                            AND DWPPM.PM_FUNCTION_ID NOT IN ( 44,45,46,47,48,49)
                            AND NVL(TRUNC(DWFACT.DELETE_DTIME), DATE '9999-12-31') <>  DATE '2023-07-11'
                            AND NVL(TRUNC(DWPPM.DELETE_DTIME), DATE '9999-12-31')  <>  DATE '2023-07-11'
                            AND NVL(TRUNC(IT.PIA_DELETE_DATE), DATE '9999-12-31')  <>  DATE '2023-07-11'
                            )

1. 构建覆盖索引 #

当前查询从FACT_ITEM_TEMP_T获取的字段包括ITEM_NO、ITEM_TYPE、SALE_START_DATE、SALE_END_DATE、PIA_DELETE_DATE、BU_RU、BU_TYPE_RU，同时过滤条件为STATE_NO = 100，关联条件是ITEM_NO和ITEM_TYPE匹配DWFACT表。

现有索引FACT_ITEM_TMP_IDX_01仅包含ITEM_NO,ITEM_TYPE,STATE_NO，优化器若使用该索引需要回表读取其他字段，可能因此选择全表扫描。将其修改为覆盖索引，包含所有查询所需字段：

-- Oracle 语法：直接扩展复合索引
CREATE INDEX TDTEMP.FACT_ITEM_TMP_IDX_01 
  ON TDTEMP.FACT_ITEM_TEMP_T
  (ITEM_NO, ITEM_TYPE, STATE_NO, SALE_START_DATE, SALE_END_DATE, PIA_DELETE_DATE, BU_RU, BU_TYPE_RU);

-- 或部分数据库支持的INCLUDE语法（避免索引键过大）
CREATE INDEX TDTEMP.FACT_ITEM_TMP_IDX_01 
  ON TDTEMP.FACT_ITEM_TEMP_T
  (ITEM_NO, ITEM_TYPE, STATE_NO)
  INCLUDE (SALE_START_DATE, SALE_END_DATE, PIA_DELETE_DATE, BU_RU, BU_TYPE_RU);

覆盖索引可让优化器直接从索引中获取所有数据，无需回表，大幅提升索引扫描的性价比。

2. 更新统计信息 #

优化器依赖准确的表和索引统计信息判断执行计划。若统计信息过期，会导致优化器误判数据分布，选择全表扫描。执行以下命令更新相关表的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('TDTEMP', 'FACT_ITEM_TEMP_T', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TDTEMP', 'FACT_ACT_T', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TDTEMP', 'FACT_ACT_PM_T', CASCADE => TRUE);

3. 调整索引顺序（根据数据过滤性） #

如果STATE_NO = 100的过滤性极强（符合条件的行数占比极低），可调整索引顺序，将过滤字段放在最前面，进一步缩小扫描范围：

CREATE INDEX TDTEMP.FACT_ITEM_TMP_IDX_STATE 
  ON TDTEMP.FACT_ITEM_TEMP_T
  (STATE_NO, ITEM_NO, ITEM_TYPE)
  INCLUDE (SALE_START_DATE, SALE_END_DATE, PIA_DELETE_DATE, BU_RU, BU_TYPE_RU);

这样优化器可先通过STATE_NO = 100快速筛选出目标行，再进行关联操作。

4. 移除不必要的DISTINCT #

查询中的DISTINCT会触发额外的排序去重操作，也可能干扰优化器的计划选择。检查关联逻辑，若确认不会产生重复行，直接移除DISTINCT，减少计算开销。

5. 强制使用索引（临时应急方案） #

若上述方法均未生效，可在查询中添加索引提示，强制优化器使用指定索引：

SELECT--+ parallel(IT,4) INDEX(IT FACT_ITEM_TMP_IDX_01)  
DISTINCT
-- 后续查询内容不变

注意：索引提示属于临时方案，优先通过调整索引和统计信息让优化器自动选择最优计划。

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