數據庫中如何實現表壓縮

這篇文章給大家分享的是有關數據庫中如何實現表壓縮的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

alter table table_name COMPRESS; --不管多大的表執行這句都很快
alter table table_name COMPRESS FOR OLTP;--不管多大的表執行這句都很快
alter table table_name move COMPRESS FOR OLTP;--大表時執行很慢
alter table table_name move NOCOMPRESS;--大表執行很慢
alter table table_name move COMPRESS;--大表執行很慢

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
您可以通過在ALTER TABLE語句中使用這些子句來啟用現有表的壓縮。 在這種情況下，只有在啟用壓縮后插入或更新的數據才被壓縮
以上應該指ALTER TABLE TABLENAME COMPRESS [BASIC|FOR OLTP|FOR QUERY|FOR ARCHIVE]這樣的操作，這些操作之后更新的數據才被壓縮。但是咱們可以使用ALTER TABLE TABLENAME MOVE COMPRESS [BASIC|FOR OLTP|FOR QUERY|FOR ARCHIVE]來對現有表的現有數據和以后更新的數據都進行壓縮，如果是大表的話執行會耗時很長。

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.
確定表是否被壓縮
在* _TABLES數據字典視圖中，壓縮表在COMPRESSION列中已啟用。 對于分區表，此列為空，并且* _TAB_PARTITIONS視圖的COMPRESSION列表示被壓縮的分區。 此外，COMPRESS_FOR列表示用于表或分區的壓縮方法。



COMPRESS [BASIC]
Rows inserted without using direct-path insert and updated rows are uncompressed.
不使用直接路徑插入和更新的行插入的行是未壓縮的。

COMPRESS FOR OLTP
Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.
不使用直接路徑插入和更新行插入的行將使用OLTP壓縮進行壓縮。

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.
不使用直接路徑插入插入的更新的行和行以行格式而不是列格式存儲，因此具有較低的壓縮級別。

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.
不使用直接路徑插入插入的更新的行和行以行格式而不是列格式存儲，因此具有較低的壓縮級別。



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
隨著數據庫的大小增加，請考慮使用表壓縮。 壓縮可節省磁盤空間，減少數據庫緩沖區高速緩存中的內存使用，并可以顯著提高讀取期間的查詢執行速度。 壓縮在數據加載和DML的CPU開銷方面具有成本。 然而，這種成本可能被減少的I / O要求所抵消。
表壓縮對于應用程序是完全透明的。 它在決策支持系統（DSS），在線交易處理（OLTP）系統和歸檔系統中很有用。
您可以為表空間，表或分區指定壓縮。 如果在表空間級別指定，那么在該表空間中創建的所有表都默認是壓縮的。
當數據被插入，更新或批量加載到表中時，可能會發生壓縮。 允許壓縮的操作包括：
單行或數組插入和更新
以下直接路徑INSERT方法：
直接路徑SQL * Loader
CREATE TABLE作為SELECT語句
并行INSERT語句
帶有APPEND或APPEND_VALUES提示的INSERT語句


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
當您使用基本壓縮，倉庫壓縮或歸檔壓縮時，僅當數據批量加載到表中時才會進行壓縮。
當您使用OLTP壓縮時，會在數據插入，更新或批量加載到表中時進行壓縮。 允許壓縮的操作包括：
單行或數組插入和更新
插入和更新不會立即壓縮。 更新已壓縮的塊時，任何未更新的列通常都保持壓縮。 更新的列以與任何未壓縮塊相似的未壓縮格式存儲。 當塊達到數據庫控制的閾值時，更新的值被重新壓縮。 當塊中的數據達到數據庫控制的閾值時，插入的數據也會被壓縮。
以下直接路徑INSERT方法：
直接路徑SQL * Loader
CREATE TABLE作為SELECT語句
并行INSERT語句
帶有APPEND或APPEND_VALUES提示的INSERT語句




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.
基本壓縮壓縮僅通過直接路徑加載插入的數據，并支持有限的數據類型和SQL操作。 OLTP壓縮適用于OLTP應用程序，并壓縮任何SQL操作的數據。
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.
倉庫壓縮和歸檔壓縮實現了最高的壓縮級別，因為它們使用Hybrid Columnar Compression技術。 混合柱壓縮技術使用修改形式的柱狀存儲，而不是行主存儲。 這使得數據庫能夠將類似的數據存儲在一起，這提高了壓縮算法的有效性。 對于更新的數據，混合列壓縮使用更多的CPU，并將更新的行移動到行格式，以便將來的更新更快。 由于這種優化，您應該僅將其用于不經常更新的數據。

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.
混合柱壓縮的較高壓縮級別僅通過插入直接路徑的數據實現。 支持常規的插入和更新，但是會使行從列格式移動到行格式，并降低壓縮級別。

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.
無論壓縮方法如何，壓縮塊上的DELETE操作與非壓縮塊上的DELETE操作相同。 由SQL DELETE操作引起的數據塊上獲取的任何空間都將被后續的SQL INSERT操作重用。 使用混合柱壓縮技術，當壓縮單元中的所有行都被刪除時，壓縮單元中的空間可用于重用。

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.
以下是與壓縮表相關的注釋和限制：
壓縮表不支持線上縮小。
本節中描述的表壓縮方法不適用于SecureFiles大對象（LOB）。 SecureFiles LOB有自己的壓縮方法。 有關詳細信息，請參閱Oracle Database SecureFiles和Large Objects開發人員指南。
壓縮技術使用CPU。 確保您有足夠的可用CPU來處理額外的負載。
使用基本壓縮創建的表具有PCT_FREE參數自動設置為0，除非另有說明。



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允許您將非分區表或分區表的分區的數據重定位到新段（可選地在不同的表空間中），并可選地修改其任何存儲屬性。
您還可以使用LOB_storage_clause和varray_col_properties子句移動與表或分區關聯的任何LOB數據段。 本節中未指定的LOB項目不會移動。
如果將表移動到不同的表空間，并且COMPATIBLE參數設置為10.0或更高版本，那么Oracle數據庫將在其創建的表空間中留下任何嵌套表列的存儲表。 如果COMPATIBLE設置為小于10.0的任何值，則數據庫將靜默地將存儲表與表一起移動到新的表空間。

感謝各位的閱讀！關于“數據庫中如何實現表壓縮”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！