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

谁借莪１个温暖的怀抱￠ 2022-05-14 01:58 186阅读 0赞

## 一、概念 ##

聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一，决定了当前的SQL语句是否走索引，还是全表扫描以及是否作为嵌套连接外部表等。如此这般，那到底什么是聚簇因子，那些情况下会影响到聚簇因子，以及如何提高聚簇因子？聚簇因子是基于表上索引列上的一个值，每一个索引都有一个聚簇因子。用于描述索引块上与表块上存储数据在顺序上的相似程度，也就说表上的数据行的存储顺序与索引列上顺序是否一致。即向优化器表面具有同样索引值的数据行是不是存放在同一个或者连续的一系列数据块中，或者数据行是否分散存放在表的多个数据块中。

## 二、实例演示 ##

继续参考学习笔记7 [https://blog.csdn.net/whandgdh/article/details/82077889][https_blog.csdn.net_whandgdh_article_details_82077889]中全表扫描和索引扫描例子，建立在T1表中ID这一列的上的索引将会具有较低的聚簇因子，用一个接近于表中数据块数量的较低数值来表明该表中基于索引后  
的数据行排序度或聚簇度是很高的。而表T2的聚簇因子将会更高并更接近于表中的数据行数。

select t.table_name || '.'||i.index_name idx_name, i.clustering_factor, t.blocks, t.num_rows from user_indexes i, user_tables t WHERE I.table_name=T.table_name AND t.table_name in('T1','T2');

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

所以正如前面例子所展示的，优化器对于T1表 会选择索引扫描，而T2会选择全表扫描最后好情况是clustering\_factor 聚簇因子与块数目相等，而最坏情况就是聚簇因子与数据行数相等。

## 三、聚簇因子与 索引关系展示 ##

1、良好的索引与聚簇因子的情形  
![这里写图片描述][70 1]  
2、良好的索引与差的聚簇因子  
![这里写图片描述][70 2]  
3、差的索引和差的聚簇因子  
![这里写图片描述][70 3]  
影响聚簇因子  
当插入到表的数据与索引的顺序相同时，可以提高聚簇因子(接近表上的块数)。 因此，任意影响该顺序的情形都将导致索引列上的聚簇因子变差。 如列的顺序，反向索引，空闲列表或空闲列表组。

## 四、聚簇因子的计算 ##

select sum (cluf_ct) clustering_factor from (select id, blk_no, lag(blk_no, 1, blk_no) over(order by id) prev_blk_no, case when blk_no != lag(blk_no, 1, blk_no) --当对索引列id排序后连续数据行的数据块编号不一致 over(order by id) or rownum = 1 then 1 else 0 end cluf_ct from (select t1.id, dbms_rowid.rowid_block_number(rowid) blk_no from t1 where t1.id is not null order by t1.id));

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

计算依据就是当数据按照索引列排序后，连续的数据行数据块编号不一致时，聚簇因子加+1，当然这并不是准确计算聚簇因子的方法，但是这个查询可以帮助了解计算的概况。

## 五、提高聚簇因子 ##

堆表的数据存储是无序存储，因此需要使无序变为有序。下面是提高聚簇因子的办法。  
a、对于表上的多个索引以及组合索引的情形，索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引。  
b、定期重构表(针对堆表)，也就是使得表与索引上的数据顺序更接近。注意，是重构表，而不是重建索引。(聚簇因子是与表数据相关而不是与索引相关)  
注意：但是当考虑到通过重建表来改进聚簇因子时，需要非常的小心，表一般都有多个索引。你不可能通过将数据排序使数据适应一个索引而不影响其他列上的索引。并且重建表通常是非常的消耗时间和资源的过程，随着时间推移，数据行不断的插入，删除，更新之后仍然还需要重构表。  
如何更好的改进聚簇因子将在后面的学习中得到解答。

重建索引并不能显剧提高CF的值，因为索引列通常是有序的，无序的是原始表上的数据。 提取原始表上的数据到一个临时表，禁用依赖于该表的相关约束，truncate原始表，再将临时表的数据按索引访问顺序填充到原始表。

c、使用聚簇表来代替堆表。  
文章来自:[https://blog.csdn.net/leshami/article/details/8847959][https_blog.csdn.net_leshami_article_details_8847959]**重点内容**

[https_blog.csdn.net_whandgdh_article_details_82077889]: https://blog.csdn.net/whandgdh/article/details/82077889
[70]: /images/20220514/dc994df95c804062a93124f70de39c80.png
[70 1]: /images/20220514/927b1e1eb3814da88c6057416853fc1e.png
[70 2]: /images/20220514/3ebedb9ea37444d9a94e8438f3b8fa6a.png
[70 3]: /images/20220514/8c33c9b52c3747d4af8a98e54ef5dec8.png
[70 4]: /images/20220514/a3a3a178a76b48a9b0c303bae0f35b48.png
[https_blog.csdn.net_leshami_article_details_8847959]: https://blog.csdn.net/leshami/article/details/8847959