《Pro Oracle SQL》Chapter3--3.2.3 Index Unique Scan

Index Unique Scan    索引唯一扫描     (page 78)

    An index unique scan is chosen when a predicate contains a condition using a column defined with a
UNIQUE or  PRIMARY KEY  index.  These types of indexes guarantee that only one row will ever be returned for a specified value.  In this cases, the index structure will be traversed from root to leaf block to a single entry, retrieve the rowid, and use it to access the table data block containing the one row.  The TABLE ACCESS BY INDEX ROWID  step in the plan indicates the table data block access.  The number of block accesses required will always be equal to the height of the index plus one unless there are special circumstances like the row is chained or contains a LOB that is stored elsewhere.   Listing 3-10 shows an example query that will produce an index unique scan plan.
    当谓词包含一条件使用了定义了UNIQUE或PRIMARY KEY索引的某列时，索引唯一扫描将被选择。这种类型的索引保证对于指定的值总是只返回一行。在这种情况下，索引结构遍历将从根到叶块再到单个记录,检索rowid，再用它访问表包含那行的数据块。计划中TABLE ACCESS BY INDEX ROWID 步骤标示出表数据块访问。需要访问的块数将总是等于索引高度加1，除非特殊的环境像行成串了或者某处包含LOB字段。 列表3-10展示了一列子查询将生成一索引唯一扫描计划。

Listing 3-10. Index Unique Scan
SQL> set autotrace traceonly explain
SQL>
SQL> select * from hr.employees where employee_id = 100;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
 
----------------------------------------------------------------------------------
|Id  | Operation                                            | Name                     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                               |     1     |    82  |     2   (0)       |
|  1 |  TABLE ACCESS BY INDEX ROWID      | EMPLOYEES         |     1     |    82  |     2   (0)       |
|* 2 |   INDEX UNIQUE SCAN                       | EMP_EMP_ID_PK  |     1     |          |     1   (0)      |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID"=100)