Managing DML Performance and Compression for Data Warehouse Workloads

Autonomous Database with Data Warehouse workloads uses Hybrid Columnar Compression for all tables by default. This gives the best compression ratio and optimal performance for direct-path load operations like the loads done using the DBMS_CLOUD package. If you perform DML operations like UPDATE and MERGE on your tables these may cause the compression ratio for the affected rows to decrease leading to larger table sizes. These operations may also perform slower compared to the same operations on an uncompressed table.

For the best compression ratio and optimal performance Oracle recommends using bulk operations like direct-path loads and CREATE TABLE AS SELECT statements. But, if your workload requires frequent DML operations like UPDATE and MERGE on large parts of a table, you can create those tables as uncompressed tables to achieve better DML performance. For example, the following statement creates the table SALES as an uncompressed table:

CREATE TABLE sales (
    prod_id             NUMBER          NOT NULL,
    cust_id             NUMBER          NOT NULL,
    time_id             DATE            NOT NULL,
    channel_id          NUMBER          NOT NULL,
    promo_id            NUMBER          NOT NULL,
    quantity_sold       NUMBER(10,2)    NOT NULL,
    amount_sold         NUMBER(10,2)    NOT NULL)
NOCOMPRESS;

At any point in time you can use the ALTER TABLE MOVE statement to compress these tables without impacting queries accessing them. For example, the following statement compresses the table SALES using Hybrid Columnar Compression.

ALTER TABLE sales MOVE COLUMN STORE COMPRESS FOR QUERY HIGH;