Consider Using Table Compression
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.
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
Example 20–1 Creating a Table with OLTP 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.
Example 20–2 Creating a Table with Basic Table Compression
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.
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.
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 optionprovides the highest level of compression and works well for infrequently-accessed data
Compression and Partitioned Tables
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
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
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.
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
Example 20–1 Creating a Table with OLTP 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.
Example 20–2 Creating a Table with Basic Table Compression
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.
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.
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 optionprovides the highest level of compression and works well for infrequently-accessed data
Compression and Partitioned Tables
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
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