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

This article is about how to achieve table compression in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Alter table table_name COMPRESS;-- No matter how big the watch is, this sentence is executed very quickly.

Alter table table_name COMPRESS FOR OLTP;--, no matter how big the watch is, you can execute this sentence very quickly.

The execution of alter table table_name move COMPRESS FOR OLTP;-- is very slow when it is big.

The execution of alter table table_name move NOCOMPRESS;-- big table is very slow.

The execution of alter table table_name move COMPRESS;-- big table is very slow.

You can enable compression for an existing table by using these clauses in an ALTER TABLE statement. In this case, only data that is inserted or updated after compression is enabled is compressed

You can enable compression of existing tables by using these clauses in ALTER TABLE statements. In this case, only data inserted or updated after compression is enabled is compressed

The above should refer to operations such as ALTER TABLE TABLENAME COMPRESS [BASIC | FOR OLTP | FOR QUERY | FOR ARCHIVE], after which the updated data is compressed. But we can use ALTER TABLE TABLENAME MOVE COMPRESS [BASIC | FOR OLTP | FOR QUERY | FOR ARCHIVE] to compress both existing and later updated data from an existing table, which can take a long time to execute if it is a large table.

Determining If a Table Is Compressed

In the * _ TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the * _ TAB_PARTITIONS views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.

Determine if the table is compressed

In the * _ TABLES data dictionary view, the compressed table is enabled in the COMPRESSION column. For partitioned tables, this column is empty and the COMPRESSION column of the * _ TAB_PARTITIONS view represents the compressed partition. In addition, the COMPRESS_FOR column represents the compression method used for tables or partitions.

COMPRESS [BASIC]

Rows inserted without using direct-path insert and updated rows are uncompressed.

Rows inserted without direct path insertion and update are uncompressed.

COMPRESS FOR OLTP

Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.

Rows that are inserted without direct path insertion and update will be compressed using OLTP compression.

COMPRESS FOR QUERY [LOW | HIGH]

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.

Updated rows and rows that are inserted without direct path insertion are stored in row format instead of column format, so they have a low level of compression.

COMPRESS FOR ARCHIVE [LOW | HIGH]

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.

Updated rows and rows that are inserted without direct path insertion are stored in row format instead of column format, so they have a low level of compression.

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I/O requirements.

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

Compression can occur while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

Single-row or array inserts and updates

The following direct-path INSERT methods:

Direct path SQL*Loader

CREATE TABLE AS SELECT statements

Parallel INSERT statements

INSERT statements with an APPEND or APPEND_VALUES hint

As the size of the database increases, consider using table compression. Compression can save disk space reduce memory usage in the database buffer cache and significantly improve query execution speed during reading. Compression has a cost in terms of data loading and CPU overhead of DML. However, this cost may be offset by reduced I / O requirements.

Table compression is completely transparent to the application. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems and archiving systems.

You can specify compression for table spaces, tables, or partitions. If specified at the tablespace level, all tables created in that tablespace are compressed by default.

Compression may occur when data is inserted, updated, or bulk loaded into a table. Operations that allow compression include:

Insert and update a single row or array

The following direct path INSERT method:

Direct path SQL * Loader

CREATE TABLE as a SELECT statement

Parallel INSERT statement

INSERT statements with APPEND or APPEND_VALUES prompts

When you use basic compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.

When you use OLTP compression, compression occurs while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

Single-row or array inserts and updates

Inserts and updates are not compressed immediately. When updating an already compressed block, any columns that are not updated usually remain compressed. Updated columns are stored in an uncompressed format similar to any uncompressed block. The updated values are re-compressed when the block reaches a database-controlled threshold. Inserted data is also compressed when the data in the block reaches a database-controlled threshold.

The following direct-path INSERT methods:

Direct path SQL*Loader

CREATE TABLE AS SELECT statements

Parallel INSERT statements

INSERT statements with an APPEND or APPEND_VALUES hint

When you use basic compression, warehouse compression, or archive compression, compression occurs only when the data is bulk loaded into the table.

When you use OLTP compression, it is compressed when data is inserted, updated, or bulk loaded into a table. Operations that allow compression include:

Insert and update a single row or array

Inserts and updates are not immediately compressed. When you update a compressed block, any columns that are not updated usually remain compressed. Updated columns are stored in an uncompressed format similar to any uncompressed block. When the block reaches the threshold controlled by the database, the updated value is recompressed. When the data in the block reaches the threshold controlled by the database, the inserted data is also compressed.

The following direct path INSERT method:

Direct path SQL * Loader

