建立在多个列上的联合索引之研究和疑问 (摘自IT_PUB)

建立在多个列上的联合索引之研究和疑问

最近才发现这个问题，不知是不是以前有人讨论过
创建了联合索引，如create index idx_emp on emp(id,name,sal,deptno)
发现在不同条件的查询中对联合索引的使用是不同的。

示例
注：这里优化模式都是基于规则的，以避免使用oracle自己产生的执行计划

SQL> select * from emp where id<20 and deptno<5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 INDEX (RANGE SCAN) OF 'IDX_EMP' (NON-UNIQUE)
执行计划显示使用到了联合索引idx_emp

SQL> select * from emp where sal>2000 and deptno>9;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'EMP'

SQL> select * from emp where sal>2000 and NAME LIKE '%MARK%';
Execution Plan
------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'EMP'
上面两个查询显示：
如果where子句条件里没有id列，执行计划执行的是全表扫描

SQL> select /*+ index(emp)*/* from emp where sal>2000 and deptno>9;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=26 Card=5 Bytes=265)
1 0 INDEX (FULL SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=26 Card=5 Bytes=265)
如果强制通过HINT使用索引访问，虽然用到了索引idx_emp，但是对索引做的是一个"FULL SCAN"，不同于第一个查询的RANGE SCAN。

这里有几个问题不明：
1 联合索引存储的是什么内容，使用联合索引查询时是怎样引用索引的？
2 为什么在WHERE子句中不使用ID列就不会自动使用索引访问？跟索引条目存储的具体内容有关吗？
3 FULL SCAN) OF 'IDX_EMP' 这种访问方式对性能的影响是怎样的？会比全表扫描快吗？
4 另外，据公司的其他高人说，创建联合索引时，如CREATE INDEX I ON EMP(ID,NAME,DEPTNO)，重复值多的列如DEPTNO应该往后放，重复值少的列如ID应该放在前面，这样会提高查询的速度，对不对？是什么原因？

回复1:
1 应该阅读一下Oracle的概念手册先
2　这个和Rule优化器的规则有关
A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.
Consider this CREATE INDEX statement:
CREATE INDEX comp_ind ON tab1(x, y, z);
These combinations of columns are leading portions of the index: x, xy, and xyz.
These combinations of columns are not leading portions of the index: yz, y, and z.

3 对索引的全扫描是一种折衷的方法吧　不一定要比全表扫描快，要看你要获取的内容
4 你说的高人说的有一定道理

回复2:
在8i中，如果想使用到复合索引，必须在where语句中引用到索引的先导列(也就是索引的第一个字段)
在9i中，Oralce提供了一种新特性:跳跃式索引扫描，Oracle声称对用户透明，可实际上如果你想使用到这个新特性，必须添加index_ss提示
e.g
SQL> select /*+ index_ss(emp)*/* from emp where sal>2000 and deptno>9;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=5 Card=5 Bytes=265)
1 0 INDEX (SKIP SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=5 Card=5 Bytes=265)



回复3:跳跃式索引扫描的结构猜想
 http://www.itpub.net/showthread.php?threadid=85948