oracle临时表经常被锁_实验验证：Oracle聚簇因子对索引使用的影响

最新推荐文章于 2023-06-14 15:59:20 发布

weixin_39540271

最新推荐文章于 2023-06-14 15:59:20 发布

阅读量84

点赞数

文章标签： oracle临时表经常被锁 oracle查询出来的顺序不一致 Oracle账号

【引言】
本文基于如下文章进行的实验，讲述聚簇因子对SQL索引的影响。

链接如下：
https://www.modb.pro/db/40283

一张表上有索引，sql在执行的时候却不走索引的原因有很多，一张表上的聚簇因子是影响CBO判断索引是否能被使用的一个重要判断依据。

索引范围扫描的成本计算公式如下：
cost = {(blevel+leaf_blocks * effective index selectivity)*(1-optimizer_index_caching/100)+(cluster_factor * effective table selectivity)*(optimizer_index_cost_adj/100)

这两个参数在oracle 10gR2之后建议保持默认值，从上面成本计算公式可看出这两个参数是如何影响成本计算的；还有一个参数在一定程度上也能决定索引的优先程度：db_file_multiblock_read_count。

先来了解下什么是聚簇因子。

聚簇因子(clustering factor)和索引高度(blevel)、叶块数(leaf_blocks)等统计信息值一样用于计算cost的值，以决定当前sql语句是走索引还是走全表扫描。

堆表中数据的存储方式为无序存储，
也就是任意的DML操作都可能使得当前数据块存在可用的空闲空间，出于节省空间的考虑，块上的可用空闲空间会被新插入的行填充，而不是按顺序填充到最后被使用的块上。

以上的操作也就导致了数据块中数据的无序性，而创建索引的时候，会将指定的列按顺序插入索引块中。正是因为表上的数据是无序的，索引上的数据是有序的，这种差异性就可以用聚簇因子来表示。

接下来看看索引聚簇因子(clustering factor)对于索引的使用是如何影响的，通过实验演示一下clustering factor对索引选择的影响。

测试数据准备

create tablespace tbs0831 datafile '/u01/app/oracle/oradata/ces/tbs0831.dbf' size 10M autoextend off;

create user test0831 identified by test0831 default tablespace tbs0831;

grant dba to test0831;

conn test0831/test0831

create table tb0831 (id1 number ,id2 number);

begin
     for i in 1..100
          loop
              for j in 1..100
                     loop
                     insert into tb0831 values(i,j);
                     end loop;
          end loop;
end;
/

create index idx_1 on tb0831(id1);
create index idx_2 on tb0831(id2);

==--搜集统计信息，注意加cascade=>true，表示对索引也搜集统计信息==
exec dbms_stats.gather_table_stats(user,'TB0831',cascade=>true);

从上面插入数据的脚本可看出，id1的值存储的更加紧凑，id2的值存储的更加松散。

SQL> select INDEX_NAME,NUM_ROWS,DISTINCT_KEYS,CLUSTERING_FACTOR,BLEVEL,LEAF_BLOCKS from dba_indexes where table_name='TB0831';

INDEX_NAME                                                                                   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS
------------------------------------------------------------------------------------------ ---------- ------------- ----------------- ---------- -----------
IDX_1                                                                                           10000           100                22          1          20
IDX_2                                                                                           10000           100              1600          1          20

先执行如下sql：

explain plan for select * from tb0831 where id1=50;
select * from table(dbms_xplan.display);

将id1换成id2再执行下面的sql:

explain plan for select * from tb0831 where id2=50;

select * from table(dbms_xplan.display);

正是索引IDX_2的聚簇因子为1600，太大了，导致cbo在评估执行计划的时候，计算出全表扫描的cost要小于走索引idx_t02的cost。所以在有索引的情况下，cbo选择了走全表扫描。

如在一些场景下，ORACLE可能不会自动走索引，如果对业务清晰，可以尝试使用强制索引，测试查询语句的性能。
针对如下SQL进行index绑定：
select * from tb0831 where id2=50;

重写sql:

SQL> explain plan for select /*+index(TB0831,idx_2)*/ * from tb0831 where id2=50;

Explained.

SQL> select * from table(dbms_xplan.display);

上述案例，可看出聚簇因子值的大小对于是否走索引产生的影响。

如有一个索引他的聚簇因子很大，该如何优化呢？

重建索引并不能改变聚簇因子的大小，因索引是有序的，而导致聚簇因子值大的根本原因在于表上存储的数据太无序，以至于和索引中存储的顺序相差甚远。一个可行方案为定期表重构，将数据先临时存储在中间表中，truncate原始表，然后按照索引存储的顺序填充数据到原始表中。
尽可能的避免一个具有槽糕clustering factor值的索引，比如索引在创建的时候，应考虑按照经常频繁读取的大范围数据的读取顺序来创建索引。

文章至此。

欢迎关注个人微信公众号“一森咖记”

近期热文

你可能也会对以下话题感兴趣。点击链接便可查看。

为什么不建议把数据库部署在docker容器内？
oracle异常恢复(一)dul和bbed的使用
Oracle Cloud云端账号的注册过程
MySQL基本知识点梳理和查询优化
区块链上的数据库：CovenantSQL
MySQL数据延迟跳动的问题分析
MySQL 5.6和 5.7_同步账号修改密码方式：真的不一样
MySQL8.0 为嘛弃用Query Cache?
你应该知道的分布式系统之奠基石CAP理论
MySQL数据延迟跳动的问题分析
如何判断一个应用系统性能好不好？
MySQL Document Store 混合使用关系型数据与非关系型数据
分布式一致性算法：Paxos算法学习
MySQL 中你不得不知的重要知识点
神技_如何快捷下载Oracle补丁的方法？！
趋势篇:oracle 11g,12c,18c,19c之support lifetime
Centos能不能替换RHEL？
Centos能不能替换RHEL？
年末总结_聊一聊数据库行业的“继往开来”
【干货篇】在国内外数据库百家争鸣的时代，DBA们该何去何从？
实操：12C RAC环境下的ADG同步库搭建
浅谈MySQL三种锁：全局锁、表锁和行锁