oracle优化模型中5种索引访问方法的分析
本文主要讨论了以下几种索引访问方法：1。索引唯一扫描（索引唯一扫描）
2。指数范围扫描（指数范围扫描）
三.索引完全扫描（索引完全扫描）
4。索引跳跃扫描（索引跳过扫描）
5。指数快速全扫描（指数快速全扫描）
索引唯一扫描（索引唯一扫描）
该指标的特点来访问数据的一个特定的值只返回一行数据时，通常使用独一无二的主键索引，在查询谓词中的列作为条件会选择这种扫描；获得高度的指标加上一个高度，除了一些特殊情况，如LOB对象存储。
复制代码代码如下所示：
SQL > AutoTrace traceonly解释
SQL SELECT * FROM hr.employees哪里employee_id > = 100；
执行计划
----------------------------------------------------------
计划哈希值：1833546154
---------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
---------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 69 1（0）| 00:00:01 |
| 1表访问的索引rowid |员工| | | | 1 69 1（0）| 00:00:01 |
2索引唯一扫描| * | | emp_emp_id_pk | 1 | | 0（0）| 00:00:01 |
---------------------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
2访问（employee_id= 100）
指数范围扫描（指数范围扫描）
谓词包含将返回一系列的数据条件，将选择范围索引，索引也只能是唯一的；指定的条件可以是（例如，在，= =）操作符之间，但是当使用时，如果使用通配符%，很可能使用扫描范围，因为条件太宽，下面是一个示例：
复制代码代码如下所示：
SQL SELECT * FROM hr.employees哪里department_id > = 30；
选择6行。
执行计划
----------------------------------------------------------
计划哈希值：2056577954
-------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
-------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 6 414 2（0）| 00:00:01 |
| 1表访问的索引rowid |员工| | | | 6 414 2（0）| 00:00:01 |
2索引范围扫描| * | | emp_department_ix | 6 | | 1（0）| 00:00:01 |
-------------------------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
2访问（department_id= 30）
统计
----------------------------------------------------------
8递归调用
0分贝块得到
7一致的获取
1物理读
0重做日志大小
1716个字节通过网络发送给客户机
从客户端接收的523个字节
2 SQL*Net切换到/从客户端
0类（内存）
0类（磁盘）
6行处理
范围扫描的范围要求对返回的数据数量进行精确分析，并且量程越大，执行全表扫描的可能性就越大。
复制代码代码如下所示：
SQL >选择department_id，计数（*）从department_id hr.employees组以计数（*）；
department_id计数（*）
和
一百零一
四百零一
一
七百零一
二百零二
一千一百零二
九百零三
六百零五
三百零六
一千零六
八千零三十四
五千零四十五
选择12行。
它使用50个数值来执行范围扫描。
SQL > AutoTrace traceonly解释
SQL SELECT * FROM hr.employees哪里department_id > = 50；
选择45行。
执行计划
----------------------------------------------------------
计划哈希值：1445457117
-------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
-------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 45 3105 3（0）| 00:00:01 |
1表访问全|员工| * | | | | 45 3105 3（0）| 00:00:01 |
-------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
1过滤器（department_id= 50）
统计
----------------------------------------------------------
0递归调用
0分贝块得到
10一致的获取
0物理读
0重做日志大小
4733个字节通过网络发送给客户机
从客户端接收的545个字节
4 SQL*Net切换到/从客户端
0类（内存）
0类（磁盘）
45行处理
您可以看到优化器在数据较大时仍然执行全表扫描。
索引范围扫描的一种优化方法是使用升序索引以降序的方式获取行。在索引列中包含子句的查询中，这种情况最为常见，因此可以避免一次操作。
复制代码代码如下所示：
SQL > AutoTrace traceonly解释
SQL SELECT * FROM hr.employees >
2 department_id在哪里（90, 100）
3阶的department_id desc；
执行计划
----------------------------------------------------------
计划哈希值：3707994525
---------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
---------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 9 621 2（0）| 00:00:01 |
| 1 | InList迭代器| | | | | |
| 2表访问的索引rowid | |员工| | | 9 621 2（0）| 00:00:01 |
|×3索引范围扫描降| emp_department_ix | | 9 | | 1（0）| 00:00:01 |
---------------------------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
3访问（department_id 或department_id = 90 = 100）
在本例中，索引条目的顺序相反，以避免读取、排序操作。
索引完全扫描（索引完全扫描）
索引完全扫描操作将扫描索引结构的每个叶块，读取每个项的行数并提取数据行。既然访问了每个索引叶块，那么它相对完整的表扫描的优势是什么呢事实上，因为包含在索引信息块少列，通常是键和rowid，所以对于同一块数据和索引项，索引键包含主要的索引块，所以如果从字段列表中的所有字段的查询的索引，可以完全跳过表数据访问方法的一部分，这个索引全扫描效率会更高。
有很多情况下，索引的全面扫描，几个典型的场景：
1，查询总是缺少谓词，但是得到的列可以直接通过索引获得。
复制代码代码如下所示：
SQL >选择电子邮件从hr.employees；
执行计划
----------------------------------------------------------
计划哈希值：2196514524
---------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
---------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 107 856 1（0）| 00:00:01 |
1索引全扫描| | | emp_email_uk | | | 107 856 1（0）| 00:00:01 |
---------------------------------------------------------------------------------
2，查询谓词包含索引中非引导列的条件。实际上，这取决于引导值的基值的大小。如果引导列的唯一值较小，则跳过扫描也是可能的。
复制代码代码如下所示：
已选择从first_name，last_name hr.employees
2在first_name like'a %；
执行计划
----------------------------------------------------------
计划哈希值：2228653197
--------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 3 45 1（0）| 00:00:01 |
1索引全扫描| * | | emp_name_ix | | | 3 45 1（0）| 00:00:01 |
--------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
1访问（first_namelike'a %）
滤波器（first_namelike'a %）
SQL >设置长2000000
SQL >选择dbms_metadata.get_ddl（'index '，'emp_name_ix '，'hr）从双；
dbms_metadata.get_ddl（'index '，'emp_name_ix '，'hr）
--------------------------------------------------------------------------------
创建索引HR。emp_name_ix 人力资源。员工（last_name
）
10 255 2 maxtrans PCTFREE initrans nologging计算统计
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)
0 1 1 pctincrease freelists空闲列表组buffer_pool默认flash_cache de
故障cell_flash_cache默认）
表空间实例
你可以看到，emp_name_ix建立的索引列（last_name
3，通过排序索引获得数据，以保存单个排序操作。
复制代码代码如下所示：
SQL SELECT * FROM的employee_id秩序>> hr.employees；
执行计划
----------------------------------------------------------
计划哈希值：2186312383
---------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
---------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 107 7383 3（0）| 00:00:01 |
| 1表访问的索引rowid |员工| | | | 107 7383 3（0）| 00:00:01 |
2索引全扫描| | | emp_emp_id_pk | 107 | | 1（0）| 00:00:01 |
---------------------------------------------------------------------------------------------
-还可以使用升序索引返回下行数据。
SQL >选择employee_id由employee_id倒序hr.employees；
执行计划
----------------------------------------------------------
计划哈希值：753568220
--------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 107 428 1（0）| 00:00:01 |
| 1全索引扫描降| emp_emp_id_pk | | | | 107 428 1（0）| 00:00:01 |
--------------------------------------------------------------------------------------------
在上面的例子中我们可以看到，全索引扫描也可以想扫描范围，通过升降指数收益率数据，并优化不仅仅是这一个，当我们查询列表中的最大值或最小值的列的列的索引时，索引全扫描会很重要优势，因为搜索优化并不是对所有叶节点的索引数据，但只有一根块，第一或最后一片叶子块扫描，这无疑将大大提高性能！！
复制代码代码如下所示：
-索引完全扫描以获得最小值
SQL >选择min（department_id）从hr.employees；
执行计划
----------------------------------------------------------
计划哈希值：613773769
------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 3 1（0）| 00:00:01 |
| 1 |排序总| | 1 | 3 | | |
| 2 |索引全扫描（最小/最大）emp_department_ix | | | | 1 3 1（0）| 00:00:01 |
------------------------------------------------------------------------------------------------
如果同时包含最大和最小的评估，优化器不主动选择有效的索引完全扫描方法。
SQL >选择min（department_id），最大（department_id）从hr.employees；
执行计划
----------------------------------------------------------
计划哈希值：1756381138
--------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 3 3（0）| 00:00:01 |
| 1 |排序总| | 1 | 3 | | |
2表访问全|员工| | | | | 107 321 3（0）| 00:00:01 |
--------------------------------------------------------------------------------
一种可供选择的优化方案
SQL >选择
2（选择min（department_id）来自人力资源。员工）min_id，
3（选择最大（department_id）来自人力资源。员工）max_id
4来自双；
执行计划
----------------------------------------------------------
计划哈希值：2189307159
------------------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
------------------------------------------------------------------------------------------------
| 0 | SELECT语句| | 1 | | 2（0）| 00:00:01 |
| 1 |排序总| | 1 | 3 | | |
| 2 |索引全扫描（最小/最大）emp_department_ix | | | | 1 3 1（0）| 00:00:01 |
| 3 |排序总| | 1 | 3 | | |
| 4 |索引全扫描（最小/最大）emp_department_ix | | | | 1 3 1（0）| 00:00:01 |
| 5 |快速双| | 1 | | 2（0）| 00:00:01 |
------------------------------------------------------------------------------------------------
索引跳跃扫描（索引跳过扫描）
这种扫描是一种特殊的情况，因为在较早的版本，优化器会拒绝使用索引，因为使用非引导柱。跳跃扫描的前提下有一个相应的场景，当非引导上市包含在谓词索引条件，引导小当柱，有可能使用索引跳跃扫描法；同一索引全扫描，扫描范围内，它也可以在升序或降序索引访问；不同的跳跃扫描根据列数将引导的唯一价值逻辑子指数分为更小，更小的子指标导柱只数。分割也较少，更有可能实现相对完整的表扫描，效率很高。
复制代码代码如下所示：
-创建一个测试表，以dba_objects表为例
SQL >创建测试表为SELECT * FROM dba_objects；
表的创建。
-创建一个复合索引，其中选择具有唯一值的单个所有者列作为引导列。
已创建的测试指标i_test（业主，object_id，object_type）；
建立索引。
统计信息分析表的收集
SQL> exec dbms_stats.gather_table_stats（'sys '测试'）；
已成功完成。
-看看引导列的唯一值的比较
从测试中选择计数（*）、计数（不同的所有者）；
计数（*）计数（distinctowner）
这个
七百二十四万八千二百二十九
-访问触发跳跃扫描使用一个条件查询一个非启动柱
SQL >选择*从测试，object_id = 46；
执行计划
----------------------------------------------------------
计划哈希值：1001786056
--------------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 97 31（0）| 00:00:01 |
| 1表访问的索引rowid |测试| | | | 1 97 31（0）| 00:00:01 |
2索引跳跃扫描| * | | i_test | 1 | | 30（0）| 00:00:01 |
--------------------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
2访问（object_id= 46）
滤波器（object_id= 46）
统计
----------------------------------------------------------
101递归调用
0分贝块得到
38一致的获取
0物理读
0重做日志大小
1610个字节通过网络发送给客户机
从客户端接收的523个字节
2 SQL*Net切换到/从客户端
3类（内存）
0类（磁盘）
1行处理
-看看这个声明的全部扫描效率
SQL >选择全（测试） / * * *测试，object_id = 46；
执行计划
----------------------------------------------------------
计划哈希值：1357081020
--------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------
| 0 | SELECT语句| | | | 1 97 282（1）| 00:00:04 |
1表访问全|测试| * | | | | 1 97 282（1）| 00:00:04 |
--------------------------------------------------------------------------
谓词信息（由操作ID标识）：
离开
1过滤器（object_id= 46）
统计
----------------------------------------------------------
1递归调用
0分贝块得到
1037一致的获取
0物理读
0重做日志大小
1607个字节通过网络发送给客户机
从客户端接收的523个字节
2 SQL*Net切换到/从客户端
0类（内存）
0类（磁盘）
1行处理
上面的查询分析可以看出，我们使用索引向导列有29个唯一的值，也就是说当执行索引跳过扫描时，分为29个逻辑子索引查询，只产生38次1037次读逻辑；逻辑相对完整的表扫描读取性能非常明显！
指数快速全扫描（指数快速全扫描）
这种访问方法在数据采集和全表扫描是一样的，多块障碍阅读，所以它不能够使用它来避免订货成本；索引快速全扫描通常发生在查询中的列，索引和一列的索引有一个非空的约束，当然这条件也容易全索引扫描，它可以用在全表扫描，将数据采集不到数据块表。
复制代码代码如下所示：
-仍然使用上面创建的测试表
SQL> DESC测试
名称零式
-----------------------------------------------------------------------------
业主VARCHAR2（30）
object_name VARCHAR2（128）
subobject_name VARCHAR2（30）
object_id非空数
data_object_id数
object_type VARCHAR2（19）
创建日期
last_ddl_time日期
时间戳VARCHAR2（19）
现状VARCHAR2（7）
临时VARCHAR2（1）
生成的VARCHAR2（1）
二VARCHAR2（1）
NAMESPACE NUMBER
edition_name VARCHAR2（30）
-创建一个在object_id列索引
已创建的测试指标pri_inx（object_id）；
建立索引。
-直接全表扫描
SQL >选择object_id测试；
选择72482行。
执行计划
----------------------------------------------------------
计划哈希值：1357081020
--------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------
| 0 | SELECT语句| | | 72482 353k | 282（1）| 00:00:04 |
1表访问全|测试| | | | 72482 353k | 282（1）| 00:00:04 |
--------------------------------------------------------------------------
统计
----------------------------------------------------------
1递归调用
0分贝块得到
5799一致的获取
0物理读
0重做日志大小
1323739个字节通过网络发送给客户机
从客户端接收的53675个字节
4834 SQL*Net切换到/从客户端
0类（内存）
0类（磁盘）
72482行处理
-修改object_id不空
SQL> ALTER TABLE测试修改（object_id不为空）；
表的改变。
与object_id列的查询，你可以看到快速使用全扫描
SQL >选择object_id测试；
选择72482行。
执行计划
----------------------------------------------------------
计划哈希值：3806735285
--------------------------------------------------------------------------------
我的名字|操作| | |行| |字节成本（CPU）时间| |
--------------------------------------------------------------------------------
| 0 | SELECT语句| | | 72482 353k | 45（0）| 00:00:01 |
1索引快速全扫描| | | | | pri_inx 72482 353k | 45（0）| 00:00:01 |
--------------------------------------------------------------------------------
统计
----------------------------------------------------------
167递归调用
0分贝块得到
5020一致的获取
161物理读
0重做日志大小
1323739个字节通过网络发送给客户机
从客户端接收的53675个字节
4834 SQL*Net切换到/从客户端
4类（内存）
0类（磁盘）
72482行处理
ps，这个索引的例子，快速全扫描，真是模拟不好，上面的例子已经很长时间了。