壓縮數據以節省空間和提高速度(某位帥多友情奉獻的資料，好東東！)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
http://www.stcore.com/html/2005/1130/104979.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;
  回復  更多評論