组合索引

最新推荐文章于 2020-12-20 05:19:23 发布

EVISWANG

最新推荐文章于 2020-12-20 05:19:23 发布

阅读量476

点赞数

分类专栏： Index

本文链接：https://blog.csdn.net/EVISWANG/article/details/50672600

版权

Index 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

定义：当某个索引包含多个列时，我们称这个索引为“组合索引”或“复合索引”。

oracle 9i引入索引跳跃式扫描增加了优化器在使用组合索引时的选择，所以您在选择索引的列的顺序时应该谨慎。

索引跳跃式扫描  （index skip scan）
索引跳跃式扫描适合于组合索引，比如(deptno,sal)，当根据组合索引的第一个列deptno做查询时，
通常是可以用的到索引的，当你想使用第二个列sal作为筛选条件时，oracle可以使用索引跳跃式扫
描，跳跃式扫描适合于第一个列值重复比较多，第二个列唯一值比较多的情况如果每个列重复值都
比较多那么这个索引没啥意义
SQL> show user
USER is "SCOTT"
SQL> create table evis as select * from emp;

Table created.

SQL> create index evis_idx on evis(deptno, sal);

Index created.

SQL> set autotrace on
SQL> set linesize 1000
SQL> select * from evis where deptno=10;

     EMPNO ENAME			  JOB				     MGR HIREDATE		   SAL	     COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ---------- ----------
      7934 MILLER			  CLERK 			    7782 23-JAN-82		  1300			  10
      7782 CLARK			  MANAGER			    7839 09-JUN-81		  2450			  10
      7839 KING 			  PRESIDENT				 17-NOV-81		  5000			  10


Execution Plan
----------------------------------------------------------
Plan hash value: 1062579367

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

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

   2 - access("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
	 15  recursive calls
	  0  db block gets
	 22  consistent gets
	  0  physical reads
	  0  redo size
       1159  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  3  rows processed


SQL> select * from evis where sal<1500;

     EMPNO ENAME			  JOB				     MGR HIREDATE		   SAL	     COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ---------- ----------
      7369 SMITH			  CLERK 			    7902 17-DEC-80		   800			  20
      7521 WARD 			  SALESMAN			    7698 22-FEB-81		  1250	      500	  30
      7654 MARTIN			  SALESMAN			    7698 28-SEP-81		  1250	     1400	  30
      7876 ADAMS			  CLERK 			    7788 23-MAY-87		  1100			  20
      7900 JAMES			  CLERK 			    7698 03-DEC-81		   950			  30
      7934 MILLER			  CLERK 			    7782 23-JAN-82		  1300			  10

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1465685010

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     6 |   522 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EVIS |     6 |   522 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("SAL"<1500)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
       1246  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  6  rows processed

SQL> analyze table evis compute statistics;

Table analyzed.

SQL> select * from evis where sal<1500;

     EMPNO ENAME			  JOB				     MGR HIREDATE		   SAL	     COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ---------- ----------
      7934 MILLER			  CLERK 			    7782 23-JAN-82		  1300			  10
      7369 SMITH			  CLERK 			    7902 17-DEC-80		   800			  20
      7876 ADAMS			  CLERK 			    7788 23-MAY-87		  1100			  20
      7900 JAMES			  CLERK 			    7698 03-DEC-81		   950			  30
      7521 WARD 			  SALESMAN			    7698 22-FEB-81		  1250	      500	  30
      7654 MARTIN			  SALESMAN			    7698 28-SEP-81		  1250	     1400	  30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1765847587

----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       |     2 |    64 |     2	 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EVIS     |     2 |    64 |     2	 (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN	    | EVIS_IDX |     2 |       |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("SAL"<1500)
       filter("SAL"<1500)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
       1288  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  6  rows processed

SQL>

一般来说，索引的第一列应该是最有可能在where子句中使用的列，并且是索引中最具选择性的列。

在引入跳跃式扫描功能之前，只有当索引的前导列也出现在where子句中时，查询才能使用索引。

SQL> drop index evis_idx;

Index dropped.
SQL> create index evis_idx on evis(empno, ename, deptno); 

Index created.

SQL> 

SQL> select table_name, index_name, column_name, column_position from evis
  2  order by table_name, index_name, column_position
  3  ;
 
TABLE_NAME                     INDEX_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------                                                                                       1
EVIS                           EVIS_IDX                       EMPNO                                                                                          1
EVIS                           EVIS_IDX                       ENAME                                                                                          2
EVIS                           EVIS_IDX                       DEPTNO                                                                                         3
如果没有跳跃式扫描功能，除非在where子句中使用前导列（empno）,否则oracle一般不会使用这个索引。
SQL> select job, empno from evis where ename='RICH';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1765847587

----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       |     1 |    15 |     2	 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EVIS     |     1 |    15 |     2	 (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN	    | EVIS_IDX |     1 |       |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("ENAME"='RICH')
       filter("ENAME"='RICH')


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  1  consistent gets
	  0  physical reads
	  0  redo size
	398  bytes sent via SQL*Net to client
	512  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

SQL> 
因为ename不是索引的前导列，优化器可能不会选择使用该索引。随着在oracle9i中引入跳跃式扫描功能，即使在where
子句中没用指定empno值，优化器也可能会选择使用该索引。另外，优化器也可能会选择索引快速全扫描（Index Fast Full Scan）
或全表扫描。当然，如果在where中使用第三列也会产生相同的效果

如下实例中使用了前导列用作empno为where子句的限制条件，以便oralce可以使用改索引。
SQL> select  job, empno from evis where empno=30;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1062579367

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

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

   2 - access("EMPNO"=30)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  1  consistent gets
	  0  physical reads
	  0  redo size
	398  bytes sent via SQL*Net to client
	512  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

SQL> 
2种最常见的索引扫描方式是唯一扫描和范围扫描，