Oracle table Compression

Preface:

Table Compression should be supported from 10 Gb. Oracle 11g exadata used this feature to the extreme and vigorously promoted the compression technology. In the condition that the cpu performance of modern machines is blowout, the reduction of IO and memory usage is particularly important. compression technology will certainly be a common technology in the future. Now, this technology is becoming more and more mature, so that we can use this function in the future maintenance process and realize the popularization of this function as soon as possible, with the benefit of DBA.

Oracle 12c also supports the compression technology. The following describes the advantages and disadvantages of various compression methods. syntax and introduction:

BASIC table compression level high cpu consumption less suitable for DSS syntax row store compress [BASIC] without using direct-path insertion and updating data will not be compressed
ADVANCED compression level high cpu consumption less suitable for oltp dss syntax row store compress advanced same as above
Data Warehouse compression (mixed COLUMN compression) Compression level higher cpu consumption HIGH suitable FOR dss syntax column store compress for query [LOW | HIGH] HIGH cpu, without using direct-path FOR Row-level storage, the compression level is reduced instead of the column format.
ARCHIVE compression (mixed COLUMN compression) the maximum compression level cpu consumption is the most suitable for archive syntax column store compress for archive [LOW | HIGH] Same as above

 

Advantages and disadvantages of various compression technologies:

Basic table compression is suitable for SQL statements of direct path insertion and restricted data types.
Advanced row compression supports all data types and all SQL statements

Hybrid column compression is suitable for infrequent update

Archive compression is only applicable to direct path inserted. Traditional insertion and update are also supported. However, you must use the ADO (automatic data optimization) policy to move rows to the required hybrid column compression level.

Conclusion: We can find that advanced row compression is the ideal compression method, and other levels are cold-door compression, which can be used as needed.

Test it.
My environment: oracle 12c for linux enterprise 5 + file system -- single instance

 

SQL> create table tt (id number) row store compress basic; -- Succeeded
SQL> create table tt (id number) row store compress advanced; -- Succeeded
SQL> create table tt (id number) column store compress for query high; -- an error is reported, which is not supported by common storage and is only applicable to exadata.

Create table tt (id number) column store compress for query high
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported
Tablespaces on this storage type
SQL> create table tt (id number) column store compress for archive high; -- the same error is reported.

SQL> create table tt (id number );

Table created.

SQL> alter table tt row store compress advanced; -- the compression level can be specified later in the table.

Table altered.

SQL>

 

Conclusion: it is a pity that the data warehouse-level compression and archive compression are not supported on the common database. We are again limited.

 

 

Alter table modifies the table to be compressed, which only affects the insert and update operations behind the table. Previous data is not operated.
Alter table move compresses all data.
Alter table... nocompress; -- Cancel table compression, but the compressed data will not be decompressed by itself, and the new data will use Uncompressed

Column store compress for query high is the default warehouse-level compression, providing good performance and compression level, but the hybrid column can only be on exadata storage
Query low is suitable for systems with poor read performance. It is faster than query high.

Column store compress for archive low is the default archive compression mode, which provides high-level compression and ideal infrequent Data Access. archive high is suitable for and less data access.

The DBMS_COMPRESSION package helps you perform special compression for important tables to achieve the desired compression level.

Create table sales_history... row store compress; -- this statement uses the basic table compression by default.

 

Perform a manual test without a database machine. Test the basic compress and row advanced compress to see the results.
SQL> select segment_name, bytes/1024/1024 from user_segments;

SEGMENT_NAME BYTES/1024/1024
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------
TEST. 0625.
AA, 192

SQL>
SQL>
SQL> create table aa_bas row store compress basic as select * from aa;

Table created.

SQL> create table aa_adv row store compress advanced as select * from aa;

Table created.

SQL> select segment_name, bytes/1024/1024 from user_segments;

SEGMENT_NAME BYTES/1024/1024
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------
TEST. 0625.
AA, 192
AA_BAS 47
AA_ADV 53

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store