CREATE TABLE as a SELECT statement

Parallel INSERT statement

INSERT statements with APPEND or APPEND_VALUES prompts

Basic compression compresses data inserted by direct path load only and supports limited data types and SQL operations. OLTP compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

Basic compression only loads inserted data through a direct path and supports limited data types and SQL operations. OLTP compression is suitable for OLTP applications and compresses data for any SQL operation.

This example demonstrates using the APPEND hint to insert rows into the sales_history table using direct-path INSERT.

INSERT / * + APPEND * / INTO sales_history SELECT * FROM sales WHERE cust_id=8890

Warehouse compression and archive compression achieve the highest compression levels because they use Hybrid Columnar Compression technology. Hybrid Columnar Compression technology uses a modified form of columnar storage instead of row-major storage. This enables the database to store similar data together, which improves the effectiveness of compression algorithms. For data that is updated, Hybrid Columnar Compression uses more CPU and moves the updated rows to row format so that future updates are faster. Because of this optimization, you should use it only for data that is updated infrequently.

Warehouse compression and archive compression achieve the highest level of compression because they use Hybrid Columnar Compression technology. Mixed column compression technology uses modified form of cylindrical storage instead of row primary storage. This enables the database to store similar data together, which improves the effectiveness of the compression algorithm. For updated data, mixed column compression uses more CPU and moves the updated rows to the row format so that future updates are faster. Because of this optimization, you should use it only for data that is not updated frequently.

The higher compression levels of Hybrid Columnar Compression are achieved only with data that is direct-path inserted. Conventional inserts and updates are supported, but cause rows to be moved from columnar to row format, and reduce the compression level.

The higher compression level of mixed column compression is achieved only by inserting data from the direct path. Regular inserts and updates are supported, but rows are moved from column format to row format and the compression level is reduced.

Regardless of the compression method, DELETE operations on a compressed block are identical to DELETE operations on a non-compressed block. Any space obtained on a data block, caused by SQL DELETE operations, is reused by subsequent SQL INSERT operations. With Hybrid Columnar Compression technology, when all the rows in a compression unit are deleted, the space in the compression unit is available for reuse.

Regardless of the compression method, the DELETE operation on the compressed block is the same as the DELETE operation on the uncompressed block. Any space acquired on the data block caused by the SQL DELETE operation will be reused by subsequent SQL INSERT operations. Using the mixed column compression technique, the space in the compression unit can be used for reuse when all rows in the compression unit are deleted.

The following are notes and restrictions related to compressed tables:

Online segment shrink is not supported for compressed tables.

The table compression methods described in this section do not apply to SecureFiles large objects (LOBs). SecureFiles LOBs have their own compression methods. See Oracle Database SecureFiles and Large Objects Developer's Guide for more information.

Compression technology uses CPU. Ensure that you have enough available CPU to handle the additional load.

Tables created with basic compression have the PCT_FREE parameter automatically set to 0 unless you specify otherwise.

The following are comments and restrictions related to compressed tables:

Compression tables do not support online shrinking.

The table compression method described in this section does not apply to SecureFiles large objects (LOB). SecureFiles LOB has its own compression method. For more information, see the Oracle Database SecureFiles and Large Objects developer's Guide.

Compression technology uses CPU. Make sure you have enough CPU available to handle the extra load.

Tables created with basic compression have the PCT_FREE parameter automatically set to 0 unless otherwise noted.

Move_table_clause

The move_table_clause lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

You can also move any LOB data segments associated with the table or partition using the LOB_storage_clause and varray_col_properties clause. LOB items not specified in this clause are not moved.

If you move the table to a different tablespace and the COMPATIBLE parameter is set to 10.0 or higher, then Oracle Database leaves the storage table of any nested table columns in the tablespace in which it was created. If COMPATIBLE is set to any value less than 10.0, then the database silently moves the storage table to the new tablespace along with the table.

Move_table_clause allows you to relocate data from a non-partitioned or partitioned table to a new segment (optionally in a different tablespace) and optionally modify any of its storage properties.

You can also use the LOB_storage_clause and varray_col_ properties clauses to move any LOB segments associated with a table or partition. LOB items that are not specified in this section are not moved.

If you move the table to a different table space and the COMPATIBLE parameter is set to 10.0 or later, the Oracle database will leave storage tables for any nested table columns in the table space it creates. If COMPATIBLE is set to any value less than 10.0, the database silently moves the storage table to the new tablespace along with the table.

Thank you for reading! This is the end of the article on "how to achieve table compression in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it out for more people to see!

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.