索引扫描访问方法

索引建立在表中的一个或者多个列或者是列的表达式上，将列值和行编号（rowid）一起存储。行编号是一个用来唯一标记表中行的伪列。它包含两个地址，其一为指向数据表中包含该行的块所存放数据文件的地址，另一个是可以直接定位到数据行自身的这一行在数据块中的地址。

rowid转换物理文件编号file_id：dbms_rowid.rowid_to_absolute_fno(rowid,user,'表名')

rowid转换物理块编号block_no：dbms_rowid.rowid_block_number(rowid);

rowid转换物理数据行编号：dbms_rowid.rowid_row_number(rowid)

索引结构

索引的高度是指第一个分支块（也就是数据块）到叶子块的分支层级数。叶子分支包含排序后的索引对象的值和行编号。

随着新行加入表中，新的索引条目也会增加到块中，直到新的条目再也加不进去为止。此时oracle就会分配两个新的索引块并将所有索引条目加入这两个新的叶子块中，之前被填满的那个地址（relative block address，RBA）和一个表明相关叶子块中最低索引值（即排序中的最小值）的值组成。利用根数据块中的这个信息，oracle就可以搜索索引以找到存有所需值的特定叶子块。目前为止，该索引高度为2，blevel为1。

索引扫描类型

索引扫描类型，包括索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描以及索引快速扫描。

聚簇因子，索引的聚簇因子向优化器表名了具有同样索引值的数据行是不是存放在同一个或连续的一系列数据块中，或者数据行是否被分散存放在表的多个数据块中。聚簇因子是通过每次当前行的块编号与前一行的块编号不同的时候对计数器加1来计算的。

索引唯一扫描

当谓语中包含使用union或者primary key索引的列作为条件的时候就会选用索引唯一扫描。这种类型的索引能够保证对于某个特定的值只返回一行数据。

在这种情况下，索引结构将会被从根到叶子进行遍历直到某个条目，取出其行编号，然后使用这个行号来访问包含这一行的表数据块。计划中table access by index rowid步骤表明对于表数据块的访问。除非某特定情况，例如数据行是链式的或者包含存储在别处的大对象（LOB）。需要访问的数据块数总是等于索引的高度加一。

索引唯一扫描示例：

SQL> set autot traceonly exp
SQL> select * from employees where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash 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 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("EMPLOYEE_ID"=100)

因为在谓语中用了主键employee_id。

索引范围扫描

当谓语中包含返回一定范围数据的条件时就会选用索引范围扫描。所指定条件可以是“<>”,“like”,“between”甚至等号“=”等运算符号。

示例如下：

SQL> select * from employees where department_id=60;
Execution Plan
----------------------------------------------------------
Plan hash 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 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("DEPARTMENT_ID"=60)

当选择了索引范围扫描时，执行计划中的谓语信息将会示出用来访问索引的条件。

在上面例子中，计划中的第2步上有个星号。这是一个表明在计划的下面列出了这一步的谓语信息的标志。在那里，可以看到一个条目表明索引条目访问通过条件department_id=60来确认。

索引范围扫描的最后一个好处在于，它可以使用一个升序排列的索引（默认值）来返回降序排列的数据行。这种情况可能发生在查询中包含有索引列上的order by 子句的时候。

示例如下：

SQL> select * from employees
 2 where department_id=90 or department_id=100
 3 order by department_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 621 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

DESCENDING表明索引降序排列行数据。上面例子中，索引条目实际上被以相反的顺序读取了。

索引全扫描

在好几种情况下都会选择索引全扫描，包括：

1. 当没有谓语但是所需获取列的列表可以通过其中一列的索引来获得。
2. 谓语中包含一个谓语条件，该谓语引用的列在索引中为非引导列。
3. 数据可以通过一个排过顺序的索引来获取并且会省去单独排序的步骤。

第一点的示例：

SQL> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash 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 |

第2点示例：

SQL> select first_name,last_name from employees where first_name like 'A%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_NAME_IX | 3 | 45 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("FIRST_NAME" LIKE 'A%')
 filter("FIRST_NAME" LIKE 'A%')

上面示例中，first_name，last_name是组合索引，并且last_name为引导列，而first_name为非引导列。

SQL> select * from employees order by employee_id;
Execution Plan
----------------------------------------------------------
Plan hash 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 |
---------------------------------------------------------------------------------------------

上面提示，以employee_id排序。

在查询字段列表中所有字段都是索引条目的一部分的时候，就能够完全跳过访问表的步骤。

另外可以用索引全扫描快速查询索引列的最大最小值，如下：

SQL> select max(department_id) from employees;
Execution Plan
----------------------------------------------------------
Plan hash 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 |
------------------------------------------------------------------------------------------------

索引跳跃扫描

当谓语中包含位于索引中非引导列上的条件，并且引导列的值是唯一的时候会选择索引跳跃扫描。早期版本中，如果一个谓语使用了索引中的非引导列，则该索引不能被选用。跳跃扫描通过逻辑上将多个列的索引分解为多个较小的子索引来实现。逻辑子索引的数目有索引引导列中唯一值的个数决定。引导列中唯一值的数目越多，所需要建立的逻辑索引页越多。

对于那些有组合索引的大型表而言，索引跳跃式扫描特性可以提供一个快速访问，即使索引的第一列没有在限制条件中使用。

oracle的优化器(这里指的是cbo)能对查询应用index skip scans至少要有几个条件:

1. 优化器认为是合适的.
2. 索引中的前导列的唯一值的数量能满足一定的条件.
3. 优化器要知道前导列的值分布(通过分析/统计表得到)
4. 合适的sql语句

SQL> create table test
 2 as 
 3 select decode(mod(rownum,2), 0, 1, 2 ) a,
 4 rownum-1 b,
 5 rownum-2 c,
 6 rownum-3 d,
 7 rownum-4 e
 8 from all_objects
 9 /
Table created.
SQL> set autotrace off
SQL> select distinct a from test;
 A
----------
 1
 2

索引前导列唯一值较少。

SQL> create index test_idx on test(a,b,c);
Index created.
SQL> analyze table test compute statistics
 2 for table
 3 for all indexes
 4 for all indexed columns;
Table analyzed.
SQL> set autot traceon exp
SQL> select * from test where b=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 2705879578
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_IDX | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("B"=99)
 filter("B"=99)

索引里面的前导列的唯一值的个数将会影响优化器是否选择跳跃式索引扫描。

索引快速扫描

当优化器选择索引快速全扫描的时候，所有索引块都将通过多块读取来进行读取。这种索引类型的索引扫描是用来在查询列表中所有字段都包含在索引中并且索引中至少有一列具有非空约束时替代全表扫描的。

扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

SQL> set autot traceon exp
SQL> alter table employees modify (email null);
Table altered.
SQL> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
SQL> alter table employees modify(email not null);
Table altered.
SQL> select /*+index_ffs(a EMP_EMAIL_UK)*/email
 2 from EMPLOYEES a where email like 'D%';
Execution Plan
----------------------------------------------------------
Plan hash value: 1103687806
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 48 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| EMP_EMAIL_UK | 6 | 48 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

上面例子说明，索引快速扫描运算为了被选择是如何依赖于非空约束的，如果没有这个约束，将会选择全表扫描运算。