Shulou(Shulou.com)05/31 Report--

This article shows you what the compression of the Oracle table is like, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

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.

I. the method of table compression

Oracle provides the following types of table compression methods:

1. Basic Compression: compression level High,CPU has less overhead

2. OLTP Compression: compression level High,CPU has less overhead

3. Warehouse Compression: compression level Higher,CPU is expensive (depending on whether the compression parameter is Low or High)

4. Archive Compression: compression level Highest,CPU is very expensive (depending on whether the compression parameter is 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.

Compression characteristics of the table:

Table compression method creates and modifies the syntax of table compression direct path insertion indicates that data inserted directly by Basic Compressioncompression [basic] will only be compressed compression and compression basic are equivalent, data not inserted by direct path will not be compressed OLTP Compressioncompression for oltp does not require data using Insert and Update will also be compressed Warehouser Compressioncompression for query [Low | High] data inserted directly by path will be compressed

CPU is expensive. Row inserts and row updates without direct path insertion are stored in row format, which reduces the compression level (Low) Archive Compressioncompression for archive [Low | High]

Data inserted directly into the path will be compressed.

CPU is expensive, and row inserts and row updates without direct path insertion are stored in row format, which reduces the compression level (Low)

Previously uncompressed tables can be accessed through alter table. Compression... 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... Nocompression... Statement uncompresses a table, the compressed data in the table continues to be compressed, and the newly inserted data is no longer compressed.

In warehouse compression mode, compression for query high is the default; in archive compression mode, compression for archive low is default.

II. Compression and partitioning tables

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.

Determine whether the table is compressed:

SQL > select table_name,compression,compress_for from user_tables

TABLE_NAME COMPRESS COMPRESS_FOR

COUNTRIES DISABLED BASIC / / it's a bit confusing here, since compression is not enabled in the table, why is the compression method in compress_for really basic?

JOBS DISABLED

EMPLOYEES ENABLED BASIC

LOCATIONS DISABLED

DEPARTMENTS DISABLED

JOB_HISTORY DISABLED

REGIONS DISABLED

Add and delete columns on the compressed table

Adding and deleting columns on a compressed table has the following restrictions:

1. For compression basic type compression, you cannot specify default values for newly added columns. For example:

SQL > alter table employees compress basic

Table altered.

SQL > alter table employees add (a varchar (10) default 'AAA')

Alter table employees add (a varchar (10) default 'AAA')

*

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

SQL > alter table employees add (b number)

Table altered.

2. For OLTP type compression, if you specify a default value for the newly added column, the column must be set to NOT NULL, and adding default values for columns that can be empty is not supported. For this point, I think the description of the document is wrong, the original text is as follows: OLTP compression-If a default value is specified for an added column, then the column must be NOT NULL. Added nullable columns with default values are not supported. The following is verified by an experiment:

SQL > alter table employees compress for oltp

Table altered.

SQL > alter table employees add (c int default 10)

Table altered.

3. Column deletion is not supported for compression basic compression, for example:

SQL > alter table employees compress basic

Table altered.

SQL > alter table employees drop column c

Alter table employees drop column c

*

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

4. For OLTP type compression, column deletion can be supported, but columns are marked as unused state within the database to avoid long decompression and recompression operations.

SQL > alter table employees compress for oltp

Table altered.

SQL > alter table employees drop column c

Table altered

The above is what the compression of Oracle table is like. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.