【每日一摩斯】-Index Skip Scan Feature (212391.1)
INDEX Skip Scan，也就是索引快速扫描，一般是指谓词中不带复合索引第一列，但扫描索引块要快于扫描表的数据块，此时CBO会选择INDEX SS的方式。
官方讲的，这个概念也好理解，如果将复合索引看做是一个分区表，其中分区主键(这里指的是复合索引的首列)定义了存储于此的分区数据。在每个键(首列)下的每行数据都将按照此键排序。因此在SS，首列可以被跳过，非首列可以作为逻辑子索引访问。因此一个“正常”的索引访问可以忽略首列。
复合索引被逻辑地切分成更小的子索引。逻辑子索引的个数取决于初始列的cardinality。因此尽管首列未出现在谓词中，也可能使用这个索引。、
另外，需要吧补充一点：当复合索引的第一个字段的值重复率非常低时，扫描索引的效率会比全表扫描更高，这是CBO才可能会选择使用INDEX Skip Scan的方式访问数据。
这里比较奇怪的是：
使用9i时，未使用INDEX Skip Scan：
SQL> create table at2(a varchar2(3),b varchar2(10),c varchar2(5));
Table created.
SQL> begin
2 for i in 1..1000
3 loop
4 insert into at2 values('M', i, 'M');
5 insert into at2 values('F', i, 'F');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index at2_i on at2(a,b,c);
Index created.
SQL> exec dbms_stats.gather_table_stats(OWNNAME => NULL, TABNAME => 'at2', CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select * from at2 where b='352';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=14)
1 0 INDEX (FAST FULL SCAN) OF 'AT2_I' (NON-UNIQUE) (Cost=2 Car
d=2 Bytes=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
447 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
使用10g的，则使用了INDEX Skip Scan：
SQL> select * from full_tbl where object_name='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293869270
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 55 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FULL_TBL | 2 | 58 | 55 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
230 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
难道9i和10g在选择INDEX Skip Scan还有什么不同么？
