Oracle 压缩表类型测试

g_hk

The DBMS_COMPRESSION package uses the constants shown in Table 34-1, "DBMS_COMPRESSION Constants - Compression Types"e:

Table 34-1 DBMS_COMPRESSION Constants - Compression Types

Constant	Type	Value	Description
COMP_NOCOMPRESS	NUMBER	1	No compression
COMP_FOR_OLTP	NUMBER	2	OLTP compression
COMP_FOR_QUERY_HIGH	NUMBER	4	High compression level for query operations
COMP_FOR_QUERY_LOW	NUMBER	8	Low compression level for query operations
COMP_FOR_ARCHIVE_HIGH	NUMBER	16	High compression level for archive operations
COMP_FOR_ARCHIVE_LOW	NUMBER	32	Low compression level for archive operations

Note:

The constants COMP_FOR_QUERY_HIGH, COMP_FOR_QUERY_LOW, COMP_FOR_ARCHIVE_HIGH, and COMP_FOR_ARCHIVE_LOW are used only in the context of Exadata, specifically the feature, Exadata Hybrid Columnar Compression (EHCC) which offers higher compression ratios for direct path loaded data. For more information, see the Oracle Exadata Storage Server Online Documentation Library.

More info: http://oracle.su/docs/11g/appdev.112/e10577/d_compress.htm

----------------------------------------------------
Test Examples:
SQL> alter table ct2 move compress; #看来这个操作不一定产生真正的压缩表。

Table altered.

SQL> select rowid, a.* from ct2 a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAqAAFAAAAErAAA       1513
AAAAAA1513


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2', 'AAASAqAAFAAAAErAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2','AAASAQAAFAAAAERAAA')
-----------------------------------------------------------------------
                                                                      1


SQL> create table ct2_1 compress as select * from ct2;

Table created.

SQL> select rowid,a.* from ct2_1 a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASArAAFAAAACrAAA       1513
AAAAAA1513


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASArAAFAAAACrAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASARAAFAAAACRAAA')
-------------------------------------------------------------------------
                                                                        1

SQL> insert into ct2_1 select * from ct2_1;

262144 rows created.

SQL> /

524288 rows created.

SQL> commit;

Commit complete.

SQL>  select rowid,a.* from ct2_1 a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASArAAFAAAACrAAA       1513
AAAAAA1513


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASArAAFAAAACrAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASARAAFAAAACRAAA')
-------------------------------------------------------------------------
                                                                        1

SQL> alter table ct2_1 move compress;

Table altered.

SQL> alter system switch logfile;

System altered.

SQL> select rowid,a.* from ct2_1 a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAsAAFAAAA+CAAA       1034
AAAAAA1034


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASAsAAFAAAA+CAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASASAAFAAAA+CAAA')
-------------------------------------------------------------------------
                                                                        2
终于产生了一个OLT compress类型的压缩表。

再来产生一个query high类型的压缩表看看：
SQL> create table ct1 compress for query high as select * from tabx;

Table created.

SQL> select count(*) from ct1;

  COUNT(*)
----------
      8192
SQL> select rowid from ct1 where rownum<2;

ROWID
------------------
AAAR/QAAFAAAACjAAA

SQL> select rowid from tabx where rownum<2;

ROWID
------------------
AAAR/PAAFAAAACDAAA

SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT1', 'AAAR/QAAFAAAACjAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT1','AAAR/QAAFAAAACJAAA')
-----------------------------------------------------------------------
                                                                      4

SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'TABX', 'AAAR/PAAFAAAACDAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','TABX','AAAR/PAAFAAAACDAAA')
------------------------------------------------------------------------
                                                                       1


query low类型的压缩表：
SQL> create table ct8 compress for query low as select * from tabx;

Table created.

SQL> select rowid,a.* from ct8 a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAtAAFAAAACrAAA       1513
AAAAAA1513


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT8', 'AAASAtAAFAAAACrAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT8','AAASATAAFAAAACRAAA')
-----------------------------------------------------------------------
                                                                      8

archive high类型压缩表：
SQL> create table ct16 compress for archive high as select * from tabx;

Table created.

SQL>  select rowid,a.* from ct16  a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAuAAFAAAAC7AAA          1
AAAAAA1


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT16', 'AAASAuAAFAAAAC7AAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT16','AAASAUAAFAAAAC7AAA')
------------------------------------------------------------------------
                                                                      16

archive low类型压缩表：
SQL> create table ct32 compress for archive low as select * from tabx;

Table created.

SQL> select rowid,a.* from ct16  a where rownum<2;

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAuAAFAAAAC7AAA          1
AAAAAA1


SQL> c/16/32
  1* select rowid,a.* from ct32  a where rownum<2
SQL> /

ROWID                       A
------------------ ----------
B
--------------------------------------------------------------------------------
AAASAvAAFAAAADDAAA          1
AAAAAA1


SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT32', 'AAASAvAAFAAAADDAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT32','AAASAVAAFAAAADDAAA')
------------------------------------------------------------------------
                                                                      32