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.
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
2.Oracle Database supports several methods of table compression.
A DSS (Decision Support System) is an interactive computer-based systems intended to help decision makers utilize data and models to identify and solve semi-structured (or unstructured) problems.
Table Compression Methods
Basic table compression compresses data inserted by direct path load only and supports limited data types and SQL operations. OLTP table compression is intended for OLTP applications and compresses data manipulated by any SQL operation.
Warehouse compression and online archival 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. Because Hybrid Columnar Compression requires high CPU overhead for DML, use it only for data that is updated infrequently.
hybrid columnar compression需要更好的CPU消耗，只有在数据很少更新的情况下使用。
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 result in a less compressed format, and reduced compression level.
Table 20-2 lists characteristics of each table compression method.
Table 20-2 Table Compression Characteristics
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.
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 online archival 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.
3.Examples Related to Table Compression
3.1Creating a Table with OLTP Table Compression
CREATE TABLE orders ... COMPRESS FOR OLTP;
3.2Creating a Table with Basic Table Compression
CREATE TABLE sales_history ... COMPRESS BASIC;
CREATE TABLE sales_history ... COMPRESS;
Frequent queries are run against this table, but no DML is expected.
3.3Using 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.
INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890;
3.4Creating 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.
3.5Creating a Table with Online Archival 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.
4.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 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.
5.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.
SQL> SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESSION COMPRESS_FOR
---------------- ------------ -----------------
T2 ENABLED BASIC
T3 ENABLED OLTP
T4 ENABLED QUERY HIGH
T5 ENABLED ARCHIVE LOW
SQL> SELECT table_name, partition_name, compression, compress_for
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
6.Determining Which Rows Are Compressed
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.
For example, the following query returns the compression type for a row in the hr.employees table:
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
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.
7.Changing the Compression Level
You 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 online archival 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.
8.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, 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.
OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.
9.Restoring a Hybrid Columnar Compression Table
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:
1. Ensure there is sufficient storage in environment to hold the data in uncompressed or OLTP compression format.
2.Use RMAN to restore the Hybrid Columnar Compression tablespace.
3.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;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26844646/viewspace-777025/，如需转载，请注明出处，否则将追究法律责任。