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
