大表JOIN查询优化：ITEM_CTY_TAR_T表需创建何种索引避免全表扫描？

现有一张包含1000万+行数据的大表ITEM_CTY_TAR_T，我已基于列ITEM_NO、ITEM_TYPE、GA_CODE_IMP、GA_TYPE_IMP、TAR_NO和VALID_DATE_FROM创建了主键索引，但执行如下SQL查询时，执行计划显示优化器对该表执行了全表扫描。请问应创建何种索引来避免全表扫描？

对应的SQL语句：

SELECT
    t.item_no, t.item_type, t.BU_CODE_RU, t.tar_no,
    VALID_DATE_FROM 
FROM
    (SELECT  
         tar.item_no, tar.item_type,
         org.BU_CODE_RU, tar.tar_no, tar.VALID_DATE_FROM,
         ROW_NUMBER() OVER (PARTITION BY tar.ITEM_NO, tar.ITEM_TYPE, org.BU_CODE_RU 
                            ORDER BY VALID_DATE_FROM DESC, tar.tar_no DESC) AS RowNumber  
     FROM 
         (SELECT 
              item_no, item_type, tar_no, valid_date_from,
              GA_CODE_IMP 
          FROM 
              ITEM_CTY_TAR_T tar 
          WHERE  
              GA_TYPE_IMP = 'CTY' 
              AND DATE '2023-08-06' >= TRUNC(tar.VALID_DATE_FROM)
              AND DATE '2023-08-06' <= NVL(trunc(tar.VALID_DATE_TO), DATE'9999-12-31')
              AND NVL(trunc(tar.DELETE_DATE), DATE '9999-12-31') >  DATE '2023-08-06') tar,
         (SELECT
              BU_CODE_RU, GA_CODE_CTY 
          FROM
              store_t 
          WHERE
              BU_TYPE = 'SO' AND BU_CODE_RU IS NOT NULL) org
     WHERE
         org.GA_CODE_CTY = tar.GA_CODE_IMP) t  
WHERE
    rownumber = 1

针对ITEM_CTY_TAR_T表的查询逻辑，建议创建过滤优先+覆盖查询的复合索引，具体如下：

推荐创建的索引 #

CREATE INDEX IDX_ITEM_CTY_TAR_FILTER_COVER ON ITEM_CTY_TAR_T (
    GA_TYPE_IMP,
    VALID_DATE_FROM,
    ITEM_NO,
    ITEM_TYPE,
    GA_CODE_IMP
) INCLUDE (
    TAR_NO,
    VALID_DATE_TO,
    DELETE_DATE
);

索引设计逻辑 #

1. 过滤条件前置：把查询中最具筛选力度的GA_TYPE_IMP = 'CTY'放在索引首位，快速缩小数据扫描范围，直接排除不符合条件的行。
2. 适配日期范围查询：VALID_DATE_FROM作为第二个列，匹配DATE '2023-08-06' >= TRUNC(tar.VALID_DATE_FROM)的范围条件，进一步过滤数据。
3. 关联与分区字段内置：ITEM_NO、ITEM_TYPE、GA_CODE_IMP是后续表关联和窗口函数分区的核心字段，放在索引中可直接用于关联计算，无需回表读取原表数据。
4. 覆盖所有查询字段：通过INCLUDE子句添加TAR_NO、VALID_DATE_TO、DELETE_DATE，让索引包含查询所需的全部字段，实现索引覆盖扫描，彻底避免全表扫描和回表操作。

额外优化建议 #

• 尽量避免对日期列使用TRUNC函数，若业务逻辑允许，可将DATE '2023-08-06' >= TRUNC(tar.VALID_DATE_FROM)改为VALID_DATE_FROM < DATE '2023-08-07'，这样优化器可以直接利用索引列的原始值，无需函数转换，进一步提升索引命中率。
• 检查store_t表的GA_CODE_CTY字段是否有索引，若没有建议创建包含GA_CODE_CTY和BU_CODE_RU的复合索引，加速两表关联过程。

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