Autonomous Data Warehouse 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
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
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;