oracle 索引范围扫描 vs 索引跳过扫描 vs 索引快速全扫描
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19059444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index range scan vs index skip scan vs index fast full scan
提问by user2824874
I have table
我有桌子
test_A(
id1 number,
id2 number,
id3 number,
name varchar2(10),
create_dt date
)
I have two indexes one composite index indx1on (id1,id2)and indx2(id3). Now when I query this table test_Aas
我有两个指标一个综合指数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);
I ran explain plan for this above SQL and it is using "index skip scan". If I create another index on create_dtthen it using index fast full scan and over all cost and %cpu is showing higher than plan with Index skip scan. It is also using Index range scan after creating index on create_dt.
我为上面的 SQL 运行了解释计划，它使用了“索引跳过扫描”。如果我创建另一个索引，create_dt那么它使用索引快速完整扫描和所有成本和 %cpu 显示高于使用索引跳过扫描的计划。在上创建索引后，它还使用索引范围扫描create_dt。
I could not come to conclusion which should be could? Do I need to create another Index on create_dtor is Index skip scan good? I believe Index skip is a feature of Oracle to run multiple index range scan?
我无法得出结论，应该可以？我需要在上面创建另一个索引create_dt还是索引跳过扫描好？我相信索引跳过是 Oracle 运行多个索引范围扫描的功能吗？
回答by krokodilko
I recommend you to familiarize yourself with this link: http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optop.htm#CHDFJIJA
It is Oracle 12c related, however it is very usefull to gain understanding how oracle uses different index access pathes in all DBMS versions.
Your subquery is ambigous:
我建议您熟悉此链接：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
both test_A.id2 and id2 references to the same test_A.id2, and the query is equivalent to this:
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);
or simply:
或者干脆：
select * from test_A where id2=123 and
create_dt=(select max(create_dt) from test_A where id2 is not null);
I suppose that you want something like this:
我想你想要这样的东西：
select * from test_A where id2=123 and
create_dt=(select max(create_dt)
from test_A ALIAS
where test_A.id2=ALIAS.id2);
For the above query a composite index on id2+create_dt most likely give the best results, try it:
对于上面的查询，id2+create_dt 上的复合索引最有可能给出最好的结果，试试看：
CREATE INDEX index_name ON test_A( id2, create_dt);
