oracle sql 高级编程学习笔记（十三）

朴灿烈づ我的快乐病毒、 2022-05-14 05:17 216阅读 0赞

Oracle提供了五种索引扫描类型，根据具体索引类型、数据分布、约束条件以及where限制的不同进行选择：  
索引唯一扫描(index unique scan)  
索引范围扫描(index range scan)  
索引跳跃扫描(index skip scan)  
索引全扫描(index full scan)  
索引快速扫描(index fast full scan)

# 一、索引唯一扫描 #

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

1、对于单一列建立的索引（单一索引），当索引属于唯一索引，在检索条件中，使用该索引进行检索，且检索值不是null时，会使用“索引唯一扫描”  
2、对于单一列建立的索引（单一索引），当索引属于唯一索引，在检索条件中，使用该索引进行检索，且检索值等于null时，会使用“全表扫描”  
3、对于多个列建立的索引（组合索引），当索引属于唯一索引，且检索条件中，使用该组合索引进行检索，且检索列使用组合索引涉及的所有列时，会使用“索引唯一扫描”  
**实例演示**：employees中的索引如下  
![这里写图片描述][70]

![这里写图片描述][70 1]

# 二、索引范围扫描 #

当谓语中包含将会返回一定范围数据的条件时就会选用索引范围扫描。索引可以是唯一或者不唯一的，因为由该条件来确定是否返回多个  
数据行。  
使用一个索引存取多行数据，或者创建索引时没有提字为unique索引,即使返一行记录也走范围扫描.  
使用index rang scan的3种情况：  
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。  
(b) 在组合索引上(unique index)，只使用部分列进行查询，导致查询出多行。  
(c) 对非唯一索引列上进行的任何查询。  
employees 中的索引如下：  
![这里写图片描述][70 2]  
**实例演示:**

## 1、对唯一索引使用range操作符 ##

可以看到执行计划中 同样选择了索引范围扫描  
![这里写图片描述][70 3]

## 2、 对非唯一索引查询： ##

![这里写图片描述][70 4]  
索引范围扫描将会从根数据块开始到第一个包含符合特定条件的条目所在的叶子数据块来遍历索引结构。再从那一点开始，从 索引条目中取出一个行编号然后取出相应的表数据块（通过索引编号访问数据表）。在第一行被取出来之后，之前的叶子索引块将再一次访问并读取下一个索引条目获取下一个行编号。使用索引范围扫描的一个精妙之处在于它能够使用一个升序排列的索引（默认值）来会返回降序排列的数据行。如果order by 子句要求按降序排列呢？因为索引按升序来排列  
，索引能够满足降序排列的要求吗？例子如下：  
3、

select * from employees where department_id in(90,100) order by department_id desc;

![这里写图片描述][70 5]  
在这个例子中索引是被以相反的顺序读取，避免了再做单独的排序运算。  
当我们对不在索引列上的列进行排序时，可知执行计划还需要单独做排序，例子如下：

select * from employees t where department_id in(90,100) order by t.last_name desc ;

![这里写图片描述][70 6]

# 三 索引全扫描(index full scan ) #

与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，  
需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。主要包括以下情况：  
1、当没有谓语但是所需要获取列的列表可以通过其中一列的索引来获得，  
2、谓语中包含一个位于索引中非引导列上的条件(组合索引中第一列除外的列)  
3、数据可以通过排过序的索引来获取并且会省去单独的排序步骤。  
具体例子如下：

## 1中情况如下 ##

需要获取列 email 是索引列，没有谓语，所以走索引全扫描。

select email from employees;

![这里写图片描述][70 7]

## 2 、情况如下： ##

first\_name 是 索引emp\_name\_ix 的非引导列，也走索引全扫描

select first_name,last_name from employees where first_name like 'A%';

![这里写图片描述][70 8]

## 3、情况如下： ##

select * from employees order by employee_id;

![这里写图片描述][70 9]

索引全扫描运算将会扫描索引结构中的每一个叶子块，读取每个条目的行编号，并取出数据行。每个叶子块都要被访问。这通常比全表扫描效率要高，因为每一个索引块要比表数据块包含更多的条目，从而总的需要访问块的数也就相对较少。在查询字段列表中所有字段都是索引条目的一部分的时候，就能够完全跳过访问表的步骤。这就意味着选用索引全扫描比读取所有的表数据块效率高。索引扫描还有一个情况，当查询某一列的最小值或最大值而这一列又是索引列时。  
实例演示如下

select min(department_id) from employees;

![这里写图片描述][70 10]  
![这里写图片描述][70 11]

当需要进行min或max聚合运算的时候，优化器能够选择一种特别优化的索引全扫描运算版本，在这样特别的情况下，当使用快速获取最小值时，这个最小值将会是  
第一个索引叶子块的第一个条目，当获取最大值时，将会是最后一个索引叶子块的最后一个条目。所有这种特别的例子种的索引全扫描并不是真正的全扫描–  
仅仅 是对根块、一个或多个分支块以及第一个和最后一个叶子块的扫描。

但对于第二例子可以看到，当同时取最小值和最大值的时候，执行计划却选择了全表扫描。这是优化器处理这种情况的一个不足之处，  
所以对于这种情况。就是把两个查询分开来写。

[70]: /images/20220514/0df63f2ed5d14587809ad2058dd0223e.png
[70 1]: /images/20220514/cc9f714379a44ead84ac6c10e98c9f1e.png
[70 2]: /images/20220514/cb86fa7a11a244568ba9cc3052e753fd.png
[70 3]: /images/20220514/c2d4c87df0884992b3b45b442bf2e5f5.png
[70 4]: /images/20220514/e5068465d94744888432ed763143be95.png
[70 5]: /images/20220514/80dd30fd24ba426daa4306ff25def2bd.png
[70 6]: /images/20220514/6c76f6a31cf544d8a2572ff9687237fe.png
[70 7]: /images/20220514/27ef0460493e447799cde0e375e25679.png
[70 8]: /images/20220514/5d0d386c14504b0b9e878ed69ad0ef10.png
[70 9]: /images/20220514/40859f22e6f344e8b894b84eea0518b4.png
[70 10]: /images/20220514/fd0ccd77f1de46af98eb3da58523c1a5.png
[70 11]: /images/20220514/0a08ddf1792e4e8694578f2c62aa57ec.png