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

你的名字 2022-05-15 06:36 144阅读 0赞

## 一、全表扫描实例演示 ##

--在dba 权限下创建表t1 并建索引
    create table t1 as select trunc((rownum-1)/100) id,rpad(rownum,100) t_pad from dba_source t where rownum<=10000;
    create index t1_idx1 on t1(id);
    
    创建表T2
     create table t2 as select mod(rownum,100) id,rpad(rownum,100) t_pad from dba_source t where rownum<=10000;
    --创建索引
    create index t2_idx1 on t2(id);

统计表信息存储过程 ，只能在sqlplus命令窗口下执行；  
DBMS\_STATS.GATHER\_TABLE\_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析–列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）

exec dbms_stats.gather_table_stats(user,'t1',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade=>True);
    exec dbms_stats.gather_table_stats(user,'t2',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade=>True);
    --在plsql 中执行如下：
    begin 
      dbms_stats.gather_table_stats(user,'t1',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade=>True);
      end;

两个表中的数据是一样的，只是存储顺序不一样。如下图：  
![这里写图片描述][70]

当我们 只取一个id时，我们取的数据为100条，占总数的1/100，对于这么小的百分百来说，我们期望优化器采用索引来进行扫描，如果数据是按顺序存储，如表T1那样，id=1的数据只在几个数据块中，那这个结论是对的，执行计划如下图：

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

但是 对于表t2来说，执行计划选择了全表扫描  
![这里写图片描述][70 2]

为什么数据量相同的T2 却没有进行索引扫描，根本原因就是两个表的数据存储方式不一样，根据  
id查询数据，t1表只需要访问很少的数据块就会获得所需要的全部数据了，但对与T2是零散的存储在该表的  
数据块中，所以按id取获取数据需要访问全部的数据块，优化器计算出可能索引扫描取获取每个查询块中所需要数据的时间  
比全表扫描后舍去不需要的时间还要长，所以选择了全表扫描。

可以通过提示来强制使用索引扫描

select /*+ index(t2 t2_idx1) */ * from t2 where  t2.id='1';
    ---- 强制使用 全表扫描 select /*+ full(t1)*/ * from  t1 where t1.id='1';
    --扩展 强制使用多个索引，使用表别名
    select/*+ INDEX(t IDX_PAGREE_1) INDEX(t5 IDX_PITEM_5) INDEX(t3 IDX_PRODUCTS_3) */ *

![这里写图片描述][70 3]  
从执行计划中可以看到对于T2表强制使用索引扫描，成本为明显比全表扫描高。  
**小结**:索引扫描并不一定比全扫描高效，优化器需要根据成本的高低选择扫描方式。  
二、全表扫描舍弃  
全表扫描 是否为高效的选择取决于需要访问的数据块个数以及最终结果集行数，此外全扫描是否为高效选择的另外一个因素是舍弃。舍弃的行是那些通过一个筛选谓语来进行验证，不满足条件的数据从最终的结果集中剔除数据行。  
对于T2表中，获取id=1的数据意味着必须检查表中所有1w行数据，并舍弃9900行，对于每一行所作的检查仅仅是筛选id=1，每一次检查都会使用cpu，这也就意味着尽管所需要访问的数据库数目是有限的，为了完成每一行的检查筛选仍然需要花费相当多的cpu资源，cpu的使用将会归入到全扫描成本中。  
所访问数据块数目以及舍弃的数量越大，全扫描的成本也就越高。  
通过如下语句获取t2的数据块：

select table_name,num_rows,blocks from user_tables t where t.table_name='T2';
    select table_name,num_rows,blocks from all_tables t where t.table_name='T2';
    --dba 权限
    select table_name,num_rows,blocks from dba_tables t where t.table_name='T2';

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

当然随着时间推移，t2表的数据会越来越大，舍弃这么多数据行的成本也会越来越大，成本大到一定程度后，优化器会切换到索引扫描，如果到达这个点后，优化器没有选择索引扫描，可以通过提示来强制使用索引扫描。

[70]: /images/20220515/41d58ee0098447c1bbf657605f94c19f.png
[70 1]: /images/20220515/7cc7a3e35c7f4a6586b0dc4d1acdf9dc.png
[70 2]: /images/20220515/b49ae1794f254630909bb761b28f9fd3.png
[70 3]: /images/20220515/c51d263df6c946bf83e327c51e4451ab.png
[70 4]: /images/20220515/6e26c71199d647689153c42ae10b70e5.png