oracle 11gR2 advanced compress table

默认oracle建立表的时候是nocompress，compress for all operations 和 compress for direct_load operations都是11g以前的选项为了兼容以前而存在的。11g以后实际的压缩选项只有compress [basic]和compress for oltp两种，其他的query等是基于硬件的选项.

{ COMPRESS [ BASIC

| FOR { OLTP

| { QUERY | ARCHIVE } [ LOW | HIGH ]

}

]

| NOCOMPRESS

}

Compression can occur while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

• Single-row or array inserts and updates
• The following direct-path insert methods:
  • Direct path SQL*Loader
  • CREATE TABLE AS SELECT statements
  • Parallel INSERT statements
  • INSERT statements with an APPEND or APPEND_VALUES hint

官方文档中的这几种操作，经过实践是可以这样理解的，普通的单行或者批量的插入或更新压缩表(就是insert into select,insert values)也会压缩，但压缩比率很低。直接路径插入压缩表的方式压缩比高，其中以下四种情况是：

1 sqlldr noap/noap@ora11 direct=true control=compress.ctl log=compress.log

2 create table table_desc compress as select * from table_source;

3 insert /*+parallel(4)*/into table_desc select * from table_source;

4 insert /*+append*/ into table_desc select * from table_souce;

sqlldr noap/noap@ora11 control=compress.ctl log=compress.log

控制文件中写的使用append的方式也是归类为这种方式。

压缩表是具有压缩属性的表，只有在上述的操作情况下才允许发生压缩.

Sqlldr中direct=true的方式要慎用 在维护索引的时候实际她是在数据完全插入以后才会维护。而且如果中途失败了数据也不会回滚，索引还会失效。Sqlldr中direct的方式实际也会扫描高水位线，然后直接在文件中插入数据，不会产生sql语句经过缓存然后再插入数据。但常规路径插入的时候如果是append的方式那么他会扫描高水位线但不会找水位线下的剩余空间，而是直接在水位线之后插入数据，commit以后会升高水位线

实验如下

NO.1 用sqlldr方式插入数据

1 建立三种表，nocompress,compress ,compress for oltp

CREATE TABLE TEST_DBA_OBJECTS_NOCOMPRESS

PARTITION BY RANGE (CREATED)

