Oracle查询优化：已创建item_desc_secondary索引仍全表扫描如何解决？

已为item_desc_secondary列创建索引，但执行计划仍显示对ITEM_MAIN_BKP表执行全表扫描，需优化以下查询：

查询语句 #

select i.item_desc_secondary,min(i.dept) dept
from ITEM_MAIN_BKP i
where i.i_level = i.t_level
and i.item_desc_secondary is not null
group by i.item_desc_secondary

执行计划 #

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  6879 |   114K|   386   (1)| 00:00:01 |
|   1 |  HASH GROUP BY     |                 |  6879 |   114K|   386   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ITEM_MAIN_BKP   | 15039 |   249K|   385   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

解决方法 #

• 创建复合覆盖索引：单独的item_desc_secondary索引无法覆盖查询所需全部字段，数据库需回表获取i_level、t_level和dept的值，成本可能高于全表扫描。建议创建包含过滤条件与查询字段的复合索引：

  -- Oracle 11g+ 可用INCLUDE避免dept成为索引键，减小索引体积
  CREATE INDEX idx_item_main_bkp_cover ON ITEM_MAIN_BKP(i_level, t_level, item_desc_secondary) INCLUDE (dept);
  -- 旧版本可直接将dept加入索引列
  CREATE INDEX idx_item_main_bkp_cover ON ITEM_MAIN_BKP(i_level, t_level, item_desc_secondary, dept);
  

  该索引可让优化器直接获取所有所需数据，无需回表，更易选择索引扫描而非全表扫描。

• 更新表与索引统计信息：若统计信息过时，优化器无法准确判断索引成本。执行以下命令收集最新统计：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'ITEM_MAIN_BKP', CASCADE => TRUE);
  

• 临时强制使用索引（不推荐长期使用）：若上述方法无效，可在查询中添加索引提示测试（仅用于排查问题，会绕过优化器自动选择）：

  select /*+ INDEX(i idx_item_main_bkp_cover) */ i.item_desc_secondary,min(i.dept) dept
  from ITEM_MAIN_BKP i
  where i.i_level = i.t_level
  and i.item_desc_secondary is not null
  group by i.item_desc_secondary
  

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