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.
##隨著數據的增加，可以考慮使用表壓縮技術。壓縮能夠節省磁盤空間，減少SGA中buffer cache的使用，顯著提高查詢時讀數據的效率。對壓縮過後的表進行數據導入和DML需要消耗更多的cpu資源，但是壓縮的使用減少了I/O的開銷，可以抵消掉額外的cpu開銷成本(根據系統的情況，如果你的庫本來cpu資源已經不足，那麼這樣做就不合適了)
Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.
##壓縮技術對應用來說完全是透明的。它對dss，oltp，archival system等系統等很有用
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:
##壓縮在insert，update和批量加載時都可能發生(具體要看你使用的是哪種壓縮方式)
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 supports several methods of table compression. They are summarized in Table 20-1.
##oracle數據庫支持如下幾種壓縮方式(注意，Hybrid Columnar Compression模式的壓縮只有在Exadata上才支持)
Table 20-1 Table Compression Methods
Basic compression
High
Minimal
DSS
None.
OLTP compression
High
Minimal
OLTP, DSS
None.
Warehouse compression (Hybrid Columnar Compression)
Higher
Higher
DSS
The compression level and CPU overhead depend on compression level specified (LOW or HIGH).
Archive compression (Hybrid Columnar Compression)
Highest
Highest
Archiving
The compression level and CPU overhead depend on compression level specified (LOW or HIGH).
When you use basic compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.
##當你使用的是basic，warehouse或者archive壓縮時，只有批量載入的數據才會被壓縮(此處我覺得有點問題，應該是只有直接路徑加載的數據才會被壓縮。不知道官方文檔這裡說的批量加載是否指的就是直接路徑加載)
When you use OLTP compression, compression occurs while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:
##當你使用的是oltp壓縮時，傳統路徑插入和直接路徑插入的數據都會被壓縮
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
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.
##Basic壓縮方式僅支持有限的數據類型和sql操作，當數據使用直接路徑插入時，這些數據會被壓縮。OLTP壓縮一般用在oltp系統上，能夠壓縮各種sql操作產生的數據
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.
##因為使用了混合列模式壓縮技術，Warehouse 和 archive能夠提供最高的壓縮等級。混合列模式壓縮技術使用列模式存儲代替了之前的行模式存儲。列模式存儲使用數據庫能夠把相似的數據存儲在一起從而提高壓縮的效率。如果混合列模式壓縮的數據被更新，那麼會消耗額外的cpu把被更新的行轉變成行模式(行的rowid會發生變化)，速度也是比較快的(不知道此處的快是跟行模式壓縮相比，還是同非壓縮表的update對比？？？)。基於混合列模式壓縮的優化原理，我們應該在那些很少被update的表上使用這種壓縮方式。
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.
Table 20-2 lists characteristics of each table compression method.
Table 20-2 Table Compression Characteristics
Basic compression
COMPRESS [BASIC]
Rows are compressed with basic compression.
COMPRESS and COMPRESS BASIC are equivalent.
Rows inserted without using direct-path insert and updated rows are uncompressed.
OLTP compression
COMPRESS FOR OLTP
Rows are compressed with OLTP compression.
Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.
Warehouse compression (Hybrid Columnar Compression)
COMPRESS FOR QUERY [LOW|HIGH]
Rows are compressed with warehouse compression.
This compression method can result in high CPU overhead.
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.
Archive compression (Hybrid Columnar Compression)
COMPRESS FOR ARCHIVE [LOW|HIGH]
Rows are compressed with archive compression.
This compression method can result in high CPU overhead.
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.
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.
##你可以在建表的時候指定compress字句，也可以在表創建後使用alter table語句改變表的壓縮屬性。如果是後者，那麼表中已存在的數據不會被壓縮，只有新插入或者update的數據有可能被壓縮。同樣的如果你改變一個壓縮表的屬性為非壓縮表，表中已經存在的被壓縮的數據還是保持壓縮的狀態，不會被解壓，但新插入的數據將不會再被壓縮。
The COMPRESS FOR QUERY HIGH option is the default data warehouse compression mode. It provides good compression and performance when using Hybrid Columnar Compression on Exadata storage. The COMPRESS FOR QUERY LOW option should be used in environments where load performance is critical. It loads faster than data compressed with the COMPRESS FOR QUERY HIGH option.
The COMPRESS FOR ARCHIVE LOW option is the default archive compression mode. It provides a high compression level and is ideal for infrequently-accessed data. The COMPRESS FOR ARCHIVE HIGH option should be used for data that is rarely accessed.
A compression advisor, provided by the DBMS_COMPRESSION package, helps you determine the expected compression level for a particular table with a particular compression method.
Note:
Hybrid Columnar Compression is dependent on the underlying storage system. See Oracle Database Licensing Information for more information.See Also:
Oracle Database Concepts for an overview of table compression
"Compressed Tablespaces"
The following examples are related to table compression:
Example 20-1, "Creating a Table with OLTP Table Compression"
Example 20-2, "Creating a Table with Basic Table Compression"
Example 20-3, "Using Direct-Path Insert to Insert Rows Into a Table"
Example 20-4, "Creating a Table with Warehouse Compression"
Example 20-5, "Creating a Table with Archive Compression"
Example 20-1 Creating a Table with OLTP Table Compression
##新建一個oltp類型的壓縮表
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.
##此種壓縮方式下，直接路徑插入和傳統路徑插入的數據都會被壓縮。
Example 20-2 Creating a Table with Basic Table Compression
##新建一個basic類型的壓縮表
The following statements, which are equivalent, enable basic table compression on the sales_history table, which is a fact table in a data warehouse:
CREATE TABLE sales_history ... COMPRESS BASIC; CREATE TABLE sales_history ... COMPRESS;
Frequent queries are run against this table, but no DML is expected.
##這種壓縮方式一般被用在頻繁查詢，但不被dml的表上
Example 20-3 Using Direct-Path Insert to Insert Rows Into a Table
This example demonstrates using the APPEND hint to insert rows into the sales_history table using direct-path INSERT.
##使用append提示符激活直接路徑插入
INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890; COMMIT;
Example 20-4 Creating a Table with Warehouse Compression
This example enables Hybrid Columnar Compression on the table sales_history:
CREATE TABLE sales_history ... COMPRESS FOR QUERY;
The table is created with the default COMPRESS FOR QUERY HIGH option. This option provides a higher level of compression than basic or OLTP compression. It works well when load performance is critical, frequent queries are run against this table, and no DML is expected.
Example 20-5 Creating a Table with Archive Compression
The following example enables Hybrid Columnar Compression on the table sales_history:
CREATE TABLE sales_history ... COMPRESS FOR ARCHIVE;
The table is created with the default COMPRESS FOR ARCHIVE LOW option. This option provides the highest level of compression and works well for infrequently-accessed data.
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 do not match, then 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.##使用alter table...move partition...compress或者表的在線重定義方式改變表的壓縮方式，對新的數據和舊的數據都是有效的。
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視圖中壓縮表的compress列被標記為"ENABLED"，當然如果是分區表的話，那麼這一列被標記為空。分區表及組合分區表的壓縮情況我們相應的應該查看*_TAB_PARTITIONS及*_TAB_SUBPARTITIONS視圖
SQL> SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESSION COMPRESS_FOR ---------------- ------------ ----------------- T1 DISABLED T2 ENABLED BASIC T3 ENABLED OLTP T4 ENABLED QUERY HIGH T5 ENABLED ARCHIVE LOW
SQL> SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions; TABLE_NAME PARTITION_NAME COMPRESSION COMPRESS_FOR ----------- ---------------- ----------- ------------------------------ SALES Q4_2004 ENABLED ARCHIVE HIGH ... SALES Q3_2008 ENABLED QUERY HIGH SALES Q4_2008 ENABLED QUERY HIGH SALES Q1_2009 ENABLED OLTP SALES Q2_2009 ENABLED OLTP
When Hybrid Columnar Compression tables are updated, the rows change to a lower level of compression, such as from warehouse compression (QUERY HIGH) to OLTP compression or no compression. To determine the compression level of a row, use the GET_COMPRESSION_TYPE function in the DBMS_COMPRESSION package.
##當混合列模式壓縮的表被更新，被更新的行的壓縮等級會降低，如從warehouse compression (QUERY HIGH)降至oltp或者非壓縮。我們可以使用DBMS_COMPRESSION寶中的GET_COMPRESSION_TYPE function去查詢某一列的壓縮等級。
For example, the following query returns the compression type for a row in the hr.employees table:
SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
ownname => 'HR',
tabname => 'EMPLOYEES',
row_id => 'AAAVEIAAGAAAABTAAD'),
1, 'No Compression',
2, 'Basic or OLTP Compression',
4, 'Hybrid Columnar Compression for Query High',
8, 'Hybrid Columnar Compression for Query Low',
16, 'Hybrid Columnar Compression for Archive High',
32, 'Hybrid Columnar Compression for Archive Low',
'Unknown Compression Type') compression_type
FROM DUAL;
By sampling the table rows, you can determine the percentage of rows that are no longer at the higher compression level. You can use ALTER TABLE or MOVE PARTITION to specify a higher compression level. For example, if 10 percent of the rows are no longer at the highest compression level, then you might alter the table or move the partition to specify a higher compression level.
See Also:
Oracle Database PL/SQL Packages and Types Reference for additional information about GET_COMPRESSION_TYPEYou can change the compression level for a partition, table, or tablespace. For example, suppose a company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free disk space.
If a table is partitioned, then the DBMS_REDEFINITION package can change the compression level of the table. This package performs online redefinition of a table by creating a temporary copy of the table that holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level of the existing table and the new table. Ensure you have enough hard disk space on your system before using the DBMS_REDEFINITION package.
If a table is not partitioned, then you can use the ALTER TABLE...MOVE...COMPRESS FOR... statement to change the compression level. The ALTER TABLE...MOVE statement does not permit DML statements against the table while the command is running.
To change the compression level for a partition, use the ALTER TABLE...MODIFY PARTITION statement. To change the compression level for a tablespace, use the ALTER TABLESPACE statement.
##對於非分區表我們只能通過ALTER TABLE...MOVE...COMPRESS FOR命令來改變表的壓縮等級，在變更的過程中不允許對表進行DML操作(如果表很大的話，就會長時間的影響應用)。如果是分區表我們可以通過上面的方式來做，也可以通過使用DBMS_REDEFINITION包來改變表的壓縮等級，使用在線重定義的時候會為目標表建一個臨時備份來保存表中的數據，在線重定義過程中目標是一直可以訪問和dml的。
See Also:
"Moving a Table to a New Segment or Tablespace" for additional information about the ALTER TABLE command
Oracle Database PL/SQL Packages and Types Reference for additional information about the DBMS_REDEFINITION package
The following restrictions apply when adding columns to compressed tables:
##向壓縮表中添加列的時候有如下限制：
Basic compression—You cannot specify a default value for an added column.##對於basic壓縮方式你不能為添加的列指定默認值
OLTP compression—If a default value is specified for an added column, then 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.##對於basic壓縮來說不支持列刪除
OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.##對於oltp壓縮也是不支持列刪除，但是我們可以把列設置為UNUSED的，避免解壓縮和重新壓縮操作隊它的影響。
Hybrid Columnar Compression tables can be imported using the impdp command of the Data Pump Import utility. By default, the impdp command preserves the table properties, and the imported table is a Hybrid Columnar Compression table. On tablespaces not supporting Hybrid Columnar Compression, the impdp command fails with an error. The tables can also be exported using the expdp command.
You can import the Hybrid Columnar Compression table as an uncompressed table using the TRANSFORM:SEGMENT_ATTRIBUTES=n option clause of the impdp command.
An uncompressed or OLTP-compressed table can be converted to Hybrid Columnar Compression format during import. To convert a non-Hybrid Columnar Compression table to a Hybrid Columnar Compression table, do the following:
Specify default compression for the tablespace using the ALTER TABLESPACE ... SET DEFAULT COMPRESS command.
Override the SEGMENT_ATTRIBUTES option of the imported table during import.
See Also:
Oracle Database Utilities for additional information about the Data Pump Import utility
Oracle Database SQL Language Reference for additional information about the ALTER TABLESPACE command
There may be times when a Hybrid Columnar Compression table must be restored from a backup. The table can be restored to a system that supports Hybrid Columnar Compression, or to a system that does not support Hybrid Columnar Compression. When restoring a table with Hybrid Columnar Compression to a system that supports Hybrid Columnar Compression, restore the file using Oracle Recovery Manager (RMAN) as usual.
When a Hybrid Columnar Compression table is restored to a system that does not support Hybrid Columnar Compression, you must convert the table from Hybrid Columnar Compression to OLTP compression or an uncompressed format. To restore the table, do the following:
Ensure there is sufficient storage in environment to hold the data in uncompressed or OLTP compression format.
Use RMAN to restore the Hybrid Columnar Compression tablespace.
Complete one of the following actions to convert the table from Hybrid Columnar Compression to OLTP compression or an uncompressed format:
Use the following statement to change the data compression from Hybrid Columnar Compression to COMPRESS FOR OLTP:
ALTER TABLE table_name MOVE COMPRESS FOR OLTP;
Use the following statement to change the data compression from Hybrid Columnar Compression to NOCOMPRESS:
ALTER TABLE table_name MOVE NOCOMPRESS;
Use the following statement to change each partition to NOCOMPRESS:
ALTER TABLE table_name MOVE PARTITION partition_name NOCOMPRESS;
Change each partition separately.
Use the following statement to move the data to NOCOMPRESS in parallel:
ALTER TABLE table_name MOVE NOCOMPRESS PARALLEL;
See ALso:
Oracle Database Backup and Recovery User's Guide for additional information about RMAN
Oracle Database SQL Language Reference for additional information about the ALTER TABLE command
The following are notes and restrictions related to compressed tables:
Online segment shrink is not supported for compressed tables.##壓縮表不支持在線segment shrink操作
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.##本節所說的壓縮方式不能再SecureFiles large objects上使用，SecureFiles LOBs有自己的壓縮方式
Compression technology uses CPU. Ensure that you have enough available CPU to handle the additional load.##壓縮操作對cpu的消耗比較高，確保你的cpu資源夠用
Tables created with basic compression have the PCT_FREE parameter automatically set to 0 unless you specify otherwise.##建表時指定basic壓縮方式PCT_FREE值為0，當然你也可以特別指定其他的你認為合適的值。(建表時指定oltp壓縮方式，PCT_FREE值默認會被設置為10)
If you use conventional DML on a table compressed with basic compression or Hybrid Columnar Compression, then all inserted and updated rows are stored uncompressed or in a less-compressed format. To "pack" the compressed table so that these rows are compressed, 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, then 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"
