压缩数据以节省空间和提高速度(某位帅多友情奉献的资料，好东东！)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; 
  回复  更多评论