oracle壓縮表表空間

壓縮資料以節省空間和提高速度(某位帥多友情奉獻的資料，好東東！)2007-03-30 15:55使用表壓縮來節省空間並提高查詢效能。 

　　很多決策支援系統通常都涉及到儲存於幾個特大表中的大量資料。隨著這些系統的發展，對磁碟空間的需求也在快速增長。在當今的環境下，儲存著數百TB（太位元組）的資料倉儲已經變得越來越普遍。 
　　 
　　為了幫助處理磁碟容量問題，在Oracle9i第2版中引入了表壓縮特性，它可以極大地減少資料庫表所需要的磁碟空間數量，並在某些情況下提高查詢效能。 
　　 
　　在本文中，我將向你說明表壓縮是如何工作的，以及在構建和管理資料庫時如何配置表空間。我還將基於一些示例測試結構討論一些效能問題，以幫助你瞭解使用表壓縮預計能獲得多大好處。 
　　 
　　表壓縮是如何工作的 
　　 
　　在Orcle9i第2版中，表壓縮特性透過刪除在資料庫表中發現的重複資料值來節省空間。壓縮是在資料庫的資料塊級別上進行的。當確定一個表要被壓縮後，資料庫便在每一個資料庫資料塊中保留空間，以便儲存在該資料塊中的多個位置上出現的資料的單一複製。這一被保留的空間被稱作符號表（symbol table）。被標識為要進行壓縮的資料只儲存在該符號表中，而不是在資料庫行本身內。當在一個資料庫行中出現被標識為要壓縮的資料時，該行在該符號表中儲存一個指向相關資料的指標，而不是資料本身。節約空間是透過刪除表中資料值的冗餘複製而實現的。 
　　 
　　對於使用者或應用程式開發人員來說，表壓縮的效果是透明的。無論表是否被壓縮，開發人員訪問表的方式都是相同的，所以當你決定壓縮一個表時，不需要修改SQL查詢。表壓縮的設定通常由資料庫管理人員或設計人員進行配置，幾乎不需要開發人員或使用者參與。 
　　 
　　如何建立一個壓縮的表 
　　 
　　要建立一個壓縮的表，可在CREATE TABLE語句中使用COMPRESS關鍵字。COMPRESS關鍵字指示Oracle資料庫儘可能以壓縮的格式儲存該表中的行。下面是CREATE TABLE COMPRESS語句的一個例項： 
　　 
　　CREATE TABLE SALES_HISTORY_COMP ( 
　　PART_ID　　　 VARCHAR2(50) NOT NULL, 
　　STORE_ID　　　VARCHAR2(50) NOT NULL, 
　　SALE_DATE　　 DATE NOT NULL, 
　　QUANTITY　　　NUMBER(10,2) NOT NULL 
　　) 
　　COMPRESS 
　　; 
　　 
　　或者，你可以用ALTER TABLE語句來修改已有表的壓縮屬性，如下所示： 
　　 
　　ALTER TABLE SALES_HISTORY_COMP COMPRESS; 
　　 
　　為了確定是否已經利用COMPRESS對一個表進行了定義，可查詢USER_TABLES資料字典檢視並檢視COMPRESSION列，如下面的例子所示： 
　　 
　　SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES; 
　　 
　　TABLE_NAME　　　　　 COMPRESSION 
　　------------------　 ----------- 
　　SALES_HISTORY　　　　DISABLED 
　　 
　　SALES_HISTORY_COMP　 ENABLED 
　　 
　　也可以在表空間級別上定義COMPRESS屬性，既可以在生成時利用CREATE TABLESPACE來定義，也可以稍後時間利用ALTER TABLESPACE來定義。與其他儲存引數類似，COMPRESS屬性也具有一些繼承特性。當在一個表空間中建立一個表時，它從該表空間繼承COMPRESS屬性。為了確定是否已經利用COMPRESS對一個表空間進行了定義，可查詢USER_TABLESPACES資料字典檢視並檢視DEF_TAB_COMPRESSION列，如下面的例子所示： 
　　 
　　SELECT TABLESPACE_NAME, 
　　DEF_TAB_COMPRESSION 
　　FROM DBA_TABLESPACES; 
　　 
　　TABLESPACE_NAME　　 DEF_TAB_COMPRESSION 
　　---------------　　------------------- 
　　DATA_TS_01　　　　　DISABLED 
　　INDEX_TS_01　　　　 DISABLED 
　　 
　　正如你所預計的那樣，你可以在一個表空間直接壓縮或解壓縮一個表，而不用考慮表空間級別上的COMPRESS屬性。 
　　 
　　向一個壓縮的表中載入資料 
　　 
　　請注意，當你像上面那樣指定COMPRESS時，你並沒在實際壓縮任何資料。上面的這些命令只是修改了一個資料字典的設定。只有你向一個表中載入或插入資料時才會實際壓縮資料。 
　　 
　　而且，為了確保資料被實際壓縮，你需要利用一種正確的方法將資料載入或插入到表中。只有在利用以下4種方法之一批次載入或批次插入過程中才會進行資料壓縮： 
　　 
　　直接路徑SQL*Loader 
　　帶有APPEND提示的序列INSERT 
　　並行INSERT 
　　CREATE TABLE ... AS SELECT 
　　 
　　如果在一個平面檔案中有輸入資料是可用的，那麼直接路徑SQL*Loader方法是將這些輸入資料載入至一個表格中最方便的手段。下面給出一個示例： 
　　 
　　$sqlldr sanjay/sanjay@proddb control=sales_history.ctl direct=true 
　　 
　　如果在一個登臺表中有輸入資料，那麼你可以使用帶有APPEND提示的序列INSERT方法或者並行INSERT方法。 
　　 
　　作為一個例子，請看一個名為SALES_HISTORY的未壓縮登臺表中的可用輸入資料。用序列INSERT方法時，你可以使用以下的語句向已壓縮表中插入資料： 
　　 
　　INSERT /*+ APPEND */ 
　　 
　　INTO SALES_HISTORY_COMP 
　　SELECT * FROM SALES_HISTORY; 
　　 
　　或者，你也可以用並行INSERT方法將資料由一個登臺錶轉移到一個已壓縮表中，如下所示： 
　　 
　　ALTER SESSION ENABLE PARALLEL DML; 
　　 
　　INSERT /*+PARALLEL(SALES_HISTORY_COMP,4)*/ 
　　INTO SALES_HISTORY_COMP 
　　SELECT * FROM SALES_HISTORY; 
　　 
　　請注意，在使用並行INSERT方法時，你需要首先利用ALTER SESSION ENABLE PARALLEL DML命令為會話期來啟動並行DML。 
　　 
　　如果輸入資料位於一個平面檔案中，那麼你也可以使用一個外部表，然後將這些資料插入到一個壓縮表中，就像這些資料放在一個登臺表中可用一樣。（對外部表的討論超出了本文的範圍）。 
　　 
　　你還可以使用CREATE TABLE ... AS SELECT語句一次生成一個壓縮表，並將資料插入至其中。 這裡有一個例子： 
　　 
　　CREATE TABLE SALES_HISTORY_COMP 
　　COMPRESS 
　　AS SELECT * FROM SALES_HISTORY; 
　　 
　　如果你沒有使用正確的載入或INSERT方法，那麼即使使用COMPRESS對錶格進行了定義，該表中的資料也將仍然保持未壓縮狀態。 例如，如果你使用慣用路徑SQL*Loader或正則INSERT語句，那麼資料仍然是未壓縮的。 
　　 
　　什麼時候使用表壓縮 
　　 
　　Oracle資料庫選擇用來壓縮表資料或不壓縮表資料的方式已暗中牽涉到了最適合於表壓縮的應用程式。如上所述，一個表中已被使用COMPRESS定義的資料，只有在使用直接路徑模式被載入或利用新增（append）或並行模式被插入時，才會得到壓縮。透過正則插入語句插入的資料將保持未壓縮狀態。 
　　 
　　在線上事務處理（OLTP）系統中，通常是使用正則插入模式來插入資料的。因此，使用表壓縮通常不會使這些表格獲得太大的好處。 表壓縮對於那些只載入一次但多次讀取的只讀表格具有最佳效果。例如，資料倉儲應用程式中所用的表格特別適合於進行表壓縮。 
　　 
　　此外，在一個已壓縮表中更新資料可能要求資料行為非壓縮的，這樣就達不到進行壓縮的目的。因此，那些需要經常進行更新操作的表不適於進行表壓縮。 
　　 
　　最後，讓我們來看一下行刪除對錶壓縮應用的影響。當你刪除一個壓縮的表中的一行時，資料庫將釋放該行在資料庫資料塊中所佔據的空間。 這一自由空間可以由未來插入的資料重新使用。但是，由於以慣用模式插入的行不能被壓縮，所以它不太可能適合放在一個被壓縮的行所釋放的空間。大量的相繼的DELETE與INSERT語句可能會導致磁碟碎片，且所浪費的空間甚至會多於使用壓縮所能節省的空間。 
　　 
　　壓縮一個已有的未壓縮表 
　　 
　　如果你有一個已有的未壓縮表，那麼你可以利用ALTER... MOVE語句對其進行壓縮。例如，可以利用以下方法對一個名為SALES_HISTORY_TEMP的未壓縮表進行壓縮： 
　　 
　　ALTER TABLE SALES_HISTORY_TEMP 
　　MOVE COMPRESS; 
　　 
　　你也可以將ALTER TABLE ...MOVE語句用於解壓縮一個表，如下例所示： 
　　 
　　ALTER TABLE SALES_HISTORY_TEMP 
　　MOVE NOCOMPRESS; 
　　 
　　請注意，ALTER TABLE ...MOVE操作會獲得一個對該表操作的EXCLUSIVE鎖，它可以在該語句執行過程中禁止對該表進行任何DML操作。你可以利用Oracle9i資料庫的線上表重定義特性來避免這一可能出現的問題。 
　　 
　　壓縮一個物化檢視 
　　 
　　你可以使用用於壓縮表的類似方式來壓縮物化檢視。下面的命令生成一個壓縮的物化檢視： 
　　 
　　CREATE MATERIALIZED VIEW MV_SALES_COMP 
　　COMPRESS 
　　AS SELECT P.PART_NAME, H.STORE_ID, H.SALE_DATE, H.QUANTITY 
　　FROM SALES_HISTORY H, PARTS P 
　　WHERE P.PART_ID = H.PART_ID; 
　　 
　　基於多個表的聯接生成的物化檢視通常很適於壓縮，因為它們通常擁有大量的重複資料項。你可以使用ALTER MATERIALIZED VIEW命令來改變一個物化檢視的壓縮屬性。下面的命令顯示瞭如何壓縮一個已有的未壓縮的物化檢視。 
　　 
　　ALTER MATERIALIZED VIEW MV_SALES COMPRESS; 
　　 
　　當你使用此命令時，請注意通常是在下一次重新整理該物化檢視時才會進行實際的壓縮。 
　　 
　　壓縮一個已分割槽的表 
　　 
　　在對已分割槽的表應用壓縮時，可以有很多種選擇。你可以在表級別上應用壓縮，也可以在分割槽級別上應用壓縮。例如，程式碼清單 1中的CREATE TABLE語句建立一個具有4個分割槽的表。 由於是在表級別指定了COMPRESS，所以對全部4個分割槽都進行壓縮。 
　　 
　　由於可以在分割槽級別上指定壓縮屬性，所以你可以選擇壓縮某些分割槽，而使另一些分割槽保持未壓縮狀態。程式碼清單 2中的示例說明了如何在分割槽級別上指定壓縮屬性。 
　　 
　　在程式碼清單 2中，壓縮了兩個表分割槽(SALES_Q1_03和SALES_Q2_03) ，而另外兩個分割槽未被壓縮。要注意，在分割槽級別上指定的壓縮屬性會取代對該分割槽在表級別上特定的壓縮屬性。如果未為一個分割槽指定壓縮屬性，那麼該分割槽將繼承在表級別上指定的壓縮屬性。在程式碼清單 2中，由於未對分割槽SALES_Q3_03和SALES_Q4_03指定壓縮屬性，所以這兩個分割槽繼承表級別上指定的屬性值（在本例情況下為預設的NOCOMPRESS）。 
　　 
　　在透過壓縮來使用已分割槽的表時，它可以提供一個獨特的好處。對錶進行分割槽的一個非常有用的方法是將要對其進行DML操作（插入、更新與刪除）的資料放入與只讀檔案分開的分割槽內。例如，在程式碼清單 2的表定義中，根據SALE_DATE對銷售資料進行了分割槽，這樣可將每一季度的銷售歷史資料儲存在一個單獨的分割槽內。在此示例中，2003年第1、2季度的銷售資料不能被修改，所以將它們置於壓縮分割槽SALES_Q1_03 和SALES_Q2_03中。對於第3、4季度的銷售資料仍可以進行修改，所以相應的分割槽SALES_Q3_03和SALES_Q4_03保持未壓縮狀態。 
　　 
　　如果在2003年第3季度末，SALES_Q3_03分割槽中的資料變為只讀的，那麼你可以利用ALTER TABLE ...MOVE PARTITION命令對此分割槽進行壓縮，如下面的語句所示： 
　　 
　　ALTER TABLE SALES_PART_COMP 
　　MOVE PARTITION SALES_Q3_03 COMPRESS; 
　　 
　　要找出一個表中的哪些分割槽被壓縮了，可以查詢資料字典檢視USER_TAB_PARTITIONS，如下例所示： 
　　 
　　SELECT TABLE_NAME, PARTITION_NAME, 
　　COMPRESSION 
　　 
　　FROM USER_TAB_PARTITIONS; 
　　 
　　TABLE_NAME　　　PARTITION_NAME COMPRESSION 
　　---------------------------- ----------- 
　　SALES_PART_COMP　SALES_Q4_03　　DISABLED 
　　SALES_PART_COMP　SALES_Q1_03　　ENABLED 
　　SALES_PART_COMP　SALES_Q2_03　　ENABLED 
　　SALES_PART_COMP　SALES_Q3_03　　ENABLED 
　　 
　　定量地評價壓縮帶來的好處 
　　 
　　使用表壓縮的最主要原因是要節省儲存空間。壓縮形式的表所佔用的空間通常小於其非壓縮形式所佔用的空間。為了說明這一點，可考慮以下測試，其中有兩個表--一個是未壓縮的（SALES_HISTORY），一個是壓縮的（SALES_HISTORY_COMP）。這兩個表都是利用直接路徑SQL＊Loader由一個包含有200萬行的單一平面檔案載入的。在完成了對兩個表的資料載入後，壓縮的表所佔用的空間差不多是未壓縮表的一半。程式碼清單 3顯示了分析結果。 
　　 
　　一個壓縮的表可以儲存在更少的資料塊中，從而節省了儲存空間，而使用更少的資料塊也意味著效能的提高。 在一個I/O受到一定限制的環境中對一個壓縮的表進行查詢通常可以更快速地完成，因為他們需要閱讀的資料庫資料塊要少得多。為了說明這一點，我對一個壓縮的表和一個未壓縮的表進行查詢，並執行一個SQLTRACE/TKPROF分析。程式碼清單 4顯示了該分析結果。 
　　 
　　SQLTRACE/TKPROF報告表明：我對該壓縮表執行的物理和邏輯I/O操作相對於對非壓縮表進行的相應查詢要少得多，因而執行得也更快得多。 
　　 
　　效能開銷 
　　 
　　由於表壓縮是在批次載入時進行的，所以資料載入操作會因涉及附加的內務操作而需要額外的處理工作。為了衡量壓縮對效能的影響，我進行了一個測試，在該測試中，我向兩個相同的表中（一個壓縮的表，另一個未壓縮的表）載入了（利用直接路徑SQL*Loader）100萬行資料。表 1顯示了由SQL*Loader日誌檔案中取出的結果，它們給出了向這兩個壓縮的與非壓縮的表中載入資料花費了多少時間。 
　　　 
　　表1：比較未壓縮的表與壓縮的表的載入時間 
　　 
　　載入壓縮的表所需要的額外時間源自在資料載入過程中所執行的壓縮操作。在實際情況下，實際時間差取決於表的設計與給定環境下的資料的佈局。 
　　 
　　結論 
　　 
　　Oracle9i第2版中的表壓縮特性可以節省大量的磁碟空間，尤其是對於具有大型只讀表的資料庫來說更是如此。如果你能記住載入和插入需要，並能確定那些適於進行壓縮的表，那麼你會發現，表壓縮是節省磁碟空間的絕佳方式，在某些情況下還可以提高查詢效能。 

