聯合索引優化方式選擇
聯合索引的使用，如果在表的兩個字段上建立聯合索引，在查詢時如果謂詞中沒有出現第一個字段，仍然可以通過index skip scan的方式實現索引訪問的方式。但是要注意的是不是所有的情況使用index skip scan都是最優的。當聯合索引的第一個字段相同的值很多，大部分是相同的，這種情況下使用index skip scan的方式是最優的。
SQL> create table t as select 1 id, object_name from dba_objects;
Table created.
SQL> insert into t select 2, object_name from dba_objects;
50319 rows created.
SQL> insert into t select 3, object_name from dba_objects;
50319 rows created.
SQL> insert into t select 4, object_name from dba_objects;
50319 rows created.
SQL> commit ;
Commit complete.
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 50319
2 50319
4 50319
3 50319
可以看到表t的值集中在ID為1,2,3,4幾個值上面，這時選擇Index Skip Scan的訪問方式是最優的。分析如下：
SQL> select * from t where object_name='TEST';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3688940926
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IND_T | 7 | 189 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='TEST')
filter("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
13 physical reads
0 redo size
388 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
當使用hints使其采用全表掃描的方式時
SQL> select /*+ full(t) */ * from t where object_name='TEST';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 203 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 7 | 189 | 203 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
900 consistent gets
0 physical reads
0 redo size
388 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
由上面可以看到，使用Index Skip Scan時只掃描了14個數據塊，而采用全表掃描則掃描了900個數據塊。
但是兩外一種情況確實截然相反的，當聯合索引的第一個值重復很少時，使用全表掃描的效率卻要高一些。
