通過案例學調優之--Oracle Cluster Table

About Clusters

A?cluster?provides an optional method of storing table data. A cluster is made up of a group of?tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together. For example, the?emp?and?dept?table share the?deptno?column. When you cluster the?emp?and?dept?tables (see?Figure 18-1), Oracle Database physically stores all rows for each department from both the?emp?and?dept?tables in the same data blocks.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary benefits:

• Disk I/O is reduced and access time improves for joins of clustered tables.

• The?cluster key?is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.

  Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format. For example, in?Figure 18-1, notice how each cluster key (each?deptno) is stored just once for many rows that contain the same value in both the?emp?and?dept?tables.

After creating a cluster, you can create tables in the cluster. However, before any rows can be inserted into the clustered tables, a cluster index must be created. Using clusters does not affect the creation of additional indexes on the clustered tables; they can be created and dropped as usual.

You should not use clusters for tables that are frequently accessed individually.

　索引簇的工作原理：

? ? ? ?聚簇：如果一組表有一些共同的列，則將這樣一組表存儲在相同的數據庫塊中;聚簇還表示把相關的數據存儲在同一個塊上。利用聚簇，一個塊可能包含多個表的數據。概念上就是如果兩個或多個表經常做連接操作，那么可以把需要的數據預先存儲在一起。聚簇還可以用于單個表，可以按某個列將數據分組存儲。

　　更加簡單的說，比如說，EMP表和DEPT表，這兩個表存儲在不同的segment中，甚至有可能存儲在不同的TABLESPACE中，因此，他們的數據一定不會在同一個BLOCK里。而我們有會經常對這兩個表做關聯查詢，比如說：select * from emp,dept where emp.deptno = dept.deptno .仔細想想，查詢主要是對BLOCK的操作，查詢的BLOCK越多，系統IO就消耗越大。如果我把這兩個表的數據聚集在少量的BLOCK里，查詢效率一定會提高不少。

　　比如我現在將值deptno=10的所有員工抽取出來，并且把對應的部門信息也存儲在這個BLOCK里(如果存不下了，可以為原來的塊串聯另外的塊)。這就是索引聚簇表的工作原理。

案例分析：

創建簇的?格式

??? CREATE CLUSTER cluster_name
??? (column date_type [,column datatype]...)
??? [PCTUSED 40 | integer] [PCTFREE 10 | integer]
??? [SIZE integer]
??? [INITRANS 1 | integer] [MAXTRANS 255 | integer]
??? [TABLESPACE tablespace]
??? [STORAGE storage]

????SIZE：指定估計平均簇鍵，以及與其相關的行所需的字節數。

1) 普通表連接查詢

10:06:37?SYS@?test1>conn?scott/tiger Connected.11:47:08?SCOTT@?test1?>select?e.ename,e.sal,e.deptno,d.dname,d.loc?from?emp?e?,dept?d 11:48:21???2???where?e.deptno=d.deptno?and?d.deptno=10;ENAME?????????????SAL?????DEPTNO?DNAME??????????LOC ----------?----------?----------?--------------?------------- CLARK????????????2450?????????10?ACCOUNTING?????NEW?YORK KING?????????????5000?????????10?ACCOUNTING?????NEW?YORK MILLER???????????1300?????????10?ACCOUNTING?????NEW?YORK執行計劃： Execution?Plan ---------------------------------------------------------- Plan?hash?value:?568005898 ---------------------------------------------------------------------------------------- |?Id??|?Operation????????????????????|?Name????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????| ---------------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT?????????????|?????????|?????3?|????99?|?????4???(0)|?00:00:01?| |???1?|??NESTED?LOOPS????????????????|?????????|?????3?|????99?|?????4???(0)|?00:00:01?| |???2?|???TABLE?ACCESS?BY?INDEX?ROWID|?DEPT????|?????1?|????20?|?????1???(0)|?00:00:01?| |*??3?|????INDEX?UNIQUE?SCAN?????????|?PK_DEPT?|?????1?|???????|?????0???(0)|?00:00:01?| |*??4?|???TABLE?ACCESS?FULL??????????|?EMP?????|?????3?|????39?|?????3???(0)|?00:00:01?| ---------------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): ---------------------------------------------------3?-?access("D"."DEPTNO"=10)4?-?filter("E"."DEPTNO"=10) Statistics ----------------------------------------------------------1??recursive?calls0??db?block?gets10??consistent?gets0??physical?reads0??redo?size766??bytes?sent?via?SQL*Net?to?client419??bytes?received?via?SQL*Net?from?client2??SQL*Net?roundtrips?to/from?client0??sorts?(memory)0??sorts?(disk)3??rows?processed查看emp和dept表所在的塊：???????????????????? 17:46:11?SCOTT@?test1?>select?dbms_rowid.ROWID_OBJECT(rowid)?"OBJ",DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)?"BLOCK_ID"?FROM?EMP?WHERE?ROWNUM=1;OBJ???BLOCK_ID ----------?----------17571????????149Elapsed:?00:00:00.01 17:46:40?SCOTT@?test1?>select?dbms_rowid.ROWID_OBJECT(rowid)?"OBJ",DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)?"BLOCK_ID"?FROM?DEPT?WHERE?ROWNUM=1;OBJ???BLOCK_ID ----------?----------17569????????133對于emp和dept表，屬于不同的object，數據存儲在不同的數據塊上。

