Oracle 11G Table Compression

The latest set of production library table space has been alarm over 90%, but the disk hardware resources are not enough, the entire library has reached 26T. The library stores nearly 4 years of data, and business communication that historical data is basically not done, but can not be archived, so think of a compressed table to save table space.

As the database grows, we can consider using Oracle's table compression technology. Table compression saves disk space, reduces the memory usage of the data buffer cache, and can significantly improve read and query speed. When using compression, when data import and DML operations result in more CPU overhead, however, the cost of CPU overhead is offset by the reduced I/O requirements due to compression enabled. Table compression is completely transparent to the application, and the compression of tables is beneficial for decision support System (DSS), online transaction processing system (OLTP), and archiving system (Archive systems). We can compress table spaces, tables, and partitions. If you compress a table space, all tables created on the tablespace will be compressed by default. Compression of data is performed only when the table performs an INSERT, update, or bulk data load.

Table Compression Methods

basic compression

none.

warehouse compression (Hybrid columnar compression)

the compression level and CPU overhead depend on compression level Specified (Low or high).

TD class= "cellalignment1335" style= "margin:0px;" > TD class= "cellalignment1335" style= "margin:0px;" >

table Compression Method	compression level	cpu Overhead	applications	notes
high	minimal	dss
OLTP compression	high	Minimal	OLTP, DSS	none.
higher	higher	dss
Archive compression (Hybrid columnar compression)	highest	Highest	Archiving	The compression level and CPU overhead depend on compression level specified.

When you use the basic Compression,warehouse compression,archive Compression type of compression, compression is performed when a bulk data import occurs. OLTP compression is used for online transaction processing systems and can perform data compression on arbitrary SQL operations. Warehouse compression and archive compression can obtain very high compression levels because they employ Hybrid columnar (mixed column) compression technology, Hybrid Columnar replaces one row-based storage form with an improved storage format for columns. Hybird Columnar technology allows the same data to be stored together, improving the efficiency of the compression algorithm. When using the hybrid column compression algorithm, there is more CPU overhead, so this compression technique is suitable for infrequently updated data.

Table Compression Characteristics

Table Compression Method	create/alter TABLE Syntax	Direct-path INSERT	Notes
Basic compression	COMPRESS [BASIC]	Rows is compressed with basic compression.	compress  and compress  basic  are equivalent. rows inserted without using Direct-path Insert and updated Rows are Uncompressed.
OLTP compression	compress for OLTP	rows is compressed with OLTP compression.	rows inserted Without using Direct-path Insert and updated rows are compressed using OLTP compression.
Warehouse compression (Hybrid columnar compression)	COMPRESS FOR QUERY [LOW|HIGH]	Rows is compressed with warehouse compression.	This compression method can result in a high CPU overhead. Updated rows and rows inserted without using Direct-path Insert is stored in row format instead of column format, and Thu S has a lower compression level.
Archive compression (Hybrid columnar compression)	COMPRESS FOR ARCHIVE [LOW|HIGH]	Rows is compressed with archive compression.	This compression method can result in a high CPU overhead. Updated rows and rows inserted without using Direct-path Insert is stored in row format instead of column format, and Thu S has a lower compression level.

Test:

Oracle version 11.2.0.4

1. Create a compressed table

[Email protected]>create table t_basic (ID number,name varchar2 ()) compress; Table created. [Email protected]>create table T_OLTP (ID number,name varchar2 (TEN)) Compress for OLTP; Table created. [Email protected]>select table_name,compression,compress_for from User_tables where table_name in (' T_BASIC ', ' T_ OLTP '); table_name COMPRESS compress_for--------------------------------------------------t_basic Enabledbasict_olt P ENABLEDOLTP

2. Uncompressed table and compression table conversion

2.1 ALTER TABLE ... compress/nocompress

[email protected]>select table_name,compression,compress_for from user_tables  where table_name = ' T '; Table_name       compress compress_for------------------------------  -------- ------------t       disabled[email protected]> alter table t compress; Table altered. [email protected]>select table_name,compression,compress_for from user_tables  where table_name = ' T '; Table_name       compress compress_for------------------------------  -------- ------------t       enabledbasic[email protected] >alter table t nocompress; Table altered. [email protected]>select table_name,compression,compress_for from user_tables  where table_name = ' T '; Table_name   &nbsP;   compress compress_for------------------------------ -------- ------------T        disabled

Previously uncompressed tables can be passed by ALTER TABLE ... compress ... Statement to compress. In this case, the record before compression is not compressed and only the newly inserted or updated data is compressed. Similarly, by ALTER TABLE ... nocompres ... Statement to suppress a table, the compressed data in the table will continue to remain compressed, and the newly inserted data will no longer be compressed.

2.2 ALTER TABLE ... move compress/nocompress

[Email protected]>select bytes/1024/1024 from user_segments where segment_ Name= ' T '; bytes/1024/1024---------------    304[email protected]>select table_name, compression,compress_for from user_tables where table_name = ' T '; Table_name       compress compress_for------------------------------  -------- ------------t       disabled[email protected]> alter table t move compress ; Table altered. [email protected]>select table_name,compression,compress_for from user_tables  where table_name = ' T '; Table_name       compress compress_for------------------------------  -------- ------------t       enabledbasic[email protected] >select bytes/1024/1024 from&nbSp;user_segments where segment_name= ' T '; bytes/1024/1024---------------     72[email protected]>alter table  t move nocompress; Table altered. [email protected]>select table_name,compression,compress_for from user_tables  where table_name = ' T '; Table_name       compress compress_for------------------------------  -------- ------------t       disabled[email protected]> Select bytes/1024/1024 from user_segments where segment_name= ' T '; bytes/1024/1024---------------    272

3, Partition table compression

[email protected]>create table t_comp_part  (ID NUMBER,NAME VARCHAR2 (10))   2  partition by range (ID)   3   (partition p1  values less than  (,  4  partition p2 values less ) than  (+))    5  compress; Table created. [Email protected]>select table_name,partition_name,compression,compress_for from user_ tab_partitions where table_name =  ' T_comp_part '; Table_name       partition_name      compress  compress_for------------------------------ ------------------------------ -------- ------ ------t_comp_part       p1      enabled   basict_comp_part       p2     &nbSp enabled  basic--modifying the compression method of a partition [email protected]>alter table t_comp_part modify  partition p1 compress for oltp; Table altered. [Email protected]>select table_name,partition_name,compression,compress_for from user_ tab_partitions where table_name =  ' T_comp_part '; Table_name       partition_name      compress  compress_for------------------------------ ------------------------------ -------- ------ ------t_comp_part       p1      enabled   oltpt_comp_part       p2      enabled   basic

Uncompressed partitions to compressed partitions

A table can have compressed partitions and uncompressed partitions, and different partitions can use different compression methods. You can change the compression method of a partition by using the following methods:
1. ALTER TABLE ... modify partition ... compress ..., this method applies only to newly inserted data.

2. ALTER TABLE ... move partition ... compress., this method applies to newly inserted data and existing data.

If you want to convert the partition table to a compressed table, ALTER TABLE ... move compress ... will be error, only for each partition in the table to do ALTER TABLE ... move partition ... compress.

The corresponding index is invalidated after the table is compacted and needs to be rebuilt.

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630

Reference Document: http://blog.itpub.net/29515435/viewspace-1128770/

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1871093

Oracle 11G Table Compression