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

Myth丶恋晨 2022-05-14 05:18 220阅读 0赞

上篇文章oracle sql 高级编程学习笔记（十三）[https://blog.csdn.net/whandgdh/article/details/82347500][https_blog.csdn.net_whandgdh_article_details_82347500]  
中的索引扫描类型还剩下索引跳跃扫描（index skip scan）以及索引快速全扫描

# 一、索引跳跃扫描（index skip scan） #

该索引扫描方式主要发生在组合索引上，且组合索引的引导列未被指定在检索条件中的情况下发生。  
在组合索引中，无论该索引是否为唯一索引。当引导列未被指定在检索条件的情况下，可能会发生“索引跳跃扫描”；  
实例演示如下：

create table employees_copy as select t.* from employees t;

然后不断的把employees\_copy 数据插入 employees\_copy表中。  
![这里写图片描述][70]

处理 emplpoyee id; 存储过程如下

create or replace procedure update_id_test is cursor get_data is select e.rowid,e.* from employees_copy e;--获取表数据
     row_id  get_data%rowtype;
     id_count number(8) ;--新的id变量
    begin id_count :=1;
      open  get_data;--打开游标
      loop --考试循环
        fetch get_data
          into row_id;
           exit when get_data%notfound;
           update employees_copy e set e.employee_id=id_count --将id_count 设置为employee_id where e.rowid=row_id.rowid;
          id_count:=id_count+1;
           end loop;
          commit;
          close get_data;
    
    end update_id_test;

执行存储过程

begin update_id_test;
      end;

id已经修改完成  
![这里写图片描述][70 1]  
将id <20的first\_name 改为‘William’

update employees_copy t set t.first_name='William' where t.employee_id<20;
    update employees_copy t set t.first_name='Kill' where t.employee_id>20 and t.first_name='William';

修改后  
![这里写图片描述][70 2]  
创建索引  
这里 job\_id 是引![这里写图片描述][70 3]导列 job\_id 的唯一值19个，

create index emp_copy_idx on employees_copy(job_id,first_name);
    --收集测试表最新的统计信息

![这里写图片描述][70 4]  
— sqlplus中 索引创建后 务必要执行这句，不然执行计划还是会选择全表扫描  
exec dbms\_stats.gather\_table\_stats(‘HR’,’EMPLOYEES\_COPY’);

查看 执行计划 优化器选择了索引跳跃查询， 有331多个逻辑读  
![这里写图片描述][70 5]  
强制使用全表扫描

select /*+ full(employees\_copy)*/ \* from employees\_copy where first\_name=’William’;  
执行计划如下，强制使用全表扫描，逻辑读取也是331个，但是成本比索引跳跃扫描多  
![这里写图片描述][70 6]  
再来查看first\_name =’Kill’ 有511条记录，而且id不连续  
![这里写图片描述][70 7]  
![这里写图片描述][70 8]  
再来看执行计划 优化器没有选择索引跳跃扫描，而是选择了全表扫描 逻辑读取364 ，成本103  
![这里写图片描述][70 9]  
强制使用索引扫描 可知 强制跳跃扫描后，成本是 442，逻辑读取592 效率当然没有全表扫描高，  
当引导列的值很少时，优化器选择是否走索引跳跃扫描还需要由非引导列数据存储方式决定，不能就说索引跳跃扫描效率就比  
全表扫描效率高，得视具体情况而定

select /*+ index(t emp_copy_idx)*/*from  employees_copy  t where t.first_name='Kill';

![这里写图片描述][70 10]  
Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。  
二、索引快速全扫描  
索引快速全扫描 更像全表扫描而不像其他任何类型的索引扫描。当选用索引快速全扫描时  
所有索引块都将通过多块读取来进行读取。这种类型的索引扫描时用来查询列表中所有字段都包含在索引中并且  
索引中至少有一列具有非空。索引快速全扫描不能用来避免排序，因为数据块时通过无序的多表读取来读取的。

对于索引快速扫描，只适用于CBO。

对于索引快速扫描，使用多块读取的方式，读取索引块。（这种方式相较于索引全扫描，获取数据的效率更高）

对于索引快速扫描，从结果集看，结果全部源于索引块，但数据结果不一定有序。

实例演示 employees 中 索引如下  
![这里写图片描述][70 11]  
将modify 修改为可以为null

alter table employees modify(email null);

查看执行计划: 选择了全表扫描  
![这里写图片描述][70 12]  
再把字段email改为不能空

alter table employees modify(email not null);

再看执行计划走的是全索引扫描  
![这里写图片描述][70 13]  
例子说明索引快速全扫描运算为了被选择是依赖非空约束的，如果没有约束将会选择全表扫描运算。

[https_blog.csdn.net_whandgdh_article_details_82347500]: https://blog.csdn.net/whandgdh/article/details/82347500
[70]: /images/20220514/9ecbab47818d41ac88a77755cb8174e9.png
[70 1]: /images/20220514/198c19d1fe1e473d80a3c703147a25a9.png
[70 2]: /images/20220514/0a4c23cdb0db4372acad3ff5e266f8f8.png
[70 3]: /images/20220514/2043fad0beba4441a62b3af0b5878f4e.png
[70 4]: /images/20220514/f3877a443108409b95d9169e254630ba.png
[70 5]: /images/20220514/9f95b62eaf3b4535bd3d3ee97bee0fa4.png
[70 6]: /images/20220514/707724bb68424d46aa221e681aafa291.png
[70 7]: /images/20220514/758b10bd803c43d48eeabac19e018f7d.png
[70 8]: /images/20220514/07841d79e2eb46dba823a9ed84b50314.png
[70 9]: /images/20220514/1dba166fb0814784a0adcc3089e6732e.png
[70 10]: /images/20220514/987e77bee2954fbea5c914356b24e5a6.png
[70 11]: /images/20220514/1e853dbee1f1475888c73e0e293f748b.png
[70 12]: /images/20220514/d0d92e8c88824f4d8cee3d80b4e0f32f.png
[70 13]: /images/20220514/b4456640a57b4c7fa020bd8b3e96d919.png