ref: 壓縮已分割槽的表：http://xsb.itpub.net/post/419/57064http://www.oracle.com/global/cn/oramag/oracle/04-mar/o24tech_data.html 
 



附： 
1， 壓縮一個已存在的表空間： 
alter tablespace users default compress; 
僅對之後特殊方式插入的資料壓縮！ 

2， 壓縮已分割槽表 
對已分割槽的表（甚至帶子分割槽）進行壓縮，如果不能一步完成，那麼： 
分兩步半完成： 

alter table test compress; 

select 'alter table test move subpartition '|| subpartition_name||';' from user_tab_subpartitions where table_name like 'TEST'; 

除表可以壓縮外，分割槽表可以壓縮，索引可以壓縮，物化檢視也可以壓縮。語法類似。 
注：除索引外，壓縮屬性可以繼承表空間的壓縮屬性。表空間改成壓縮的： 
alter tablespace ts_test default compress; 

以下5種情況可以發揮壓縮特性： 
　　直接路徑SQL*Loader 
　　帶有APPEND提示的序列INSERT 
　　並行INSERT 
　　CREATE TABLE ... AS SELECT 
　　alter table move 

壓縮可以大幅度減少空間佔用(可壓縮60%以上)，從而減少IO量，提高效能。 

  回覆  更多評論
  

