|
|
http://download.oracle.com/docs/ ... les002.htm#CJAGFBFG
Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-06
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:
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
Oracle Database support two methods of table compression. They are summarized in Table 20-1.
Table 20-1 Table Compression Methods
Table Compression Method Applications CREATE/ALTER TABLE Syntax Direct-Path Insert DML
Basic compression
DSS
COMPRESS [BASIC]Foot 1
Yes
YesFoot 2
OLTP compression
OLTP, DSS
COMPRESS FOR OLTP
Yes
Yes
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.
See Also:
The "Table Compression" section of Oracle Database Concepts for an overview of table compression
"Compressed Tablespaces"
Examples
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 DISABLED
T2 ENABLED BASIC
T3 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 OLTP
SALES Q1_2009 ENABLED OLTP
SALES 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.
See Also:
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
"Improving INSERT Performance with Direct-Path Insert"
"Redefining Tables Online" |
|
IP卡
狗仔卡
发表于 2008-5-6 13:27
收藏
提升卡
置顶卡
变色卡
显身卡
发表于 2010-12-21 10:13