ORACLE 11g 資料壓縮測試

先建立一個沒有啟用壓縮功能的測試表格
SQL> create table test01
  2    ( a varchar2(100),
  3      b varchar2(20) );
 

確認壓縮功能沒有啟用
SQL> SELECT table_name --資料表名稱
  2        ,compression --是否啟用壓縮
  3        ,compress_for --壓縮類型
  4    FROM dba_tables
  5    where table_name='TEST01';

TABLE_NAME      COMPRESSION      COMPRESS_FOR
--------------- ---------------- ------------------------
TEST01          DISABLED


寫入資料
SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> commit;
Commit complete.


查詢test01的大小
SQL> select segment_name,bytes,blocks
  2      from dba_segments
  3      where segment_name='TEST01';

SEGMENT_NAME              BYTES     BLOCKS
-------------------- ---------- ----------
TEST01                  6291456        768


將test01資料刪除
SQL> truncate table test01;
Table truncated.


啟用test01的壓縮功能,並指定壓縮類型為OLTP
SQL> ALTER TABLE test01 COMPRESS FOR OLTP;
Table altered.


也可以在建立表格時指定,語法如下:
create table my_compressed_table
  ( col1 number(20), col2 varchar2(300), ... ) compress for all operations ;


確認縮壓功能有啟用且類型為OLTP
SQL> SELECT table_name
  2        ,compression
  3        ,compress_for
  4    FROM dba_tables
  5    where table_name='TEST01';
 
TABLE_NAME      COMPRESSION      COMPRESS_FOR
--------------- ---------------- ------------------------
TEST01          ENABLED          OLTP


寫入資料到test01
SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> commit;
Commit complete.


重新查詢資料大小,發現容量比未壓縮小
SQL> select segment_name,bytes,blocks
  2          from dba_segments
  3         where segment_name='TEST01';

SEGMENT_NAME              BYTES     BLOCKS
-------------------- ---------- ----------
TEST01                  5242880        640

Oracle DB的listener.log清理