Oracle索引梳理系列（九）- 淺談聚簇因數對索引使用的影響及優化方法

来源:http://www.cnblogs.com/yumiko/archive/2016/11/07/6036795.html
-Advertisement-
Play Games

版權聲明：本文發佈於http://www.cnblogs.com/yumiko/，版權由Yumiko_sunny所有，歡迎轉載。轉載時，請在文章明顯位置註明原文鏈接。若在未經作者同意的情況下，將本文內容用於商業用途，將保留追究其法律責任的權利。如果有問題，請以郵箱方式聯繫作者（793113046@q ...


版權聲明：本文發佈於http://www.cnblogs.com/yumiko/，版權由Yumiko_sunny所有，歡迎轉載。轉載時，請在文章明顯位置註明原文鏈接。若在未經作者同意的情況下，將本文內容用於商業用途，將保留追究其法律責任的權利。如果有問題，請以郵箱方式聯繫作者（[email protected]）。

 1、聚簇因數的概念

  • 聚簇因數，是CBO優化器決定是否使用索引的因素之一，主要反映索引塊上的數據（順序存儲）,與該索引基於的表塊上的數據（無序存儲）的順序相似程度的差異性。即表數據的存儲順序是否與相應索引數據的存儲順序一致。
  • 通過查詢dba_indexes視圖、user_indexes視圖以及all_indexes視圖的CLUSTERING_FACTOR列，可以瞭解當前索引的聚簇因數值。

 

 

2、索引塊與相應數據塊之間數據分佈產生差異的原因

  • 對於索引塊的數據存儲，這裡以普通btree索引為例，索引塊中鍵值的分佈總是有序的，且根據鍵值及其相應的rowid信息，唯一定位一行記錄在相應表的數據塊中的分佈。理想情況下，相同或相鄰的鍵值，儘量定位在相同的數據塊上，可以避免對於數據塊多餘的I/O操作。

  • 對於數據塊的數據存儲，並不是有序存儲的。且ORACLE為節省空間，會優先使用當前當水位線（HWM）以下的可用數據塊，而不是按序使用最後被使用的塊。當HWM以下無可用數據塊時，再開闢新的數據塊使用。
  • 正因為數據塊中數據存儲的特點，隨著時間的推移，數據在相應數據塊間的分佈越發零散，進而影響索引塊中，相同或相鄰鍵值對應的相應數據行信息（rowid），所指向的數據塊越加分散，進而導致聚簇因數變差。

 

 

3、聚簇因數的計算方法

聚簇因數大致的計算方法順序如下：

  1. 進行一次索引全掃描
  2. 檢查索引塊中的rowid信息。比較前一個rowid與一個rowid是否指向同一個數據塊。若不同，則聚簇因數加1.
  3. 當完成整個的索引掃面後，即得到該索引的聚簇因數的數值。

 

 

4、聚簇因數好壞的判斷 

良好的CF值，會趨向於數據表的塊數。

較差的CF值，會趨向於數據表的行數。

需要註意的是：隨著時間的推移，頻繁的DML操作，會讓CF值總是趨向於惡劣方向發展。

 

示例：

本示例主要說明CF的趨勢性。

--查看當前測試表中索引的聚簇因數情況
--註意此時的LAST_ANALYZED為空，說明未收集過統計信息
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- -------------------
TEST_IDX        TEST                        17381       18432    1217342                    



--分析收集表test最新的統計信息
Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.



--查看收集後最新的信息，可以看到，結果集中CF值，明顯小於數據塊值，說明此時情況相似度很好。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                        17381       18432    1217342 2016-11-06 16:38:08



--插入新的數據併進行提交
Yumiko_sunny@OA01> insert into test  select * from test;
1217342 rows created.

Yumiko_sunny@OA01> commit;
Commit complete.



--再次收集表test相關的統計信息
Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.



