Data Compression in Oracle

🔹 Data Compression in Oracle

Oracle provides several methods to compress data to improve storage efficiency and performance. Compression is useful in large databases, data warehouses, and OLTP systems.

---

1️⃣ Table Compression (OLTP & Warehouse)

Oracle Table Compression reduces storage space by compressing table data.

🔹 How to Enable Table Compression

1.1 Basic Compression (for Data Warehouses)

Used for bulk operations (INSERT /*+ APPEND */, CREATE TABLE AS SELECT).

CREATE TABLE sales_compressed 
AS SELECT * FROM sales
COMPRESS;

🔹 Best for: Bulk loads in Data Warehouses.

---

1.2 OLTP Compression (For Transactions)

Used for OLTP workloads with frequent INSERT, UPDATE, and DELETE.

CREATE TABLE orders_compressed (
    order_id NUMBER PRIMARY KEY, 
    customer_name VARCHAR2(100), 
    order_date DATE
) COMPRESS FOR OLTP;

🔹 Best for: Regular transactional tables.

---

2️⃣ Index Compression

Oracle allows index compression to reduce the storage of repeated keys.

🔹 How to Enable Index Compression

CREATE INDEX idx_compressed ON sales(customer_id)
COMPRESS 2;

🔹 COMPRESS 2 means Oracle will compress the first two columns.

---

3️⃣ LOB Compression (Large Objects)

LOB (CLOB, BLOB) compression reduces the size of documents, images, and XML data.

🔹 How to Enable LOB Compression

CREATE TABLE documents (
    doc_id NUMBER PRIMARY KEY, 
    doc_content CLOB
) LOB (doc_content) STORE AS SECUREFILE (COMPRESS HIGH);

🔹 COMPRESS HIGH gives better compression but higher CPU usage.
🔹 COMPRESS LOW is faster but less compression.

---

4️⃣ Advanced Table Compression (Hybrid Columnar Compression)

Used in Exadata & ZFS storage for extreme compression.

🔹 How to Enable Hybrid Columnar Compression (HCC)

CREATE TABLE large_table COMPRESS FOR QUERY HIGH
AS SELECT * FROM big_data_table;

🔹 Best for analytics and data warehouses.

---

5️⃣ RMAN Backup Compression

To reduce backup size, enable RMAN compression.

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

🔹 Reduces backup storage and improves performance.

---

🔹 Summary: Best Compression Methods

Compression Type	Best For	Command
Basic Table Compression	Bulk loads, Data Warehouses	COMPRESS
OLTP Compression	Transactional tables	COMPRESS FOR OLTP
Index Compression	Repeating indexed values	CREATE INDEX ... COMPRESS
LOB Compression	Large objects (CLOB, BLOB)	STORE AS SECUREFILE (COMPRESS HIGH)
Hybrid Columnar Compression	Exadata, Warehouses	COMPRESS FOR QUERY HIGH
RMAN Backup Compression	Backup storage reduction	BACKUP AS COMPRESSED BACKUPSET