The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small, and in OLTP environments. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
l When you enable table compression by specifying either COMPRESS or COMPRESS FOR DIRECT_LOAD OPERATIONS, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format.
l When you enable table compression by specifying COMPRESS FOR ALL OPERATIONS, Oracle Database attempts to compress data during all DML operations on the table.
Note:
Tables with COMPRESS or COMPRESS FOR DIRECT_LOAD OPERATIONS use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause. Tables with COMPRESS FOR ALL OPERATIONS or NOCOMPRESS use the PCTFREE default value of 10, to maximize compress while still allowing for some future DML changes to the data, unless you override this default explicitly.
You can specify table compression for the following portions of a heap-organized table:
l For an entire table, in the physical_properties clause of relational_table or object_table
l For a range partition, in the table_partition_description of the range_partitions clause
l For a composite range partition, in the table_partition_description of the range_partition_desc
l For a composite list partition, in the table_partition_description of the list_partition_desc
l For a list partition, in the table_partition_description of the list_partitions clause
l For a system or reference partition, in the table_partition_description of the reference_partition_description
l For the storage table of a nested table, in the nested_table_col_properties clause
See Also:
"Conventional and Direct-Path INSERT" for information on direct-path INSERT operations, including restrictions
Oracle Database Data Warehousing Guide for information on table compression usage scenarios
Restrictions on Table Compression Table compression is subject to the following restrictions:
l Table compression is not supported for tables with more than 255 columns.
l Data segments of BasicFile LOBs are not compressed. For information on compression of SecureFile LOBs, see LOB_compression_clause.
l You cannot specify table compression for an index-organized table, any overflow segment or partition of an overflow segment, or any mapping table segment of an index-organized table.
l You cannot specify table compression for external tables or for tables that are part of a cluster.
l You cannot drop a column from a table that is compressed for direct-load operations, although you can set such a column as unused. All of the operations of the ALTER TABLE ... drop_column_clause are valid for tables compressed for all operations.
Oracle Database Data Warehousing Guide
Table Compression
You can save disk space by compressing heap-organized tables. A typical type of heap-organized table you should consider for table compression is partitioned tables.
To reduce disk use and memory use (specifically, the buffer cache), you can store tables and partitioned tables in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a cost in CPU overhead.
Table compression should be used with highly redundant data, such as tables with many foreign keys. You should avoid compressing tables with much update or other DML activity. Although compressed tables or partitions are updatable, there is some overhead in updating these tables, and high update activity may work against compression by causing some space to be wasted.
"Conventional and Direct-Path INSERT"
Conventional and Direct-Path INSERT
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-path INSERT:
l Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database.
l Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:
Direct-path INSERT always bypasses undo generation for data changes.
If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.
If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for both LOGGING and NOLOGGING tables.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
l You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
l Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
l If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
l The target table cannot be part of a cluster.
l The target table cannot contain object type columns.
l Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
l Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
l The target table cannot have any triggers or referential integrity constraints defined on it.
l The target table cannot be replicated.
l A transaction containing a direct-path INSERT statement cannot be or become distributed.
到了这个版本，10g的compree已经有了分支，即compress direct_load/all ，从官方文档的描述，可以看出 direct_load 就是之前的10g版本的名字的改变，功能和特点（当然也包括限制）完全一致，而10g不支持的oltp，在这个版本中支持了，先不说功能好不好用，起码有进步了。
在 all operations 中，pctfree为10，为dml预留了空间。分区表支持粒度加大，但是限制几乎一样，毕竟对压缩来讲，入库时候需要满足的条件没有改变，这也是改变不了的。在此属性中，已经可以drop column了。但还是最多不能超过255列。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-1360983/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10037372/viewspace-1360983/
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
