SQL/Oracle：当可以使用多列上的索引时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/57878/
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
SQL/Oracle: when indexes on multiple columns can be used
提问by avernet
If I create an index on columns (A, B, C), in that order, my understanding is that the database will be able to use it even if I search only on (A), or (A and B), or (A and B and C), but not if I search only on (B), or (C), or (B and C). Is this correct?
如果我按顺序在列 (A, B, C) 上创建索引，我的理解是即使我只搜索 (A)，或 (A 和 B)，或 ( A 和 B 和 C)，但如果我只搜索 (B)、或 (C) 或 (B 和 C)，则不会。这样对吗？
采纳答案by David Aldridge
There are actually three index-based access methods that Oracle can use when a predicate is placed on a non-leading column of an index.
当谓词放在索引的非前导列上时，Oracle 实际上可以使用三种基于索引的访问方法。
i) Index skip-scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105
i) 索引跳过扫描：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105
ii) Fast full index scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044
ii) 快速全索引扫描：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044
iii) Index full scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107
iii) 索引全扫描：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107
I've most often seen the fast full index scan "in the wild", but all are possible.
我最常看到“在野外”进行快速全索引扫描，但一切皆有可能。
回答by Ethan Post
That is not correct. Always best to come up with a test case that represents your data and see for yourself. If you want to really understand the Oracle SQL Optimizer google Jonathan Lewis, read his books, read his blog, check out his website, the guy is amazing, and he always generates test cases.
那是不正确的。始终最好提出一个代表您的数据的测试用例并亲自查看。如果你想真正了解 Oracle SQL Optimizer google Jonathan Lewis，阅读他的书，阅读他的博客，查看他的网站，这个人很了不起，他总是生成测试用例。
create table mytab nologging as (
select mod(rownum, 3) x, rownum y, mod(rownum, 3) z from all_objects, (select 'x' from user_tables where rownum < 4)
);
create index i on mytab (x, y, z);
exec dbms_stats.gather_table_stats(ownname=>'DBADMIN',tabname=>'MYTAB', cascade=>true);
set autot trace exp
select * from mytab where y=5000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
1 0 INDEX (SKIP SCAN) OF 'I' (INDEX) (Cost=1 Card=1 Bytes=10)
回答by stuandgravy
Up to version Oracle 8an index will never be used unless the first column is included in the SQL.
在 Oracle 8 之前，除非 SQL 中包含第一列，否则永远不会使用索引。
In Oracle 9i the Skip Scan Index Accessfeature was introduced, which lets the Oracle CBO attempt to use indexes even when the prefix column is not available.
在 Oracle 9i中引入了跳过扫描索引访问特性，即使前缀列不可用，它也允许 Oracle CBO 尝试使用索引。
Good overview of how skip scan works here: http://www.quest-pipelines.com/newsletter-v5/1004_C.htm
这里很好地概述了跳过扫描的工作原理：http: //www.quest-pipelines.com/newsletter-v5/1004_C.htm