--刪除分割槽 
declare 
-- 這裡是本地變數 
i integer; 
lc_date varchar2(10); 
cursor c_area_code is 
select 'ALTER TABLE odsstat.WID_SETT_TICKET_DAY_' || region_id || ' DROP PARTITION P' || lc_date || ';' a1 
from area_code 
where region_id <= 2500; 
begin 
-- 這裡是測試語句 
for i in 1 .. 31 loop 
lc_date := to_char(to_date('20100531', 'YYYYMMDD') + i, 'YYYYMMDD'); 
for c1 in c_area_code loop 
dbms_output.put_line(c1.a1); 
end loop; 
end loop; 
end; 
--壓縮分割槽 
declare 
-- 這裡是本地變數 
i integer; 
lc_date varchar2(10); 
cursor c_area_code is 
select 'ALTER TABLE odsstat.WID_SETT_TICKET_DAY_' || region_id || ' MOVE PARTITION P' || lc_date || 
' COMPRESS;' a1 
from area_code 
where region_id <= 2500; 
begin 
-- 這裡是測試語句 
for i in 1 .. 31 loop 
lc_date := to_char(to_date('20100630', 'YYYYMMDD') + i, 'YYYYMMDD'); 
for c1 in c_area_code loop 
dbms_output.put_line(c1.a1); 
end loop; 
end loop; 
end; 
--增加分割槽 
declare 
-- 這裡是本地變數 
i integer; 
lc_date varchar2(10); 
cursor c_area_code is 
select 'ALTER TABLE odsstat.WID_SETT_TICKET_DAY_' || region_id || ' ADD PARTITION P' || lc_date || ' VALUES (' || 
lc_date || ') TABLESPACE SETTDATA_01 NOLOGGING;' a1 
from area_code 
where region_id <= 2500; 
begin 
-- 這裡是測試語句 
for i in 1 .. 31 loop 
lc_date := to_char(to_date('20100831', 'YYYYMMDD') + i, 'YYYYMMDD'); 
for c1 in c_area_code loop 
dbms_output.put_line(c1.a1); 
end loop; 
end loop; 
end; 
  回覆  更多評論