Oracle HCC简单测试

HYBRID COLUMNAR COMPRESSION [HCC] is only for EXADATA.

Online Archival Compression Syntax：
CREATE TABLE emp (
  emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR ARCHIVE [ LOW | HIGH ];


Warehouse Compression Syntax：
CREATE TABLE emp (
  emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR QUERY [ LOW | HIGH ];


自己的测试：

SQL> create table stab(a int, b varchar2(20)) compress for query high;

Table created.

SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ------------------------------ -------- ------------
GAN                            STAB                           GANTS                          ENABLED  QUERY HIGH

SQL> insert into stab values(1, '1111111');

1 row created.

SQL> c/1/2
  1* insert into stab values(2, '1111111')
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ------------------------------ -------- ------------
GAN                            STAB                           GANTS                          ENABLED  QUERY HIGH

SQL> l
  1* select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab
SQL> /

       FNO        BLK       RNUM          A B
---------- ---------- ---------- ---------- --------------------
         5        189          0          1 1111111
         5        189          1          2 1111111
SQL> l
  1* select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab
SQL> /

       FNO        BLK       RNUM          A B
---------- ---------- ---------- ---------- --------------------
         5        189          0          1 1111111
         5        189          1          2 1111111

SQL> alter table stab move compress for archive high;

Table altered.

SQL> select owner,table_name,tablespace_name,compression,compress_for from all_tables where owner='GAN' and table_name='STAB';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ------------------------------ -------- ------------
GAN                            STAB                           GANTS                          ENABLED  ARCHIVE HIGH

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rnum, newtab.* from stab newtab;

       FNO        BLK       RNUM          A B
---------- ---------- ---------- ---------- --------------------
         5        227          0          1 1111111
         5        227          1          2 1111111