2）簇表查詢

------建立簇 create?cluster?dept_emp_clu?(deptno?number(3))pctfree?20?pctused?60size?500?tablespace?users;Size選項：是用來告訴Oracle預計有500字節數據和每個cluser?key相關。Oracle將使用這個信息來計算每個block能容納的最大cluster?key數目。因此size太高，在每一block將得到很少的key，并且將使用比需要的更多的空間；設置容量太低，將得到過多的數據連接，這將偏離使用cluster的目的。Size是cluster的重要參數。----?建立簇表create?table?department(id?number(3)?primary?key,dname?varchar(14)?,loc?varchar2(13))cluster?dept_emp_clu(id);create?table?employee(eno?number(4)?primary?key?,ename?varchar2(10),job?varchar2(9),mgr?number(4),hiredate?date,sal?number(7,2),comm?number(7,2),dept_id?number(3)?references?department)?cluster?dept_emp_clu(dept_id);-----在簇上建立索引create?index?dept_emp_idx?on?cluster?dept_emp_clutablespace?indx;11:49:43?SCOTT@?test1?>analyze?table?department?compute?statistics; Table?analyzed.11:50:15?SCOTT@?test1?>analyze?table?employee?compute?statistics; Table?analyzed.11:50:31?SCOTT@?test1?>select?e.ename,e.sal,e.DEPT_ID,d.dname,d.loc?from?employee?e,department?d 11:50:39???2??where?e.dept_id=d.id?and?d.id=10;? ENAME?????????????SAL????DEPT_ID?DNAME??????????LOC ----------?----------?----------?--------------?------------- CLARK????????????2450?????????10?ACCOUNTING?????NEW?YORK KING?????????????5000?????????10?ACCOUNTING?????NEW?YORK MILLER???????????1300?????????10?ACCOUNTING?????NEW?YORK執行計劃： Execution?Plan ---------------------------------------------------------- Plan?hash?value:?2165989181 --------------------------------------------------------------------------------------------- |?Id??|?Operation????????????????????|?Name?????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????| --------------------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT?????????????|??????????????|?????5?|???140?|?????2???(0)|?00:00:01?| |???1?|??NESTED?LOOPS????????????????|??????????????|?????5?|???140?|?????2???(0)|?00:00:01?| |???2?|???TABLE?ACCESS?BY?INDEX?ROWID|?DEPARTMENT???|?????1?|????18?|?????1???(0)|?00:00:01?| |*??3?|????INDEX?UNIQUE?SCAN?????????|?SYS_C005404??|?????1?|???????|?????0???(0)|?00:00:01?| |???4?|???TABLE?ACCESS?CLUSTER???????|?EMPLOYEE?????|?????5?|????50?|?????1???(0)|?00:00:01?| |*??5?|????INDEX?UNIQUE?SCAN?????????|?DEPT_EMP_IDX?|?????1?|???????|?????0???(0)|?00:00:01?| --------------------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): ---------------------------------------------------3?-?access("D"."ID"=10)5?-?access("E"."DEPT_ID"=10) Statistics ----------------------------------------------------------1??recursive?calls0??db?block?gets5??consistent?gets0??physical?reads0??redo?size767??bytes?sent?via?SQL*Net?to?client419??bytes?received?via?SQL*Net?from?client2??SQL*Net?roundtrips?to/from?client0??sorts?(memory)0??sorts?(disk)3??rows?processed對于Cluster?table連接訪問，可以看到“5??consistent?gets“，比普通表”10??consistent?gets“減少了一半的一致性讀。查詢簇表所在的塊： 10:16:53?SYS@?test1>conn?scott/tiger Connected. 10:19:04?SCOTT@?test1>select?rowid,ename?from?employee?where?rownum?=1; ROWID??????????????ENAME ------------------?---------- AAACiCAAEAAAACEAAA?SMITH10:19:05?SCOTT@?test1>select?rowid,dname?from?department?where?rownum=1; ROWID??????????????DNAME ------------------?-------------- AAACiCAAEAAAACEAAA?ACCOUNTING17:49:17?SCOTT@?test1?>select?dbms_rowid.ROWID_OBJECT('AAACiCAAEAAAACEAAA')?"OBJ",DBMS_ROWID.ROWID_BLOCK_NUMBER('AAACiCAAEAAAACEAAA')?"BLOCK_ID"?FROM?DUAL;OBJ???BLOCK_ID ----------?----------10370????????132 ------因為簇表的數據放在相同的塊上，所以在表連接查詢時，減少了I/O

