oracle 索引访问方式

oracle 为索引的访问提供了很多种访问方式，以下是Oracle提供的常用的扫描方式

 

1.INDEX UNIQUE SCAN（索引唯一扫描）

   在一个有主键的表上，当你使用主键做为查询条件，Oracle会是用该方式来访问索引，并且在索引中已经对相关的列进行了排序。

SQL> select * from dept where deptno=10;              

Execution Plan              
----------------------------------------------------------              
Plan hash value: 2852011669              
----------------------------------------------------------------------------         
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|               
----------------------------------------------------------------------------                
|   0 | SELECT STATEMENT            |         |     1 |    18 |     1   (0)|              
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)|            
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|           
----------------------------------------------------------------------------

2.INDEX RANGE SCAN（索引范围扫描）

   在一个有普通索引的表中，当你使用索引中的列进行作为查询条件时，你会发现Oracle会使用索引的范围扫描。当然，在唯一索引上使用> ,<类似的查找条件，也会使用索引范围扫描。

SQL> select * from dept where deptno>10;          


Execution Plan          
----------------------------------------------------------          
Plan hash value: 2985873453          
----------------------------------------------------------------------------          
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|          
----------------------------------------------------------------------------          
|   0 | SELECT STATEMENT            |         |     3 |    54 |     2   (0)|          
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     3 |    54 |     2   (0)|          
|*  2 |   INDEX RANGE SCAN          | PK_DEPT |     3 |       |     1   (0)|          
----------------------------------------------------------------------------

3.INDEX FULL SCAN (全索引扫描)

  顾名思义，就是对这个索引进行扫描，例子如下：

SQL> select count(*) from dept;          


Execution Plan          
----------------------------------------------------------          
Plan hash value: 3051237957          

--------------------------------------------------------------------          
| Id  | Operation        | Name    | Rows  | Cost (%CPU)| Time     |          
--------------------------------------------------------------------          
|   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 |          
|   1 |  SORT AGGREGATE  |         |     1 |            |          | --这一行并没有执行排序，而是由于count引起的。         
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |     1   (0)| 00:00:01 |          
--------------------------------------------------------------------

 4.INDEX FAST FULL SCAN(索引快速全扫描)

     这个有点类似于全表扫描，会使用Oracle多块读的特性，并且数据没有排序。

SQL> select count(object_id) from t;        


Execution Plan        
----------------------------------------------------------        
Plan hash value: 2371838348        

-------------------------------------------------------------------------------        
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |        
-------------------------------------------------------------------------------        
|   0 | SELECT STATEMENT      |       |     1 |    13 |    49   (0)| 00:00:01 |        
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |        
|   2 |   INDEX FAST FULL SCAN| IDX_T | 69356 |   880K|    49   (0)| 00:00:01 |        
-------------------------------------------------------------------------------

对于b树索引常用的扫描就是以上4种方式，下面是tom描述的INDEX FULL SCAN和INDEX FAST FULL SCAN的区别，关于这个下面这个讲的很清楚Z：

An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.

An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multiblock IO for this operation.

5. INDEX SKIP SCAN（索引跳跃扫描）

 这个主要用在组合索引中，并且当索引的第一个键值的可选择性不高时，后面使用的键值可选择性较高时使用该索引，性能会好很多。

dbms_stats.gather_table_stats('SC','TABLENAME',CASCADE=>TRUE)

  

SQL> select * from t where object_id=10; 


Execution Plan 
---------------------------------------------------------- 
Plan hash value: 3878739043 

-------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time| 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |        |     1 |    97 |    32   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    97 |    32   (0)| 00:00:01 | 
|*  2 |   INDEX SKIP SCAN           | IDX_T2 |     1 |       |    31   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------