數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)表壓縮

這篇文章給大家分享的是有關(guān)數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)表壓縮的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

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

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
您可以通過(guò)在ALTER TABLE語(yǔ)句中使用這些子句來(lái)啟用現(xiàn)有表的壓縮。 在這種情況下，只有在啟用壓縮后插入或更新的數(shù)據(jù)才被壓縮
以上應(yīng)該指ALTER TABLE TABLENAME COMPRESS [BASIC|FOR OLTP|FOR QUERY|FOR ARCHIVE]這樣的操作，這些操作之后更新的數(shù)據(jù)才被壓縮。但是咱們可以使用ALTER TABLE TABLENAME MOVE COMPRESS [BASIC|FOR OLTP|FOR QUERY|FOR ARCHIVE]來(lái)對(duì)現(xiàn)有表的現(xiàn)有數(shù)據(jù)和以后更新的數(shù)據(jù)都進(jìn)行壓縮，如果是大表的話執(zhí)行會(huì)耗時(shí)很長(zhǎng)。

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數(shù)據(jù)字典視圖中，壓縮表在COMPRESSION列中已啟用。 對(duì)于分區(qū)表，此列為空，并且* _TAB_PARTITIONS視圖的COMPRESSION列表示被壓縮的分區(qū)。 此外，COMPRESS_FOR列表示用于表或分區(qū)的壓縮方法。



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壓縮進(jìn)行壓縮。

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

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



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


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




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.
基本壓縮壓縮僅通過(guò)直接路徑加載插入的數(shù)據(jù)，并支持有限的數(shù)據(jù)類型和SQL操作。 OLTP壓縮適用于OLTP應(yīng)用程序，并壓縮任何SQL操作的數(shù)據(jù)。
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.
倉(cāng)庫(kù)壓縮和歸檔壓縮實(shí)現(xiàn)了最高的壓縮級(jí)別，因?yàn)樗鼈兪褂肏ybrid Columnar Compression技術(shù)。 混合柱壓縮技術(shù)使用修改形式的柱狀存儲(chǔ)，而不是行主存儲(chǔ)。 這使得數(shù)據(jù)庫(kù)能夠?qū)㈩愃频臄?shù)據(jù)存儲(chǔ)在一起，這提高了壓縮算法的有效性。 對(duì)于更新的數(shù)據(jù)，混合列壓縮使用更多的CPU，并將更新的行移動(dòng)到行格式，以便將來(lái)的更新更快。 由于這種優(yōu)化，您應(yīng)該僅將其用于不經(jīng)常更新的數(shù)據(jù)。

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.
混合柱壓縮的較高壓縮級(jí)別僅通過(guò)插入直接路徑的數(shù)據(jù)實(shí)現(xiàn)。 支持常規(guī)的插入和更新，但是會(huì)使行從列格式移動(dòng)到行格式，并降低壓縮級(jí)別。

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

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.
以下是與壓縮表相關(guān)的注釋和限制：
壓縮表不支持線上縮小。
本節(jié)中描述的表壓縮方法不適用于SecureFiles大對(duì)象（LOB）。 SecureFiles LOB有自己的壓縮方法。 有關(guān)詳細(xì)信息，請(qǐng)參閱Oracle Database SecureFiles和Large Objects開(kāi)發(fā)人員指南。
壓縮技術(shù)使用CPU。 確保您有足夠的可用CPU來(lái)處理額外的負(fù)載。
使用基本壓縮創(chuàng)建的表具有PCT_FREE參數(shù)自動(dòng)設(shè)置為0，除非另有說(shuō)明。



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允許您將非分區(qū)表或分區(qū)表的分區(qū)的數(shù)據(jù)重定位到新段（可選地在不同的表空間中），并可選地修改其任何存儲(chǔ)屬性。
您還可以使用LOB_storage_clause和varray_col_properties子句移動(dòng)與表或分區(qū)關(guān)聯(lián)的任何LOB數(shù)據(jù)段。 本節(jié)中未指定的LOB項(xiàng)目不會(huì)移動(dòng)。
如果將表移動(dòng)到不同的表空間，并且COMPATIBLE參數(shù)設(shè)置為10.0或更高版本，那么Oracle數(shù)據(jù)庫(kù)將在其創(chuàng)建的表空間中留下任何嵌套表列的存儲(chǔ)表。 如果COMPATIBLE設(shè)置為小于10.0的任何值，則數(shù)據(jù)庫(kù)將靜默地將存儲(chǔ)表與表一起移動(dòng)到新的表空間。

感謝各位的閱讀！關(guān)于“數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)表壓縮”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！