oracle - 索引范围扫描 vs 索引跳过扫描 vs 索引快速全扫描

Question

我有 table

test_A(
    id1 number,
    id2 number,
    id3 number,
    name varchar2(10),
    create_dt date
)

我有两个索引一个复合索引 indx1在 (id1,id2)和 indx2(id3) .现在当我查询这个表时 test_A作为

select * from test_A where id2=123 and 
create_dt=(select max(create_dt) from test_A where test_A.id2=id2);

我为上面的 SQL 运行了解释计划，它使用了“索引跳过扫描”。如果我在 create_dt 上创建另一个索引然后它使用索引快速完整扫描和所有成本和 %cpu 显示高于使用索引跳过扫描的计划。在 create_dt 上创建索引后，它还使用索引范围扫描.

我无法得出结论，应该可以？我需要在 create_dt 上创建另一个索引吗？还是索引跳过扫描好？我相信索引跳过是 Oracle 运行多个索引范围扫描的功能吗？

Answer 1

我建议您熟悉此链接:http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optop.htm#CHDFJIJA
它与 Oracle 12c 相关，但是了解 oracle 如何在所有 DBMS 版本中使用不同的索引访问路径非常有用。

您的子查询不明确:

select max(create_dt) from test_A where test_A.id2=id2

test_A.id2 和 id2 都引用了同一个 test_A.id2，查询等价于:

select * from test_A where id2=123 and 
create_dt=(select max(create_dt) from test_A where id2=id2);

或者干脆:

select * from test_A where id2=123 and 
create_dt=(select max(create_dt) from test_A where id2 is not null);

我想你想要这样的东西:

select * from test_A where id2=123 and 
create_dt=(select max(create_dt) 
           from test_A ALIAS 
           where test_A.id2=ALIAS.id2);

对于上面的查询，id2+create_dt 上的复合索引最有可能给出最好的结果，试试看:

CREATE INDEX index_name ON test_A( id2, create_dt);