--不難發現，隨著insert的操作，CF值發生了改變，雖然目前該值在可接受範圍內，但已經開始趨向行數。
--可以想象下，一個生產環境中，除了insert，還有update跟delete，隨著這些操作的增多，勢必更加趨向行數。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                       206123       35840    2434684 2016-11-06 16:39:58


 

 

5、聚簇因數的優化

由於影響CF（CLUSTERING_FACTOR）值的主要取決於數據表的數據，在數據塊中的存儲分佈情況，因此優化CF的重點還是在調整數據表本身，具體方法如下：

  • 定期按索引列順序重建表
    • 建議通過dbms_metadata.get_ddl提取表結構完整的DDL語句，結合insert order by column以及rename table的方式進行表的重建。
    • 不建議採用CTAS方式（create table as select），該方式可能引起後續不必要的麻煩。具體影響可參閱鏈接中的案例 http://blog.csdn.net/leshami/article/details/7362156
  • 使用聚簇表代替普通的數據表
    • 頻繁DML的表以及經常需要全表掃描的表，不適合建立聚簇表。
    • 具體查閱作者前面關於“表簇索引”一文的介紹 “Oracle索引種類之表簇索引（cluster index）”

 

示例：

本示例承接上面的示例，主要演示通過重建表的方式進行聚簇因數優化的過程。

--再次確認原始表test的CF值
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                       206123       35840    2434684 2016-11-06 22:10:51 




--通過調用dbms_metadata包的get_ddl函數，抽取原始表test的DDL結構語句
Yumiko_sunny@OA01> set long 100000
Yumiko_sunny@OA01> set pages 0
Yumiko_sunny@OA01> select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dual;
Enter value for table_name: TEST
Enter value for owner: SCOTT
old   1: select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dual
new   1: select dbms_metadata.get_ddl('TABLE',upper('TEST'),upper('SCOTT')) from dual

  CREATE TABLE "SCOTT"."TEST"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"




--利用抽取的原始表test的結構語句，創建新表test_tmp
Yumiko_sunny@OA01> CREATE TABLE "SCOTT"."TEST_TMP"
  2     (    "OWNER" VARCHAR2(30),
  3          "OBJECT_NAME" VARCHAR2(128),
  4          "SUBOBJECT_NAME" VARCHAR2(30),
  5          "OBJECT_ID" NUMBER,
  6          "DATA_OBJECT_ID" NUMBER,
  7          "OBJECT_TYPE" VARCHAR2(19),
  8          "CREATED" DATE,
  9          "LAST_DDL_TIME" DATE,
 10          "TIMESTAMP" VARCHAR2(19),
 11          "STATUS" VARCHAR2(7),
 12          "TEMPORARY" VARCHAR2(1),
 13          "GENERATED" VARCHAR2(1),
 14          "SECONDARY" VARCHAR2(1),
 15          "NAMESPACE" NUMBER,
 16          "EDITION_NAME" VARCHAR2(30)
 17     ) SEGMENT CREATION IMMEDIATE
 18    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 19   NOCOMPRESS LOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 23    TABLESPACE "USERS";

Table created.



--通過對原始表test的索引列進行order by排序操作後，差入到新表test_tmp中
--通過append hint的方法，雖然可以減少redo的產生，並且在hwm以上開闢數據塊，加快了數據的載入速度。
--但該方式，在commit或者rollback事物前，其他會話無法針對該表進行DML操作，生產環境中需要註意。
Yumiko_sunny@OA01> insert into /*+append */ test_tmp select * from test order by object_id;
2434684 rows created.

Yumiko_sunny@OA01> commit;



--為新表test_tmp的索引列添加索引
Yumiko_sunny@OA01> create index test_idx_new on test_tmp(object_id);
Index created.


--將原始表test進行重命名test_old
Yumiko_sunny@OA01> alter table test rename to test_old;
Table altered.


--將新表test_tmp重命名為test
Yumiko_sunny@OA01> alter table test_tmp rename to test;
Table altered.



--分析收集新表test的統計信息
Yumiko_sunny@OA01> analyze table TEST compute statistics;
Table analyzed.



