首页 > 应用开发 > IT综合 > Using Table Compression (161)

Using Table Compression (161)

原创 IT综合 作者:tsinglee 时间:2007-11-08 10:40:48 0 删除 编辑

Compression occurs while data is being bulk inserted or bulk loaded. These operations
■ Direct path SQL*Loader
■ CREATE TABLE and AS SELECT statements
■ Parallel INSERT (or serial INSERT with an APPEND hint) statements

Existing data in the database can also be compressed by moving it into compressed
form through ALTER TABLE and MOVE statements. This operation takes an exclusive
lock on the table, and therefore prevents any updates and loads until it completes. If
this is not acceptable, then Oracle's online redefinition utility (DBMS_REDEFINITION
PL/SQL package) can be used.

Data compression works for all datatypes except for all variants of LOBs and
datatypes derived from LOBs, such as VARRAYs stored out of line or the XML datatype
stored in a CLOB.

Table compression is done as part of bulk loading data into the database. The overhead
associated with compression is most visible at that time. This is the primary trade-off
that needs to be taken into account when considering compression.

Compressed tables or partitions can be modified the same as other Oracle tables or
partitions. For example, data can be modified using INSERT, UPATE, and DELETE
statements. However, data modified without using bulk insertion or bulk loading
techniques is not compressed. Deleting compressed data is as fast as deleting
uncompressed data. Inserting new data is also as fast, because data is not compressed
in the case of conventional INSERT; it is compressed only doing bulk load. Updating
compressed data can be slower in some cases. For these reasons, compression is more
suitable for data warehousing applications than OLTP applications. Data should be
organized such that read only or infrequently changing portions of the data (for
example, historical data) is kept compressed.

1. 在数据进行批量插入或者批量加载时 , 发生表压缩 , 操作包括 : 直接路径加载 , ctas , 并行插入或者insert加append提示 .
2. 使用alter table <> move时 , 发生表已有数据压缩 . 这个操作产生排他锁 .
3. 除了lobs , 以及基于lobs的字段类型外 , 其他类型的数据都可被压缩
4. 表数据压缩更适合于只读数据或者很少改变的数据(历史数据) , 更适合于数据数据仓库


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
  • 博文量
  • 访问量