oracle （11gR2）中的表壓縮

壓縮的好處

壓縮是一項更適合於資料倉儲環境的一項oracle 特性。壓縮的好處包括以下兩個方面：

1、節省儲存空間，對應海量的資料來說非常有意義。

2、查詢效能將會提高（不是絕對會提高），因為物理I/O 減少，並且提高了記憶體中資料塊的命中率。

不利的方面：

可能增加CPU 的負載。影響DML操作的效能。表中的碎片增多，浪費儲存空間。更多詳細的說明請參考：

造成這些結果，主要是因為誤用了表壓縮技術，因為表壓縮技術是一項更適合於資料倉儲環境的一項oracle 特性。即資料主要是用作查詢目的，很少涉及DML操作。

oracle 提供的壓縮方式及特性

oracle 提高了4總壓縮方式，分別適用不同的場景。分別是：

basic compression：壓縮度高，CPU開銷最低，適用於DDS。

OLTP compression : 壓縮度高，CPU開銷最低，適用於OLTP,DDS。

注：壓縮可以指定在表空間級，表級，分割槽級。如果表空間帶有壓縮屬性那麼其中的表預設也帶有壓縮屬性，但是表級的壓縮屬性可以覆蓋表空間級的壓縮屬性，表的壓縮屬性與分割槽的壓縮屬性的關係也是這樣的。以下主要講的是表的壓縮。

另外有兩種壓縮方式，不過這兩種壓縮方式使用範圍有限：

SQL> create table object_copy compress for query
2 as select * from dba_objects;
as select * from dba_objects
*
ERROR at line 2:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

也就是說使用混合柱狀壓縮表空間需要位於exadata一體機上面。更多關於柱狀壓縮的資料，可以參考：

建立表的時候透過指定compress basic 關鍵字使表使用basic compression的壓縮方式。如果只使用compress 關鍵字不指定壓縮方式，預設是basic方式。使用basic 方式壓縮的特性在於:使用直接路徑insert 的記錄是壓縮的，update 操作相當於解壓縮。

SQL> create table objects_1
2 compress basic
3 as select object_id,object_name
4 from dba_objects;
Table created.

指定OLTP方式的壓縮方法指定關鍵字:compress for oltp。OLTP方式的壓縮方式特性

在於：使用直接路徑insert 的記錄依然是壓縮的，傳統的insert 語句插入的記錄也是壓縮的，update 語句操作過後資料依然是壓縮的。

oracle壓縮實戰

如何知道一個表是否是壓縮的，壓縮方式是什麼，可以透過以下的查詢來獲得：

SQL> select table_name,compression,compress_for
2 from user_tables
3 where table_name = 'OBJECTS_1';
TABLE_NAME COMPRESSION COMPRESS_FOR
------------ ---------------- ------------------------
OBJECTS_1 ENABLED BASIC

表空間是否是壓縮的可以透過查詢dba_tablespaces 資料字典。表分割槽是否是壓縮的可以透過查詢[dba|all|user]_tab_partitions 資料字典。

建立如下的分割槽表，每個分割槽可以指定不同的壓縮方式。

1 create table part_objects(
2 object_id number(10),
3 object_name varchar2(120)
4 )
5 partition by range(object_id)(
6 partition p1 values less than (100000) compress basic,
7 partition p2 values less than (200000) compress for oltp,
8 partition p3 values less than (maxvalue) nocompress
9* )
SQL> /
Table created.

SQL> select table_name,partition_name,compression,compress_for
2   from user_tab_partitions
3   where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          BASIC
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         DISABLED

你不僅可以為不同的分割槽指定不同的壓縮方式，你還可以在分割槽表建立以後改變

這些壓縮方式。

1、改變分割槽對將來插入的資料的壓縮方式。

SQL>alter table part_objects modify partition p1 compress for oltp;
Table altered.
SQL>select table_name,partition_name,compression,compress_for
2    from user_tab_partitions
3    where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          OLTP
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         DISABLED

2、改變分割槽已經存在和將來插入的資料的壓縮方式。

SQL>alter table part_objects move partition p3 compress basic;
Table altered.
SQL>select table_name,partition_name,compression,compress_for
2   from user_tab_partitions
3   where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          OLTP
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         ENABLED          BASIC

如果一個表是非壓縮的，或者想改變一個表的壓縮方式可以透過以下的方式來改變。

SQL> select table_name,compression,compress_for
2 from user_tables
3 where table_name = 'EMPLOYEES';
TABLE_NAME COMPRESSION COMPRESS_FOR
------------ ---------------- ------------------------
EMPLOYEES DISABLED

SQL>alter table employees move compress basic;
Table altered.
SQL>select table_name,compression,compress_for
2   from user_tables
3   where table_name = 'EMPLOYEES';
TABLE_NAME   COMPRESSION      COMPRESS_FOR
------------ ---------------- ------------------------
EMPLOYEES    ENABLED          BASIC

壓縮表上面存在的限制

1、使用basic compression 方式壓縮的表不能刪除列。

SQL> alter table employees drop column salary;
alter table employees drop column salary
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

2、oltp 方式壓縮的表，支援刪除表中的列，但是oracle內部是把刪除的列用unused方式來處理的，從而避免大量的解壓縮與壓縮操作，不過這導致了不能釋放該列佔有的儲存空間。

SQL> alter table employees move compress for oltp;
Table altered.
SQL> alter table employees drop column salary;
Table altered.

SQL> select * from user_unused_col_tabs
2 where table_name = 'EMPLOYEES';
TABLE_NAME COUNT
------------ ----------
EMPLOYEES 1

3、如果你不指定使用basic compression 方式壓縮的表的pct_free 引數將會被自動的設定為0。

4、壓縮理論上將會增加CPU的負載，但是實際的情況可能不是這樣的，因為CPU處理的I/O 操作減少了。如果I/O 操作不能抵消CPU負載的提高，確保你的系統中有足夠CPU資源。

5、壓縮表不能超過255個列。

6、壓縮表不支援線上段壓縮。

7、可以往basic compression 方式的壓縮表中新增列，但是不能指定預設值。

SQL> alter table employees move compress basic;
Table altered.
SQL> alter table employees add (salary number(7) default 0);
alter table employees add (salary number(7) default 0)
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL> alter table employees add (salary number(7));
Table altered.

8、可以往oltp compression 方式的壓縮表中新增列，如果指定預設值，該列必須是not null 約束的。

SQL> alter table employees add (income number(7) default 0);
alter table employees add (income number(7) default 0)
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table employees add (income number(7) default 0 not null);
Table altered.

9、這裡所說的表的壓縮方式，不適合與LOBs

oracle （11gR2）中的表壓縮

相關文章