散列聚簇表? ??

在簇表中，Oracle使用存儲在索引中的鍵值來定位表中的行，而在散列聚簇表中，使用了散列函數代替了簇索引，先通過內部函數或者自定義的函數進行散列計算，然后再將計算得到的碼值用于定位表中的行。創建散列簇需要用到HASHKEYS子句。????? 1、創建散列簇????? create?cluster?my_clu_two(empno?number(10)?)???? pctused?70???? pctfree?10???? tablespace?users???? hash?is?empno???? hashkeys?150?;????? 說明：???? *?hash?is?子句指明了進行散列的列，如果列是唯一的標示行，就可以將列指定為散列值???? *?hashkeys?指定和限制散列函數可以產生的唯一的散列值的數量????? 2、創建散列表????? create?table?t2_emp?(?????? empno?number?(?10?),?????? ename?varchar2?(?20?),?????? birth_date?date?,?????? deptno?number?)???? cluster?my_clu_two(empno);????? 注意：???? *?必須設置數值的精度(具體原因不詳)???? *?散列簇不能也不用創建索引???? *?散列簇不能ALTER:size、hashkeys、hash?is參數

案例分析：

? ? ? ?當數據存儲在一個普通表中的時候，這些記錄將以插入到數據庫時的順序物理地保存到分配的塊中。例如，如果有一個用于存儲員工信息的表，那么員工姓名將會按照插入到表的順序存儲在表中。　　

　　如果員工記錄非常多的話，那么數據表的響應速度就會逐漸變慢。你可以通過選擇值相對等分布的一列（如員工的部門編號）并建立一個簇表來提高查詢員工的速度。

　　在簇表中，如果員工屬于同一個部門，那么它們的記錄將物理地存儲在同一系列的塊中。這樣就可以提高查找員工信息的速度，這是因為在檢索某個特定部門的員工時，需要讀取數據庫塊的數量減少了。而在非簇表中查找員工，就可能需要對每個數據庫塊進行訪問。

　　當表中存在大量鍵值的時候，你就會開始發現由于存在許多簇塊而導致的性能問題。避免這個問題的一個方法就是使用一個哈希函數來約束簇塊的數量。哈希函數將會給定一個數值用來限定簇塊數量的預計范圍，但它得到的值是相對等分布的。例如你可以創建一個哈希函數,只比較部門編號的最后兩位。

　　哈希函數中存在的一個問題就是函數值會打亂記錄原本的順序。你可以通過 ORDER BY來解決這個問題；但是，在很多情況下，記錄數量是非常龐大的。在Oracle 10g 中，你可以將一個數據定義為“natural order” ，那么就可以不用經過排序而以你所希望的順序來檢索哈希簇的數據，從而解決了上面的提出問題。

　　例如，假設你有一個信用卡業務的數據庫。你決定以信用卡號作為簇主鍵將有利于數據的存儲分布。但是，由于存在大量的信用卡號，所以可以使用一個哈希函數來約束簇塊的數量。而且你希望在你的大部分報表中數據是按照時間順序排列的，那么在進行每個查詢操作時使用排序哈希簇，而不要使用ORDER BY。

下面給出了相關語句：

