复合索引和INDEXSKIPSCAN

今天是2014-01-21，在此学习一下复合索引和INDEX SKIP SCAN; 复合索引很简单无非就是在创建索引的时候指定接字段，但是要注意字段的选择是有一定的可参考性的，在字段选择的时候我们一般将where条件之后经常使用的字段创建为复合索引，也就是说where条件自居

今天是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之后该技术的引入才打破了这一局限。

官方介绍：

Index skip scans improve index scans by non-prefix columns since it is often faster to scan index blocks than scanning table data blocks. A non-prefix index is an index which does not contain a key column as its first column.

This concept is easier to understand if one imagines a prefix index to be similar to a partitioned table. In a partitioned object the partition key (in this case the leading column) defines which partition data is stored within. In the index case every row underneath each key (the prefix column) would be ordered under that key. Thus in a skip scan of a prefixed index, the prefixed value is skipped and the non-prefix columns are accessed as logical sub-indexes. The trailing columns are ordered within the prefix column and so a 'normal' index access can be done ignoring the prefix.

In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.

也就是说，索引跳跃式扫描及时通过逻辑子索引消除或跳过一个复合索引，这个时候复合索引可以认为化成了几个逻辑子索引。如果在where条件中没有使用前导列就会采用索引跳跃式扫描。

在看如下例子：

SQL> select dbms_metadata.get_ddl('INDEX','EMP_IDX1','AMY') FROM DUAL; DBMS_METADATA.GET_DDL('INDEX','EMP_IDX1','AMY') -------------------------------------------------------------------------------- CREATE INDEX "AMY"."EMP_IDX1" ON "AMY"."EMP" ("EMPNO", "ENAME", "SAL") PCT SQL> SQL> set autotrace trace exp SQL> SQL> select * from emp where sal=1250; Execution Plan ---------------------------------------------------------- Plan hash value: 954130750 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | EMP_IDX1 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAL"=1250) filter("SAL"=1250) SQL> [oracle@oracle-one ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 21 15:24:25 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> conn amy/rhys Connected. SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> select * from emp where sal=1250; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 SQL> alter session set events '10046 trace name context off'; Session altered. SQL> SQL> select * from v$diag_info; INST_ID NAME VALUE ---------- ------------------------------------------------------------ ------------------------------------------------------------ 1 Diag Enabled TRUE 1 ADR Base /opt/app/oracle 1 ADR Home /opt/app/oracle/diag/rdbms/rhys/RHYS 1 Diag Trace /opt/app/oracle/diag/rdbms/rhys/RHYS/trace 1 Diag Alert /opt/app/oracle/diag/rdbms/rhys/RHYS/alert 1 Diag Incident /opt/app/oracle/diag/rdbms/rhys/RHYS/incident 1 Diag Cdump /opt/app/oracle/diag/rdbms/rhys/RHYS/cdump 1 Health Monitor /opt/app/oracle/diag/rdbms/rhys/RHYS/hm 1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_4694.trc 1 Active Problem Count 1 1 Active Incident Count 1 11 rows selected. SQL> 看一下执行计划： [oracle@oracle-one script]$ tkprof RHYS_ora_4694.trc tkprof_4694.txt sys=no aggregate=yes explain=amy/rhys record=record_sql.sql waits=yes TKPROF: Release 11.2.0.4.0 - Development on Tue Jan 21 15:31:42 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. [oracle@oracle-one script]$ [oracle@oracle-one script]$ vi tkprof_4694.txt TKPROF: Release 11.2.0.4.0 - Development on Tue Jan 21 15:31:42 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: RHYS_ora_4694.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: ajqsk3f0nk06d Plan Hash: 954130750 select * from emp where sal=1250 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.03 0 4 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 (AMY) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 2 2 2 TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=41 us cost=2 size=32 card=1) 2 2 2 INDEX SKIP SCAN EMP_IDX1 (cr=2 pr=0 pw=0 time=42 us cost=1 size=0 card=1)(object id 88000) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'EMP' (TABLE) 2 INDEX MODE: ANALYZED (SKIP SCAN) OF 'EMP_IDX1' (INDEX) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 14.93 14.93 ******************************************************************************** SQL>