《Pro Oracle SQL》Chapter 3--3.2.5 Index Full Scan
Index Full Scan 索引全扫描 (page 103)An index full scan is chosen under several conditions including: when there is no predicate but the
column list can be satisfied through an index on a column, the predicate contains a condition on a
non-leading column in an index, or the data can be retrieved via an index in sorted order and save the
need for a separate sort step. Listing 3-13 shows an example of each of these cases.
选中索引全扫描包括下列几种条件：没有谓词但是可通过列上的索引满足(查询)列集，谓词包含一个在索引中非前置列上的条件，或者数据能通过索引按顺序检索而省去了单独排序的步骤。
Listing 3-13. Index Full Scan Examples
SQL> set autotrace traceonly explain
SQL> select email from hr.employees ;
--HR EMP_EMAIL_UK Unique EMAIL
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0) |
| 1 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 856 | 1 (0) |
----------------------------------------------------------------------
SQL>
SQL> select first_name, last_name from hr.employees
2 where first_name like 'A%' ;
--HR EMP_NAME_IX Normal LAST_NAME, FIRST_NAME
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0) |
|* 1 | INDEX FULL SCAN | EMP_NAME_IX | 3 | 45 | 1 (0) |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME" LIKE 'A%')
filter("FIRST_NAME" LIKE 'A%')
SQL> select * from hr.employees order by employee_id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2186312383
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0) |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 107 | 7276 | 3 (0) |
| 2 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | | 1 (0) |
----------------------------------------------------------------------------------
SQL> select * from hr.employees order by employee_id desc ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2761389396
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0) |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 107 | 7276 | 3 (0) |
| 2 | INDEX FULL SCAN DESCENDING | EMP_EMP_ID_PK | 107 | | 1 (0) |
----------------------------------------------------------------------------------
An index full scan operation will scan every leaf block in the index structure, read the rowids for
each entry, and retrieve the table rows. Every leaf block is accessed. This is often more efficient than
doing a full table scan as the index blocks will contain more entries than the table blocks will,
therefore fewer overall blocks may need to be accessed. In cases where the columns needed to satisfy
the column list are all present as part of the index entry, the table access step is avoided as well. This
means that choosing an index full scan operation will be more efficient than reading all the table
blocks.
索引全扫描操作将扫描在索引结构中的每个叶块，读取每条记录的rowid，检索表的行集。每个叶块都被访问到。这通常都比做全表扫描更加有效率因为索引块比表块包含更多的记录。因此总体上需要访问的块要少一些。如果列集中所需的列都是索引记录的一部分，还可以省略表访问步骤。这意味着选择索引全扫描操作将比读取所有表块更有效率。
You may have noticed in the last example that the index full scan operation also has the ability to
read in descending order to avoid the need for a separate descending ordered sort request. There is
another optimization for index full scans. This optimization occurs when a query requests the
minimum or maximum column value and that column is indexed. Listing 3-14 shows an example of
this operation choice.
页:
[1]