10:54:06?SCOTT@?test1?>create?cluster?credit_cluster( card_no?varchar2(16), transdate?date?sort)? hashkeys?10000? hash?is?ora_hash(card_no)? size?256; Cluster?created.10:55:25?SCOTT@?test1?>create?table?credit_orders (card_no?varchar2(16), transdate?date, amount?number)? cluster?credit_cluster(card_no,transdate); Table?created.10:56:10?SCOTT@?test1?>select?*?from?tab; TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- BONUS??????????????????????????TABLE CREDIT_CLUSTER?????????????????CLUSTER CREDIT_ORDERS??????????????????TABLE????????????110:56:15?SCOTT@?test1?>alter?session?set?nls_date_format?=?"YYYYMMDDHH24MISS"; Session?altered.10:56:33?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('4111111111111111','20050131000123',57.99); 1?row?created.10:57:24?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('4111111111111111','20050130071216',16.59); 1?row?created.10:57:33?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('4111111111111111','20050131111111',39.00); 1?row?created.10:57:45?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('4111111111111111','20050130081001',25.16); 1?row?created.11:12:24?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('5111111111111111','20050131000123',57.99); 1?row?created.11:16:16?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values?('5111111111111111','20050130071216',16.59); 1?row?created.11:16:36?SCOTT@?test1?>insert?into?credit_orders?(card_no,transdate,amount)?values('5111111111111111','20050131111111',39.00); 1?row?created.11:16:57?SCOTT@?test1?>commit; Commit?complete.11:17:02?SCOTT@?test1?>select?*?from?credit_orders; CARD_NO??????????TRANSDATE??????????AMOUNT ----------------?--------------?---------- 4111111111111111?20050131000123??????57.99 4111111111111111?20050130071216??????16.59 4111111111111111?20050131111111?????????39 4111111111111111?20050130081001??????25.16 5111111111111111?20050131000123??????57.99 5111111111111111?20050130071216??????16.59 5111111111111111?20050131111111?????????39 7?rows?selected.10:58:14?SCOTT@?test1?>SET?AUTOTRACE?ON 10:59:53?SCOTT@?test1?>select?*?from?credit_orders?where?card_no=4111111111111111; CARD_NO??????????TRANSDATE??????????AMOUNT ----------------?--------------?---------- 4111111111111111?20050131000123??????57.99 4111111111111111?20050130071216??????16.59 4111111111111111?20050131111111?????????39 4111111111111111?20050130081001??????25.16Execution?Plan ---------------------------------------------------------- Plan?hash?value:?1026124641 ----------------------------------------------------------------------------------- |?Id??|?Operation?????????|?Name??????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????| ----------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT??|???????????????|?????1?|????32?|????87???(0)|?00:00:02?| |*??1?|??TABLE?ACCESS?FULL|?CREDIT_ORDERS?|?????1?|????32?|????87???(0)|?00:00:02?| ----------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): ---------------------------------------------------1?-?filter(TO_NUMBER("CARD_NO")=4111111111111111) Note ------?dynamic?sampling?used?for?this?statement?(level=2) Statistics ----------------------------------------------------------27??recursive?calls0??db?block?gets418??consistent?gets0??physical?reads0??redo?size667??bytes?sent?via?SQL*Net?to?client415??bytes?received?via?SQL*Net?from?client2??SQL*Net?roundtrips?to/from?client2??sorts?(memory)0??sorts?(disk)4??rows?processed11:00:32?SCOTT@?test1?>analyze 11:01:10???2??table?credit_orders?compute?statistics; Table?analyzed.11:01:30?SCOTT@?test1?>select?*?from?credit_orders?where?card_no=4111111111111111; CARD_NO??????????TRANSDATE??????????AMOUNT ----------------?--------------?---------- 4111111111111111?20050131000123??????57.99 4111111111111111?20050130071216??????16.59 4111111111111111?20050131111111?????????39 4111111111111111?20050130081001??????25.16 Elapsed:?00:00:00.02 Execution?Plan ---------------------------------------------------------- Plan?hash?value:?1026124641 ----------------------------------------------------------------------------------- |?Id??|?Operation?????????|?Name??????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????| ----------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT??|???????????????|?????3?|????72?|????87???(0)|?00:00:02?| |*??1?|??TABLE?ACCESS?FULL|?CREDIT_ORDERS?|?????3?|????72?|????87???(0)|?00:00:02?| ----------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): ---------------------------------------------------1?-?filter(TO_NUMBER("CARD_NO")=4111111111111111) Statistics ----------------------------------------------------------12??recursive?calls0??db?block?gets352??consistent?gets0??physical?reads0??redo?size667??bytes?sent?via?SQL*Net?to?client415??bytes?received?via?SQL*Net?from?client2??SQL*Net?roundtrips?to/from?client1??sorts?(memory)0??sorts?(disk)4??rows?processed

? ? ? ?我們可以通過創建一個單表的hash cluster，將相同鍵值的數據物理存放在一起，達到提高性能的目的。創建cluster有兩個最重要的參數：hashkeys和size，前者表示cluster中有多少個不同的鍵值，后者表示每個鍵值需要分配的空間。因為hash cluster的空間是預先分配的，這兩個值的正確設置對cluster的性能影響非常大。hashkeys設置過大，會造成空間浪費，而如果設置過小，則會產生大量的hash碰撞，極大影響性能。size也是一樣，設置過大會浪費空間，而設置過小，數據超過預先分配的空間時，會通過鏈接方式存放在溢出段中，影響性能。會導致塊溢出（同一個hash key分布在不同的block中），從而導致IO增大。而這兩個值一旦設置，就無法更改，除非重建cluster。

