Shulou(Shulou.com)06/01 Report--

Recently, the alarm of a set of production database table space has been more than 90%, but the disk hardware resources are insufficient, and the whole library has reached 26T. The database has stored nearly 4 years of data, communicate with the business that the historical data basically can not do the operation, but can not be archived, so I thought of compressing the table to save table space.

As the database grows, we can consider using oracle's table compression technology. Table compression can save disk space, reduce data buffer cache memory usage, and significantly improve the speed of reads and queries. When compression is used, there will be more CPU overhead in data import and DML operations, however, the reduced Imax O requirements due to enabling compression will offset the cost of CPU. Table compression is completely transparent to applications, and is beneficial to decision support systems (DSS), online transaction processing systems (OLTP), and archiving systems (Archive Systems). We can compress tablespaces, tables and partitions. If the tablespace is compressed, all tables created on the tablespace will be compressed by default. Data compression is performed only when the table performs an insert, update, or bulk data load.

Table Compression Methods

Table Compression MethodCompression LevelCPU OverheadApplicationsNotes

Basic compression

High

Minimal

DSS

None.

OLTP compression

High

Minimal

OLTP, DSS

None.

Warehouse compression (Hybrid Columnar Compression)

Higher

Higher

DSS

The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

Archive compression (Hybrid Columnar Compression)

Highest

Highest

Archiving

The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

When Basic Compression,warehouse Compression,Archive Compression type compression is used, compression is performed only when bulk data import occurs. OLTP Compression is used in online transaction processing systems to perform data compression for arbitrary SQL operations. Warehouse Compression and Archive Compression can achieve high compression levels because they use Hybrid Columnar (mixed column) compression technology, and Hybrid Columnar uses an improved column storage form instead of row-based storage. Hybird Columnar technology allows the same data to be stored together, which improves the efficiency of the compression algorithm. When using the hybrid column compression algorithm, it will result in more CPU overhead, so this compression technique is suitable for data that is updated infrequently.

Table Compression Characteristics

Table Compression MethodCREATE/ALTER TABLE SyntaxDirect-Path INSERTNotes

Basic compression

COMPRESS [BASIC]

Rows are 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 are 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 are compressed with warehouse compression.

This compression method can result in high CPU overhead.

Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

Archive compression (Hybrid Columnar Compression)

COMPRESS FOR ARCHIVE [LOW | HIGH]

Rows are compressed with archive compression.

This compression method can result in high CPU overhead.

Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

Test:

Oracle version 11.2.0.4

1. Create a compression table

Zx@ORCL > create table t_basic (id number,name varchar2 (10)) compress;Table created.zx@ORCL > create table t_oltp (id number,name varchar2 (10)) compress for oltp;Table created.zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name in TABLE_NAME COMPRESS COMPRESS_FOR---T_BASIC ENABLED BASICT_OLTP ENABLED OLTP

2. Conversion between uncompressed table and compressed table

2.1 alter table... Compress/nocompress

Zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name = 'TABLEX' name COMPRESS COMPRESS_FOR-- T DISABLEDzx@ORCL > alter table t compress Table altered.zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name = 'TABLEX' name COMPRESS COMPRESS_FOR-- T ENABLED BASICzx@ORCL > alter table t nocompress Table altered.zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name = 'Tableberry' name COMPRESS COMPRESS_FOR-- T DISABLED

Previously uncompressed tables can be accessed through alter table. Compress... Statement is compressed. In this case, records before compression is enabled will not be compressed, and only newly inserted or updated data will be compressed. Again, through alter table... Nocompres... Statement uncompresses a table, the compressed data in the table continues to be compressed, and the newly inserted data is no longer compressed.

2.2 alter table... Move compress/nocompress

Zx@ORCL > select bytes/1024/1024 from user_segments where segment_name='T';BYTES/1024/1024- 304zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T' TABLE_NAME COMPRESS COMPRESS_FOR-- T DISABLEDzx@ORCL > alter table t move compress; Table altered.zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T' TABLE_NAME COMPRESS COMPRESS_FOR-- T ENABLED BASICzx@ORCL > select bytes/1024/1024 from user_segments where segment_name='T' BYTES/1024/1024- 72zx@ORCL > alter table t move nocompress;Table altered.zx@ORCL > select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T' TABLE_NAME COMPRESS COMPRESS_FOR---T DISABLEDzx@ORCL > select bytes/1024/1024 from user_segments where segment_name='T';BYTES/1024/1024- 272

3. Compression of partition table

Zx@ORCL > 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.zx@ORCL > select table_name,PARTITION_NAME,compression,COMPRESS_FOR from user_tab_partitions where table_name = 'Tunable Compact part' TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR-- T_COMP_PART P1 ENABLED BASICT_COMP_PART P2 ENABLED BASIC-- modifies partition compression zx@ORCL > alter table t_comp_part modify partition p1 compress for oltp Table altered.zx@ORCL > select table_name,PARTITION_NAME,compression,COMPRESS_FOR from user_tab_partitions where table_name = 'Tunable Compact part' TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR-- T_COMP_PART P1 ENABLED OLTPT_COMP_PART P2 ENABLED BASIC

Convert uncompressed partitions to compressed partitions

A table can have compressed and uncompressed partitions, and different partitions can use different compression methods. You can change the compression method of a partition in the following ways:

1 、 alter table... Modify partition... Compress..., this method applies only to newly inserted data.

2 、 alter table... Move partition... Compress., this method is suitable for newly inserted data and existing data.

If you want to convert a partitioned table to a compressed table, directly alter table. Move compress... Will report an error, can only do alter table for each partition in the table. Move partition... Compress... .

After the table is compressed, the corresponding index becomes invalid 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/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.