Oracle查询未使用位图索引的原因排查

测试环境准备 #

测试用表test_data可通过以下代码生成：

DROP TABLE test_data;

CREATE TABLE test_data (
    random_value    NUMBER,
    random_string   VARCHAR2(20),
    binary_number      NUMBER,
    binary_string VARCHAR2(20)
);

DELETE FROM test_data;

COMMIT;

INSERT INTO test_data
    SELECT
        dbms_random.value() random_value,
        dbms_random.string('x',20) random_string,
        CASE
                WHEN ROWNUM < 500000 THEN 1
                ELSE 0
            END
        binary_number,
                CASE
                WHEN ROWNUM < 500000 THEN 'YNTRFKC3M081ZVYF4WPR'
                ELSE 'TV2XN199BSSGDI547AC1'
            END
        binary_string
    FROM
        dual
    CONNECT BY
        level <= 1000000;

COMMIT;

随后为binary_number和binary_string列创建位图索引，并收集表统计信息：

DROP INDEX ix_td1;

DROP INDEX ix_td2;

CREATE BITMAP INDEX ix_td1 ON
    test_data (
        binary_number
    );

CREATE BITMAP INDEX ix_td2 ON
    test_data (
        binary_string
    );

EXEC dbms_stats.gather_table_stats(user,'TEST_DATA');

问题现象 #

执行以下查询时，Oracle执行计划选择了全表扫描，并未使用创建的位图索引：

EXPLAIN PLAN
    FOR
SELECT
    *
FROM
    test_data t
WHERE
    binary_number = 1
    AND binary_string = 'YNTRFKC3M081ZVYF4WPR';

SELECT
    *
FROM
    TABLE ( dbms_xplan.display(format => 'ALL +OUTLINE') );

执行计划输出 #

Plan hash value: 1706400376
 
---------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   526K|    32M|  2716   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| TEST_DATA |   526K|    32M|  2716   (1)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("BINARY_STRING"='YNTRFKC3M081ZVYF4WPR' AND "BINARY_NUMBER"=1)
       filter("BINARY_STRING"='YNTRFKC3M081ZVYF4WPR' AND "BINARY_NUMBER"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "T"."RANDOM_VALUE"[NUMBER,22], "T"."RANDOM_STRING"[VARCHAR2,20], 
       "BINARY_NUMBER"[NUMBER,22], "BINARY_STRING"[VARCHAR2,20]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

即使添加索引强制提示，Oracle依然选择全表扫描：

SELECT /*+ index(ix_td1) index(ix_td2) */
    *
FROM
    test_data t
WHERE
    binary_number = 1
    AND binary_string = 'YNTRFKC3M081ZVYF4WPR';

原因分析 #

• 数据返回占比过高：查询返回约50万行数据，占表总行数（100万）的50%。Oracle优化器认为，当查询返回数据量超过表总量的20%-30%时，全表扫描的成本更低——索引扫描需要先定位索引块，再回表读取数据块，大量回表操作的IO开销会超过全表扫描的连续IO开销。
• 位图索引适用场景限制：位图索引虽适合低基数列，但更偏向小结果集查询。对于返回大量数据的查询，位图索引的优势无法体现，优化器会倾向于全表扫描。
• 统计信息与成本计算：已收集的表统计信息让优化器能准确计算查询返回行数，基于成本的优化器（CBO）会对比全表扫描和索引扫描的成本，最终选择成本更低的执行路径。
• 索引提示格式错误：你使用的index(ix_td1)提示格式有误，正确写法应指定表别名和索引名，比如/*+ index(t ix_td1 ix_td2) */。不过即使格式正确，当优化器判定全表扫描成本远低于索引扫描时，仍可能忽略提示。

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