(

PARTITION TEST_DBA_OBJECTS_20101212 VALUES LESS THAN (TO_DATE(' 2010-12-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

PARTITION TEST_DBA_OBJECTS_20101213 VALUES LESS THAN (TO_DATE(' 2010-12-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

PARTITION TEST_DBA_OBJECTS_20101214 VALUES LESS THAN (TO_DATE(' 2010-12-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

)

AS

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_TYPE,T1.CREATED

FROM DBA_OBJECTS T1,DBA_OBJECTS T2

WHERE 1=0;

CREATE TABLE TEST_DBA_OBJECTS_OLTP

PARTITION BY RANGE (CREATED)

(

PARTITION TEST_DBA_OBJECTS_20101212 VALUES LESS THAN (TO_DATE(' 2010-12-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,

PARTITION TEST_DBA_OBJECTS_20101213 VALUES LESS THAN (TO_DATE(' 2010-12-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ,

PARTITION TEST_DBA_OBJECTS_20101214 VALUES LESS THAN (TO_DATE(' 2010-12-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

) COMPRESS FOR OLTP

AS

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_TYPE,T1.CREATED

FROM DBA_OBJECTS T1,DBA_OBJECTS T2

WHERE 1=0;

CREATE TABLE TEST_DBA_OBJECTS_COMPRESS

PARTITION BY RANGE (CREATED)

(

PARTITION TEST_DBA_OBJECTS_20101212 VALUES LESS THAN (TO_DATE(' 2010-12-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

PARTITION TEST_DBA_OBJECTS_20101213 VALUES LESS THAN (TO_DATE(' 2010-12-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

PARTITION TEST_DBA_OBJECTS_20101214 VALUES LESS THAN (TO_DATE(' 2010-12-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

) COMPRESS

AS

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_TYPE,T1.CREATED

FROM DBA_OBJECTS T1,DBA_OBJECTS T2

WHERE 1=0;

2 造实验数据

SELECT * FROM (

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_TYPE,T1.CREATED

FROM DBA_OBJECTS T1,DBA_OBJECTS T2

)

WHERE ROWNUM < 1000000;

3 SQLLDR 用direct方式导入后表大小

SQL> EXEC dbms_stats.gather_table_stats('noap','TEST_DBA_OBJECTS_INSERT');

PL/SQL procedure successfully completed

SQL> EXEC dbms_stats.gather_table_stats('noap','TEST_DBA_OBJECTS_INSERT_OLTP');

PL/SQL procedure successfully completed

SQL> EXEC dbms_stats.gather_table_stats('noap','TEST_DBA_OBJECTS_INSERT_APPEND');

PL/SQL procedure successfully completed

SQL> EXEC dbms_stats.gather_table_stats('noap','TEST_DBA_OBJECTS_INSTAPPD_OLTP');

PL/SQL procedure successfully completed

SQL> EXEC dbms_stats.gather_table_stats('noap','TEST_DBA_OBJECTS_INSERT_NOCOM');

PL/SQL procedure successfully completed

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_COMPRESS';

SUM(T.BYTES)/1024/1024

----------------------

11.25

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_OLTP';

SUM(T.BYTES)/1024/1024

----------------------

13.25

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_NOCOMPRESS';

SUM(T.BYTES)/1024/1024

----------------------

29.25

NO.2 用 insert 常规方式以及append方式

CTAS分别建立以compress，compress for oltp,nocompress三种表并

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as noap

===================================

常规插入

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSERT COMPRESS AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.047 seconds

SQL> INSERT INTO TEST_DBA_OBJECTS_INSERT SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 1.719 seconds

SQL> COMMIT;

Commit complete

Executed in 0.031 seconds

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSERT_OLTP COMPRESS FOR OLTP AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.094 seconds

SQL> INSERT INTO TEST_DBA_OBJECTS_INSERT_OLTP SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 24.36 seconds

SQL> COMMIT;

Commit complete

Executed in 0.047 seconds

=========================================================

Append 方式

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSERT_APPEND COMPRESS AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.094 seconds

SQL> INSERT /*+ APPEND*/INTO TEST_DBA_OBJECTS_INSERT_APPEND SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 0.844 seconds

SQL> COMMIT;

Commit complete

Executed in 0.031 seconds

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSTAPPD_OLTP COMPRESS FOR OLTP AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.141 seconds

SQL> INSERT /*+ APPEND*/INTO TEST_DBA_OBJECTS_INSTAPPD_OLTP SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 0.86 seconds

SQL> COMMIT;

Commit complete

Executed in 0.016 seconds

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSERT_NOCOM AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.062 seconds

SQL> INSERT INTO TEST_DBA_OBJECTS_INSERT_NOCOM SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 4.5 seconds

SQL> COMMIT;

Commit complete

Executed in 0.016 seconds

查看结果

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT';

SUM(T.BYTES)/1024/1024

----------------------

26

Executed in 0.781 seconds

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT_OLTP';

SUM(T.BYTES)/1024/1024

----------------------

13

Executed in 0.781 seconds

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT_APPEND';

SUM(T.BYTES)/1024/1024

----------------------

11

Executed in 0.781 seconds

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSTAPPD_OLTP';

SUM(T.BYTES)/1024/1024

----------------------

13

Executed in 0.875 seconds

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT_NOCOM';

SUM(T.BYTES)/1024/1024

----------------------

29

Executed in 0.844 seconds

由以上数据可以看出来常规insert的插入到压缩表,压缩比是

no compress(29)　<　compress basic(26)　<　compress for oltp(13)

append方式插入到压缩表，压缩比是

no compress (29) <　compress for oltp(13) 　< compress basic(11)

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR

2 FROM USER_TABLES T

3 WHERE T.TABLE_NAME IN ('TEST_DBA_OBJECTS_INSERT',

4 'TEST_DBA_OBJECTS_INSERT_OLTP',

5 'TEST_DBA_OBJECTS_INSERT_APPEND',

6 'TEST_DBA_OBJECTS_INSTAPPD_OLTP',

7 'TEST_DBA_OBJECTS_INSERT_NOCOM');

TABLE_NAME COMPRESSION COMPRESS_FOR

------------------------------ ----------- ------------

TEST_DBA_OBJECTS_INSERT ENABLED BASIC

TEST_DBA_OBJECTS_INSERT_APPEND ENABLED BASIC

TEST_DBA_OBJECTS_INSERT_NOCOM DISABLED

TEST_DBA_OBJECTS_INSERT_OLTP ENABLED OLTP

TEST_DBA_OBJECTS_INSTAPPD_OLTP ENABLED OLTP

Executed in 0.094 seconds

NO3 .并行插入压缩表数据也会对数据压缩

SQL> CREATE TABLE TEST_DBA_OBJECTS_INSERT_PALLEL COMPRESS FOR OLTP AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS WHERE 1=2;

Table created

Executed in 0.047 seconds

SQL>

SQL> INSERT/* +PARALLEL(4)*/ INTO TEST_DBA_OBJECTS_INSERT_PALLEL SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

999999 rows inserted

Executed in 27.579 seconds

SQL> COMMIT;

Commit complete

Executed in 0.188 seconds

SQL>

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT_PALLEL';

SUM(T.BYTES)/1024/1024

----------------------

13

Executed in 0.812 seconds

NO.4 CTAS的方式实际也是用direct_path方式实现数据插入的，这样数据也会压缩；

SQL> CREATE TABLE TEST_DBA_OBJECTS_CTAS_BASIC COMPRESS AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

Table created

SQL> CREATE TABLE TEST_DBA_OBJECTS_CTAS_OLTP COMPRESS FOR OLTP AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

Table created

SQL> CREATE TABLE TEST_DBA_OBJECTS_CTAS_NOCOM AS SELECT * FROM TEST_DBA_OBJECTS_NOCOMPRESS;

Table created

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_CTAS_BASIC';

SUM(T.BYTES)/1024/1024

----------------------

11

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_CTAS_OLTP';

SUM(T.BYTES)/1024/1024

----------------------

13

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_CTAS_NOCOM';

SUM(T.BYTES)/1024/1024

----------------------

29

Packing Compressed Tables

If you use conventional DML on a table compressed with basic compression, then all inserted and updated rows are stored uncompressed. To "pack" the compressed table such that these rows are compressed, you can use an ALTER TABLE MOVE statement.

对于本身是压缩表但是插入数据是按照常规插入的情况下，官方虽然说没有压缩但事实之前的实验表明数据占据的空间还是比存在于非压缩表中占用空间要少，为了让这些数据进一步压缩，可以使用alter table move这种语句来实现，来看一下实验；

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT';

SUM(T.BYTES)/1024/1024

----------------------

26

SQL> ALTER TABLE TEST_DBA_OBJECTS_INSERT MOVE ;

Table altered

SQL> SELECT SUM(T.BYTES)/1024/1024 FROM USER_SEGMENTS T WHERE T.SEGMENT_NAME = 'TEST_DBA_OBJECTS_INSERT';

SUM(T.BYTES)/1024/1024

----------------------

11

压缩表是具有压缩属性的表，只有在上述的操作情况下才允许发生压缩.[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25093763/viewspace-1045783/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25093763/viewspace-1045783/