Oracle中B树索引未优化范围查询的性能优化咨询

查询与执行成本 #

• 查询ONE：

SELECT * FROM TEST_RANDOM WHERE EMPNO >= '236400' AND EMPNO <= '456000';

执行成本为1927，创建B-Tree索引后仍未触发索引扫描，执行成本无优化。

• 查询TWO：

SELECT * FROM TEST_RANDOM WHERE EMPNO = '236400';

创建索引前执行成本1924，创建后降至4，性能提升明显。

表结构与数据情况 #

TEST_RANDOM表共100万行，创建过程如下：

Create table test_normal (empno varchar2(10), ename varchar2(30), sal number(10), faixa varchar2(10));

Begin 
    For i in 1..1000000 
    Loop 
        Insert into test_normal values(
            to_char(i), dbms_random.string('U',30), 
            dbms_random.value(1000,7000), 'ND'
        ); 
        If mod(i, 10000) = 0 then 
            Commit; 
        End if; 
    End loop; 
End; 

Create table test_random 
as
select /*+ append */ * from test_normal order by dbms_random.random;

已创建的B-Tree索引：

CREATE INDEX IDX_RANDOM_1 ON TEST_RANDOM (EMPNO);

1. 核心原因分析 #

查询ONE的过滤范围覆盖了219601行数据（占总数据量~22%），Oracle优化器判断：通过索引回表（因查询SELECT *需获取所有字段）的IO成本高于全表扫描，因此未选择索引执行计划。

2. 针对性优化手段 #

(1) 创建覆盖索引 #

直接创建包含所有查询字段的覆盖索引，避免回表操作，让优化器优先选择索引扫描：

CREATE INDEX IDX_RANDOM_EMPNO_COVER ON TEST_RANDOM (EMPNO) INCLUDE (ENAME, SAL, FAIXA);

优化器可直接从索引中获取全部所需数据，无需访问主表，执行成本会显著降低。

(2) 强制使用索引（谨慎操作） #

若确认索引扫描更高效但优化器未选择，可通过Hint强制指定索引：

SELECT /*+ INDEX(TEST_RANDOM IDX_RANDOM_1) */ * FROM TEST_RANDOM WHERE EMPNO >= '236400' AND EMPNO <= '456000';

注意：强制Hint可能在数据分布变化后导致性能下降，需定期验证执行计划。

(3) 重新收集统计信息 #

确保优化器拥有准确的表与索引统计信息，避免因统计数据过时做出错误判断：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'TEST_RANDOM', CASCADE => TRUE);

CASCADE => TRUE会同时收集索引统计信息，帮助优化器精准计算索引扫描成本。

(4) 采用分区表方案 #

若数据量持续增长，可按EMPNO范围对表分区，缩小查询扫描范围：

CREATE TABLE TEST_RANDOM_PART
(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(30),
    SAL NUMBER(10),
    FAIXA VARCHAR2(10)
)
PARTITION BY RANGE (EMPNO)
(
    PARTITION P1 VALUES LESS THAN ('100000'),
    PARTITION P2 VALUES LESS THAN ('200000'),
    PARTITION P3 VALUES LESS THAN ('300000'),
    PARTITION P4 VALUES LESS THAN ('400000'),
    PARTITION P5 VALUES LESS THAN ('500000'),
    PARTITION P6 VALUES LESS THAN (MAXVALUE)
);
-- 迁移数据
INSERT INTO TEST_RANDOM_PART SELECT * FROM TEST_RANDOM;

查询ONE仅需扫描P3、P4两个分区，大幅减少扫描数据量。

(5) 调整字段类型（可选） #

当前EMPNO为VARCHAR2类型，但存储的是数字字符串，范围查询时字符串对比效率略低于数字类型。若业务允许，可修改字段类型并重建索引：

ALTER TABLE TEST_RANDOM MODIFY EMPNO NUMBER(10);
DROP INDEX IDX_RANDOM_1;
CREATE INDEX IDX_RANDOM_1 ON TEST_RANDOM (EMPNO);

注意：修改字段类型需确认无业务影响，并校验所有关联SQL。

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