--查看新建的表test的CF，不難發現，此時的CF值將較之前已經明顯下降。
--至此，CF的優化過程結束。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME    TABLE_NAME    CLUSTERING_FACTOR   TB_BLOCKS   TB_ROWS  LAST_ANALYZED       
----------------------------------------------------------------------------------------
TEST_IDX_NEW        TEST                39700       35840   2434684  2016-11-06 22:26:10

 

需要補充說明的是：
對於alter table move的操作，可以降低高水位線，但對於優化聚簇因數值而言，意義不大。
對於重建索引，通過實驗發現（只進行了兩個實驗，可能結果集存在誤差），聚簇因數值不但未降低，有時還存在些許的增加，需要註意。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • [轉]一些NSArray,NSDictionary,NSSet相關的演算法知識
    iOS編程當中的幾個集合類：NSArray，NSDictionary，NSSet以及對應的Mutable版本，應該所有人都用過。只是簡單使用的話，相信沒人會用錯，但要做到高效（時間複雜度）精確（業務準確性），還需要瞭解其中所隱藏的演算法知識。 在項目當中使用集合類幾乎是不可避免的，集合類的使用場景其實 ...
  • 矽谷商城2--自定義數字加減控制項
    1_自定義數字加減控制項的要求 創建Module -NumberAddSubView A_輸入的只能是數字，而且不能通過鍵盤輸入 B_通過加減按鈕操作數字 C_監聽加減按鈕 D_數組有最小值和最大值的限制 E_自定義屬性 2_提供介面，讓外界監聽到數字的變化 1_設置介面 2_監聽變化 3_自定義屬性 ...
  • React Native 之 組件化開發
    前言 學習本系列內容需要具備一定 HTML 開發基礎，沒有基礎的朋友可以先轉至 "HTML快速入門(一)" 學習 本人接觸 React Native 時間並不是特別長，所以對其中的內容和性質瞭解可能會有所偏差，在學習中如果有錯會及時修改內容，也歡迎萬能的朋友們批評指出，謝謝 文章第一版出自簡書，如果 ...
  • 矽谷商城1--商城熱賣
    1.商品展示頁面的請求地址 http://112.124.22.238:8081/course_api/wares/hot?pageSize=10&curPage=1 下一頁 http://112.124.22.238:8081/course_api/wares/hot?pageSize=10&cu ...
  • iOS多線程之7.NSOperation的初識
    NSOperation和GCD一樣，不用我們管理線程的生命周期，加鎖等問題，只要把操作封裝進NSOperation中，系統會自動幫我們創建線程，執行操作。而且他是面向對象的，我們看起來更容易理解，使用起來也更靈活。GCD提供的API都是C語言的，看起來確實有點頭痛。 NSOperation是一個抽象 ...
  • plist文件的使用
    什麼是plist文件 直接將數據寫在代碼裡面，不是一種合理的做法。如果經常改，就要經常翻開對應的代碼進行修改，造成代碼擴展性低 因此，可以考慮將經常變的數據放在文件中進行存儲，程式啟動後從文件中讀取最新的數據。如果要變動數據，直接修改數據文件即可，不用修改代碼 一般可以使用屬性列表文件存儲 或者 之 ...
  • mysql-5.6.14-win32為免安裝解壓縮版
    mysql-5.6.14-win32為免安裝解壓縮版，安裝版（http://dev.mysql.com/downloads/installer/5.5.html#downloads）存在很多弊端。mysql 5.6.14 win7 32位免安裝版配置1.下載mysql 5.6.14；下載地址:htt ...
  • hbase偽分散式平臺搭建（centos 6.3）
    搭建完《hadoop偽分散式平臺》後就開始搭建hbase偽分散式平臺了。有了hadoop環境，搭建hbase就變得很容易了。 一、Hbase安裝 1、從官網下載最新版本Hbase安裝包1.2.3，為了省去編譯安裝環節，我直接下載了hbase-1.2.3-bin.tar.gz，解壓即可使用。（如果此鏈 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類