? ? ? ? hash cluster簡單的說就是通過預先分配空間的方式，將相同key的數據存放在一起，以提高查詢性能的一種手段，所以準確的設置hashkeys和size參數是使用hash cluster的關鍵，使用的前提是key的數量是可以估算的，而且每個key的數據是基本平均的。但是，在實際使用的環境中，數據量的變化往往是不可預知的，這也造成hash cluster的應用場景非常有限。

簇表的應用：

什么時候不應該使用聚簇：

　　1) 如果預料到聚簇中的表會大量修改：必須知道，索引聚簇會對DML的性能產生某種負面影響(特別是INSERT語句)。管理聚簇中的數據需要做更多的工作。

　　2) 如果需要對聚簇中的表執行全表掃描：不只是必須對你的表中的數據執行全面掃描，還必須對(可能的)多個表中的數據進行全面掃描。由于需要掃描更多的數據，所以全表掃描耗時更久。

　　3) 如果你認為需要頻繁地TRUNCATE和加載表：聚簇中的表不能截除。這是顯然的，因為聚簇在一個塊上存儲了多個表，必須刪除聚簇表中的行。

　　因此，如果數據主要用于讀(這并不表示“從來不寫”;聚簇表完全可以修改)，而且要通過索引來讀(可以是聚簇鍵索引，也可以是聚簇表上的其他索引)，另外會頻繁地把這些信息聯結在一起，此時聚簇就很適合。

補充關于需要使用聚簇表的情況：

考慮對經常在連接語句中訪問的表建立聚簇。

如果表只是偶爾被連接或者它們的公共列經常被修改，則不要聚簇表。（修改記錄的聚簇鍵值比在非聚簇的表中修改此值要花費更多的時間，因為Oracle 必須將修改的記錄移植到其他的塊中以維護聚簇）。

?如果經常需要在一個表上進行完全搜索，則不要聚簇這個表（對一個聚簇表進行完全搜索比在非聚簇表上進行完全搜索的時間長，Oracle 可能要讀更多的塊，因為表是被一起存儲的。）

?如果經常從一個父表和相應的子表中查詢記錄，則考慮給1 對多（1:* ）關系創建聚簇表。（子表記錄存儲在與父表記錄相同的數據塊中，因此當檢索它們時可以同時在內存中，因此需要Oracle 完成較少的I/O ）。

?如果經常查詢同一個父表中的多個子記錄，則考慮單獨將子表聚簇。（這樣提高了從相同的父表查詢子表記錄的性能，而且也沒有降低對父表進行完全搜索的性能）。

?如果從所有有相同聚簇鍵值的表查詢的數據超過一個或兩個Oracle 塊，則不要聚簇表。（要訪問在一個聚簇表中的記錄，Oracle 讀取所有包含那個記錄值的全部數據塊，如果記錄占據了多個數據塊，則訪問一個記錄需要讀的次數比一個非聚簇的表中訪問相同的記錄讀的次數要多）。

使用哈希聚簇指南：

?當經常使用有相同列的包含相等條件的查詢子句訪問表時，考慮使用哈希聚簇來存儲表。使用這些列作為聚簇鍵。

?如果可以確定存放具有給定聚簇鍵值的所有記錄所需的空間（包括現在的和將來的），則將此表以哈希聚簇存儲。

?如果空間不夠，并且不能為將要插入的新記錄分配額外的空間，那么不要使用哈希聚簇。

?如果偶爾創建一個新的、很大的哈希聚簇來保存這樣的表是不切實際的，那么不要用哈希聚簇存儲經常增長的表。

?如果經常需要進行全表搜索，并且必須要為表的預期增長中的哈希聚簇分配足夠的空間，則不要將此表以哈希聚簇存儲。（這樣的完全檢索必須要讀分配給哈希聚簇的全部塊，即使有些塊可能只包含很少的記錄。單獨地存儲表將減少由完全的表檢索讀取的塊的數量。）

?如果你的應用程序經常修改聚簇鍵的值，則不要將表以哈希聚簇方式存儲。

?不管這個表是否經常與其他表連接，只要進行哈希對于基于以前的指南的表是合適的，那么在哈希聚簇中存儲一個表可能是有用的。

轉載于:https://blog.51cto.com/tiany/1591472

總結

以上是生活随笔為你收集整理的通过案例学调优之--Oracle Cluster Table的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。