数据库中如何实现表压缩

这篇文章给大家分享的是有关数据库中如何实现表压缩的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

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  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的任何值，则数据库将静默地将存储表与表一起移动到新的表空间。

感谢各位的阅读！关于“数据库中如何实现表压缩”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！