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. 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 BASIC, you enable basic table compression. 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 Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause.
l In earlier releases, this type of compression was called DSS table compression and was enabled using COMPRESS FOR DIRECT_LOAD OPERATIONS. This syntax has been deprecated.
for information on direct-path INSERT operations, including restrictions
l When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.
l Tables with COMPRESS FOR OLTP 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.
l In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. This syntax has been deprecated.
l When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable hybrid columnar compression. With hybrid columnar compression, data can be compressed during bulk load operations. During the load process, data is transformed into a column-oriented format and then compressed. Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio. Hybrid columnar compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form of compression is recommended for data that is not frequently updated.
l COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.
l COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.
l Tables with COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause. For these tables, PCTFREE has no effect for blocks loaded using direct-path INSERT. PCTFREE is honored for blocks loaded using conventional INSERT, and for blocks created as a result of DML operations on blocks originally loaded using direct-path INSERT.
Oracle Exadata Storage Server Software documentation for more information on hybrid columnar compression, which is a feature of Oracle Exadata
l 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_desc
l For the storage table of a nested table, in the nested_table_col_properties clause
for information about the DBMS_COMPRESSION package, which helps you choose the correct compression level for an application, and for more information about table compression, including examples
Restrictions on Table Compression Table compression is subject to the following restrictions:
l COMPRESS FOR OLTP and COMPRESS BASIC are 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 .
l You cannot drop a column from a table that uses COMPRESS BASIC, although you can set such a column as unused. All of the operations of the ALTER TABLE ... drop_column_clause are valid for tables that use COMPRESS FOR OLTP, COMPRESS FOR QUERY, and COMPRESS FOR ARCHIVE.
l If you specify COMPRESS FOR OLTP, then chained rows are not compressed unless the header for the row remains in the original block and all row columns are moved to another block. If the row chaining results in leaving just the row header in the block and moving all of the row's columns to the next block, and they all fit in the next block, then the columns can be compressed.
l You cannot specify any type of 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 any type of table compression for external tables or for tables that are part of a cluster.
l You cannot specify hybrid columnar compression on tables with LONG columns, tables that are owned by the SYS schema and reside in the SYSTEM tablespace, or tables with row dependencies enabled.
l You cannot specify hybrid columnar compression on the following object-relational features: object tables, XMLType tables, columns with abstract data types, collections stored as tables, or OPAQUE types, including XMLType columns stored as objects.
l When you update a row in a table compressed with hybrid columnar compression, the ROWID of the row may change.
l In tables compressed with hybrid columnar compression, updates to a single row may result in locks on multiple rows. Concurrency for write transactions may therefore be affected.
l If a table compressed with hybrid columnar compression has a foreign key constraint, and you insert data using INSERT with the APPEND hint, then the data will be compressed using OLTP. To compress the data with hybrid columnar compression, disable the foreign key constraint, insert the data using INSERT with the APPEND hint, and then reenable the foreign key constraint.
Consider Using Table Compression
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 may be offset by reduced I/O requirements.
Table compression is completely transparent to applications. It is useful in both decision support systems (DSS) and online transaction processing (OLTP) 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:
l Single-row or array inserts and updates
l 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
Oracle Database support two methods of table compression. They are summarized in .
Table 20-1 Table Compression Methods
Table Compression Method
CREATE/ALTER TABLE Syntax
COMPRESS [BASIC] href="#sthref2140" Foot?1?
Yes href="#sthref2141" Foot?2?
COMPRESS FOR OLTP
Footnote 1 COMPRESS and COMPRESS BASIC are equivalent
Footnote 2 Inserted and updated rows are uncompressed
You specify table compression with the COMPRESS clause of the CREATE TABLE statement. 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. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.
To enable OLTP table compression, you must set the COMPATIBLE initialization parameter to 11.1.0 or higher.
The "Table Compression" section of for an overview of table compression
The following example enables OLTP table compression on the table orders:
CREATE TABLE orders ... COMPRESS FOR OLTP;
Data for the orders table is compressed during both direct-path insert and conventional DML.
The next two examples, which are equivalent, enable basic table compression on the sales_history table, which is a fact table in a data warehouse. Frequent queries are run against this table, but no DML is expected.
CREATE TABLE sales_history ... COMPRESS BASIC; CREATE TABLE sales_history ... COMPRESS;
The next 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 year=2008;COMMIT;
Compression and Partitioned Tables
A table can have both compressed and uncompressed partitions, and different partitions can use different compression methods. If the compression settings for a table and one of its partitions disagree, the partition setting has precedence for the partition.
To change the compression method for a partition, do one of the following:
To change the compression method for new data only, use ALTER TABLE ... MODIFY PARTITION ... COMPRESS ...
To change the compression method for both new and existing data, use either ALTER TABLE ... MOVE PARTITION ... COMPRESS ... or online table redefinition.
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.
SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESSION COMPRESS_FOR---------------- ----------- ------------------T1 DISABLEDT2 ENABLED BASICT3 ENABLED OLTP
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions; TABLE_NAME PARTITION_NAME COMPRESSION COMPRESS_FOR----------- ---------------- ----------- ------------------------------SALES Q4_2008 ENABLED OLTPSALES Q1_2009 ENABLED OLTPSALES Q2_2009 ENABLED OLTP
Adding and Dropping Columns in Compressed Tables
The following restrictions apply when adding columns to compressed tables:
Basic compression—You cannot specify a default value for an added column.
OLTP compression—If a default value is specified for an added column, the column must be NOT NULL. Added nullable columns with default values are not supported.
The following restrictions apply when dropping columns in compressed tables:
Basic compression—Dropping a column is not supported.
OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.
Notes and Other Restrictions for Compressed Tables
Online segment shrink is not supported for compressed tables.
The table compression methods described in this section do not apply to SecureFile large objects (LOBs). SecureFile LOBs have their own compression methods. See Oracle Database SecureFiles and Large Objects Developer's Guide for more information.
Compression technology uses CPU. You should 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.
Packing Compressed Tables
If you use conventional DML on a table compressed with basic compression, then all inserted and updated rows are stored uncompressed. To "pack" the compressed table such that these rows are compressed, you can use an ALTER TABLE MOVE statement. This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes. If this is not acceptable, you can use online table redefinition.
Oracle Database SQL Language Reference for more details on the CREATE TABLE...COMPRESS, ALTER TABLE...COMPRESS, and ALTER TABLE...MOVE statements, including restrictions
Oracle Database VLDB and Partitioning Guide for more information on table partitioning
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-1361008/，如需转载，请注明出处，否则将追究法律责任。