复合索引和INDEXSKIPSCAN - 数据库编程

今天是2014-01-21，在此学习一下复合索引和INDEX SKIP SCAN;

复合索引很简单无非就是在创建索引的时候指定接字段，但是要注意字段的选择是有一定的可参考性的，在字段选择的时候我们一般将where条件之后经常使用的字段创建为复合索引，也就是说where条件自居中不同的键一起频繁出现，且使用“与”操作这些列时复合索引是不错的选择。

eg：

SQL> select index_type,index_name,table_name from user_indexes where table_name=upper('dept');

INDEX_TYPE                  INDEX_NAME                     TABLE_NAME
--------------------------- ------------------------------ ------------------------------
NORMAL                      DEPT_PK                        DEPT

SQL> drop index dept_pk;
drop index dept_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table dept drop constraint dept_pk;
alter table dept drop constraint dept_pk
                                 *
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

SQL>  select constraint_name,constraint_type,table_name,status from user_constraints where table_name in ('DEPT','EMP');

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
DEPT_PK                        P DEPT                           ENABLED
EMP_FK                         R EMP                            ENABLED

SQL> ALTER TABLE EMP DROP CONSTRAINT EMP_FK;

Table altered.

SQL> ALTER TABLE DEPT DROP CONSTRAINT DEPT_PK;

Table altered.

SQL>

创建复合索引：

SQL>  create index dept_idx1 on dept(deptno,dname);

Index created.

SQL> set autotrace trace exp
SQL> select * from dept where deptno=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2855125856

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    18 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    18 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEPT_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20)

SQL>

可以看到在只查询前导列deptno的时候，出现了索引范围扫描，但是由于loc字段没有在复合索引列中，那么还需要增加对表的扫描，无疑增加了额外的I/0，。

重新选择复合索引列值：

eg：

SQL> set autotrace off
SQL> drop index dept_idx1;

Index dropped.

SQL> create index dept_idx1 on dept(deptno,dname,loc);

Index created.

SQL> set autotrace trace exp
SQL> select * from dept where deptno=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2571496166

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    18 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DEPT_IDX1 |     1 |    18 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=20)

SQL>

可以看到在选择复合索引的列值是应该注意的地方。在此只是一个非常简单的例子，但是却反应了一个很大问题所在。

对于 index skip scan是从oracle 9I引入的，当没引入该技术时，在复合索引中，如果where条件没有使用到前导列，那么就走全表扫描而不使用索引，在9I之后该技术的引入才打破了这一局限。

官方介绍：

Inde

复合索引和INDEXSKIPSCAN(一)