索引扫描方式

1.索引唯一扫描：谓语中包含使用unique 或primary key 索引的列作为条件的时候就会选用索引唯一扫描：

SQL>set autotrace traceonly exp

SQL>select * from hr.employees where employee_id =100;

ExecutionPlan

----------------------------------------------------------

Planhash value: 1833546154

---------------------------------------------------------------------------------------------

|Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |   69 |     1   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    1 |    69 |     1  (0)| 00:00:01 |

|*  2 |  INDEX UNIQUE SCAN         |EMP_EMP_ID_PK |     1 |       |    0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   2 - access("EMPLOYEE_ID"=100)

索引结构将会被从根到叶子进行遍历直到某个条目，取出其行编号，然后使用这个行编号来访问包含这一行的表数据块。

TABLE ACCESS BY INDEX ROWID 表明了对于表数据块的访问

 

2.索引范围扫描

谓词中包含将会返回一定范围数据的条件时，就会选用索引范围扫描。

SQL>select * from hr.employees where department_id = 60;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2056577954

-------------------------------------------------------------------------------------------------

|Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |     5 |  345 |     2   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    5 |   345 |     2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          |EMP_DEPARTMENT_IX |     5 |       |    1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

PredicateInformation (identified by operation id):

---------------------------------------------------

   2 -access("DEPARTMENT_ID"=60)

3.索引全扫描：当没有谓语但是所需获取列的列表可以通过其中一列的索引来获得，谓语中包含一个位于索引中非引导列上的条件，或者数据可以通过一个排过序的索引来获取并且会省去单独的排序步骤。

索引全扫描会扫描索引结构中的每一个叶子块，读取每个条目的行编号，并取出数据行。每个叶子块都要被访问。

SQL>select email from hr.employees;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2196514524

---------------------------------------------------------------------------------

|Id  | Operation        | Name         | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |   107 |  856 |     1   (0)| 00:00:01 |

|   1 | INDEX FULL SCAN | EMP_EMAIL_UK |  107 |   856 |     1  (0)| 00:00:01 |

---------------------------------------------------------------------------------

SQL>select * from hr.employees where

  2 first_name like 'A%';

ExecutionPlan

----------------------------------------------------------

Planhash value: 1445457117

-------------------------------------------------------------------------------

|Id  | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     3 |   207 |    3   (0)| 00:00:01 |

|*  1 | TABLE ACCESS FULL| EMPLOYEES |    3 |   207 |     3  (0)| 00:00:01 |

-------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

   1 - filter("FIRST_NAME" LIKE 'A%')

SQL>select * from hr.employees order by employee_id;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2186312383

---------------------------------------------------------------------------------------------

|Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |   107 | 7383 |     3   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |  107 |  7383 |     3  (0)| 00:00:01 |

|   2 |  INDEX FULL SCAN           | EMP_EMP_ID_PK|   107 |       |    1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

 

SQL>select * from hr.employees order by employee_id desc;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2761389396

 --------------------------------------------------------------------------------------------

|Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |   107 | 7383 |     3   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |  107 |  7383 |     3  (0)| 00:00:01 |

|   2 |  INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK |   107 |      |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

 

索引全扫描求最大/最小值的最优方法：

SQL>select min(department_id) from hr.employees ;

ExecutionPlan

----------------------------------------------------------

Planhash value: 613773769

------------------------------------------------------------------------------------------------

|Id  | Operation                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                   |     1|     3 |     1  (0)| 00:00:01 |

|   1 | SORT AGGREGATE            |                   |     1 |    3 |            |          |

|   2 |  INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |    3 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

SQL>select min(department_id), max(department_id) from hr.employees ;

ExecutionPlan

----------------------------------------------------------

Planhash value: 1756381138

--------------------------------------------------------------------------------

|Id  | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |     1 |     3 |    3   (0)| 00:00:01 |

|   1 | SORT AGGREGATE    |           |    1 |     3 |            |          |

|   2 |  TABLE ACCESS FULL| EMPLOYEES |  107 |   321 |     3  (0)| 00:00:01 |

--------------------------------------------------------------------------------

SQL>select (select min(department_id) from hr.employees) min_id,

  2    (select max(department_id) from hr.employees) max_id

  3  fromdual;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2189307159

 -----------------------------------------------------------------------------------------------

|Id  | Operation                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                   |     1 |      |     2   (0)| 00:00:01 |

|   1 | SORT AGGREGATE            |                   |     1 |    3 |            |          |

|   2 |  INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |    3 |     1   (0)| 00:00:01 |

|   3 | SORT AGGREGATE            |                   |     1 |    3 |            |          |

|   4 |  INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX |     1 |    3 |     1   (0)| 00:00:01 |

|   5 | FAST DUAL                 |                   |     1 |      |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

因为索引是按照排序后的顺序来存储的，所以最大/最小值必然在第一个和最后一个叶子块中。

当找最大和最小时，并没有真正的全扫描。仅仅是对根块，一个或多个分支块以及第一个和最后一个叶子块的扫描。

4.索引跳跃扫描

当谓语中包含位于索引中非引导列上的条件，并且引导列的值是唯一的时候就会选择索引跳跃扫描。

5.索引快速全扫描：

SQL>select email from hr.employees;

ExecutionPlan

----------------------------------------------------------

Planhash value: 2196514524

---------------------------------------------------------------------------------

|Id  | Operation        | Name         | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |   107 |  856 |     1   (0)| 00:00:01 |

|   1 |  INDEXFULL SCAN | EMP_EMAIL_UK |   107 |   856 |    1   (0)| 00:00:01 |

---------------------------------------------------------------------------------

 

如果Email列为空，则会选择全表扫描运算。