Oracle 提供了很多優化DB的方式，像 compress 的功能 ，上次有解說 compress table ，這次我們來研究一下 compress index
1.首先建立一個測試的table
SQL> create table test_table1 as select * from dba_objects;
Table created.
SQL> insert into test_table1 select * from test_table1;
95850 rows created.
SQL> /
191700 rows created.
SQL> /
383400 rows created.
SQL> /
766800 rows created.
SQL> /
1533600 rows created.
SQL> /
3067200 rows created.
SQL> select count(*) from test_table1;
COUNT(*)
----------
6134400
約6百萬筆數
執行計畫( 因排版關係 則用截圖方式呈現
因為沒有 index 則 table full access ，consistent gets 87635
2.建立一般的index
SQL> create index t_index on test_table1(object_type);
Index created.
SQL> set autot on
index 這次有使用到了，consisten gets 從87635 降到 1795 ，優化效果很顯著
查詢一下index size.
SQL> SELECT segment_name, header_block, bytes
2 FROM dba_segments
3 WHERE segment_name = 'T_INDEX';
SEGMENT_NAME HEADER_BLOCK BYTES
----------------------------- ------------------ -------------
T_INDEX 174162 142606336
index 大小約136MB
3.這次建立有壓縮的index (效果更強)
SQL> drop index t_index;
Index dropped.
SQL> create index t_index on test_table1(object_type) compress;
Index created.
使用了壓縮的index ，consistent gets 下降到1194，效果更驚人
查詢一下index size.
SQL> SELECT segment_name, header_block, bytes
FROM dba_segments
WHERE segment_name = 'T_INDEX';
SEGMENT_NAME HEADER_BLOCK BYTES
----------------------------- ---------------------- -------------
T_INDEX 174162 83886080
index 大小約 80MB
使用壓縮的index 跟沒有壓縮的index 比較
consistent gets 從 1795 降到 1194 -> 33.48 %
cost 從 2272 降到 1287 -> 43.35%
bytes 從 136MB 縮小到 80MB -> 41.17%
難道compress index 就真的這麼好用?!，會不會有其他狀況導致compress index 的優點變成缺點呢，接下來我們在做更深入的實驗
1.建立一個測試 table
CREATE TABLE test_com
( a NUMBER, b NUMBER) );
2.
SQL> begin
2 for i in 1..500000 loop
3 insert into test_com values(mod(i,2),i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
2. 查詢一下此 table 的欄位重覆的值狀況如何
SQL> select count(distinct a),count(distinct b) from test_com;
COUNT(DISTINCTA) COUNT(DISTINCTB)
------------------------ -----------------------------
2 500000
SQL>
A欄位 裡面只有兩個不同的值，B欄位裡有 500000 都是不同的值
3.建立一般的 index ，並查看SIZE
SQL> create index test_com_idx1 on test_com(a);
Index created.
SQL> create index test_com_idx2 on test_com(b);
Index created.
SQL>
SQL> select segment_name , bytes/1024/1024 size_mb from dba_segments where segment_name like 'TEST_C
OM_%';
SEGMENT_NAME SIZE_MB
----------------------------------- -----------------
TEST_COM_IDX1 8
TEST_COM_IDX2 9
SQL>
4.接著建立 有compress 的index ，並查看SIZE
SQL> drop index test_com_idx1;
Index dropped.
SQL> drop index test_com_idx2;
Index dropped.
SQL>
SQL> create index test_com_idx1 on test_com(a) compress;
Index created.
SQL> create index test_com_idx2 on test_com(b) compress;
Index created.
SQL>
SQL> select segment_name , bytes/1024/1024 size_mb from dba_segments where segment_name like 'TEST_C
OM_%';
SEGMENT_NAME SIZE_MB
----------------------------------- ------------------
TEST_COM_IDX1 7
TEST_COM_IDX2 13
這邊看到A欄位從未壓縮到壓縮 8MB > 7MB ，而B欄位卻從9MB 增大到13MB，index 對於table 重覆值較少的欄位還會增加更多空間來存放 prefix ，想啟動壓縮的功能卻讓size 變大
當然在很多種狀況下，並不能用同一招來打天下 ，所以這也是為什麼Oracle 提供了很多的解決方案
Oracle 官方 Concepts 裡的說明
Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead, because every prefix entry has an overhead of 4 bytes associated with it.
index compress
優點
1.值的重覆越多壓縮的效果越好，節省更多的空間(但要是欄位值重覆很少的話，建立index compress 其實也沒太大用處)
2.掃描的block 大幅減少
缺點
1.index 更新時需要重新解壓縮 ->更新 ->壓縮，使用更多的執行時間與CPU
2.block 的爭用，當查詢的結果都集中再同一個block 時就會引起block 的爭用
3.bitmap index 不能壓縮
PS：
1.假如要對現有的index 啟動壓縮功能，可以利用rebuild index 重建index compress 或者解除壓縮功能
alter index T_INDEX rebuild compress;
alter index T_INDEX rebuild nocompress;
2.可以建立compress 的複合式index
create index T_INDEX on test_com(a,b,c) compress1; 只壓縮第一個欄位
create index T_INDEX on test_com(a,b,c) compress2; 對前兩個欄位進行壓縮
create index T_INDEX on test_com(a,b,c) compress3; 對前三個欄位進行壓縮
沒有留言:
張貼留言