Table Compression

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