Oracle table Compression

Oracle's compressed data processing is based on database blocks. In essence, it saves space by eliminating duplicate data in database blocks. The specific method is as follows: Compare all fields or records contained in data blocks, duplicate data is only stored in the Symbol Table at the beginning of the data block. When the same data occurs in other rows or fields, only one pointer pointing to the relevant data in the mark table is recorded.

Create a compressed table:

Create table Name (

......

) Compress;

Alter table Name compress;

Alter table Name nocompress;

 

Materialized View compression:

Create materialized view ViewName compress

As select ......;

Alter materialized view ViewName compress;

 

Partition Table compression:

Create table Name (

......

) Compress

Partition ......;

Create table Name (

......

)

Partition ......(

Partition PartName... compress,

Partition PartName... compress,

Partition PartName ......

);

Define the compression attribute at the tablespace level:

Create tablespace... default compress;

Alter tablespace... compress/nocompress;

When the compression attribute is defined on the tablespace, this attribute is inherited by the table when the table is created, but the table-level compression attribute overwrites the compression attribute of the tablespace.

 

Check whether a table is a compressed table:

Select compression from user_table where table_name = TableName;

 

Check whether a tablespace is compressed:

Select def_tab_compression from dba_tablespace where tablespace_name = TablespaceName;

 

View the compression attributes of each partition in the Partition Table:

Select table_name, partition_name, compression from user_tab_partitions where table_name = TableName;

 

Table compression implementation:

To compress the data of a compressed table, you must correctly use batch loading or insertion:

1. Use direct path in SQL * LOADER to load data;

2. Execute the create table... as select statement;

3. Execute parallel insert statements;

4. Execute the serial insert statement and use the append prompt.

Alter table Name move compress/nocompress;

 

Performance analysis:

1. When data is loaded or inserted in batches, compression increases CPU usage and significantly increases loading time.

2. For general INSERT statements, since the compression process is not executed, the performance is almost unaffected.

3. Using the DELETE statement to DELETE a compressed table is faster, mainly because the compressed table has a small amount of compressed row data and a small amount of log data.

4. The update and compression operations are slow, mainly because ORACLE has optimized non-compressed tables.

5. When a system with limited IO throughput executes a large number of queries, such as full table scan, compressing a table will significantly increase the query speed, mainly because after compression, to search for the same data row, you only need to read fewer data blocks.