聯(lián)合索引優(yōu)化方式選擇

聯(lián)合索引的使用，如果在表的兩個(gè)字段上建立聯(lián)合索引，在查詢(xún)時(shí)如果謂詞中沒(méi)有出現(xiàn)第一個(gè)字段，仍然可以通過(guò)index skip scan的方式實(shí)現(xiàn)索引訪問(wèn)的方式。但是要注意的是不是所有的情況使用index skip scan都是最優(yōu)的。當(dāng)聯(lián)合索引的第一個(gè)字段相同的值很多，大部分是相同的，這種情況下使用index skip scan的方式是最優(yōu)的。

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幾個(gè)值上面，這時(shí)選擇Index Skip Scan的訪問(wèn)方式是最優(yōu)的。分析如下：

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

當(dāng)使用hints使其采用全表掃描的方式時(shí)

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時(shí)只掃描了14個(gè)數(shù)據(jù)塊，而采用全表掃描則掃描了900個(gè)數(shù)據(jù)塊。

但是兩外一種情況確實(shí)截然相反的，當(dāng)聯(lián)合索引的第一個(gè)值重復(fù)很少時(shí)，使用全表掃描的效率卻要高一些。