Managing data efficiently is crucial in any database system, especially as datasets grow over time. Automatic Data Optimization (ADO) in Oracle Autonomous Database helps automate data lifecycle management by applying compression tiering based on how often data is accessed or modified. This ensures that frequently used data remains easily accessible, while older, less-used data is compressed.
In this guide, we’ll cover:
✅ How to enable ADO (including required settings)
✅ Different levels of data compression
✅ ADO policy types and syntax
✅ Practical examples of implementing and modifying ADO policies
✅ How to monitor and manage ADO policies
🔧 Requirement: Enabling Heat Map
Before using Automatic Data Optimization (ADO), you must ensure that Heat Map is enabled. Heat Map tracks how often data is accessed or modified, allowing ADO policies to be applied based on real-time usage patterns.
Check if Heat Map is Enabled
Run the following command:
SHOW PARAMETER heat_map;
If the result shows OFF, enable it with:
Note:
HEAT_MAP must be ON for ADO policies to work.
You may need ALTER SYSTEM privileges to change this setting.
Once enabled, the database will start tracking data usage, allowing ADO to optimize storage automatically.
🚀 What is Automatic Data Optimization (ADO)?
Automatic Data Optimization (ADO) uses Oracle’s Heat Map to track how often data is accessed or modified. Based on this information, ADO policies automatically compress data, optimizing performance and reducing storage costs.
💡 Example Use Case:
Imagine you have a table storing sales transactions. Recent sales data is frequently accessed, but older records are rarely used. With ADO, you can set a policy to automatically compress data older than 6 months, reducing storage costs while keeping recent data readily available.
🗂️ Understanding Oracle Compression Levels
Oracle provides different compression techniques, each suited for different scenarios.
1. Row Store Compression (OLTP Mode)
🔹 Best for: Active transactional tables (OLTP)
🔹 How it works: Compresses data as it’s inserted or updated, reducing storage usage while allowing normal DML operations.
🔹 Use case: Frequently updated tables in high-transaction environments.
🔹 Compression Level: Moderate (lower than HCC but maintains good performance for updates).
ALTER TABLE sales_ado ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION
sales_ado after 30 days of inactivity.
2. Hybrid Columnar Compression (HCC)
🔹 Best for: Data warehousing, reporting, and historical data
🔹 How it works: Organizes data in columnar format for better compression ratios.
🔹 Variants:
-
Warehouse Compression – Balances compression and performance (for frequently queried data).
-
Archive Compression – Maximum compression for rarely accessed historical data.
🔹 Compression Level: Very high (best for read-intensive workloads).
ALTER TABLE sales MODIFY PARTITION sales_q1_2001 ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 6 MONTHS OF NO MODIFICATION
👉 This policy applies high-level archive compression to sales_q1_2001 after 6 months of inactivity.
🔄 Defining ADO Policies: Syntax & Options
ADO policies can be applied at different levels:
-
Row-Level Policies (compress individual rows)
-
Segment-Level Policies (compress entire table or partition)
📌 ADO Policy Syntax
ILM ADD POLICY <action>
[FOR <data type>]
AFTER <time condition>
[OF <heat map condition>];
📌 Actions Available in ADO Policies
| Action | Description |
|---|---|
| COMPRESS FOR OLTP | Enables OLTP row store compression. |
| COMPRESS FOR QUERY LOW | Enables Hybrid Columnar Compression (HCC) for query optimization. |
| COMPRESS FOR QUERY HIGH | More aggressive HCC for better compression. |
| COMPRESS FOR ARCHIVE LOW | Moderate archive compression for rarely accessed data. |
| COMPRESS FOR ARCHIVE HIGH | Maximum compression for cold data. |
📌 Time-Based & Heat Map Conditions
| Condition | Description |
|---|---|
| AFTER X DAYS OF NO MODIFICATION | Triggers action after a specified period of no updates. |
| AFTER X DAYS OF NO ACCESS | Triggers action after data hasn’t been read for a certain period. |
🛠️ Creating a Table with Compression and Partitioning
Below is an example of creating a partitioned table with compression policies. This table automatically compresses older data using Hybrid Columnar Compression (HCC) after a set period.
📌 Create Table with ADO Policies
CREATE TABLE sales_ado (
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
)
PARTITION BY RANGE (time_id)
(
PARTITION sales_q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','dd-MON-yyyy')),
PARTITION sales_q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','dd-MON-yyyy')),
PARTITION sales_q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','dd-MON-yyyy')),
PARTITION sales_q4_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy'))
)
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
AFTER 12 MONTHS OF NO ACCESS;
/* Add a row-level compression policy after 30 days of no modifications */ ALTER TABLE sales_ado MODIFY PARTITION sales_q1_2012 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION; /* Add a segment level compression policy for data after 6 months of no modifications */ ALTER TABLE sales MODIFY PARTITION sales_q2_2012 ILM ADD POLICY COMPRESS FOR ARCHIVE LOW SEGMENT AFTER 6 MONTHS OF NO MODIFICATION; /* Add a segment level compression policy for data after 12 months of no access */ ALTER TABLE sales MODIFY PARTITION sales_q3_2012 ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 12 MONTHS OF NO ACCESS;
💡 What does this do?
✔ Creates a partitioned table (sales_ado) for sales data.
✔ Each partition has a different compression level:
-
sales_q1_2012→ Row-level Compression -
sales_q2_2012→ Archive Low Compression sales_q3_2012→ Archive High Compression
🛠️ Modifying an Existing ADO Policy
ADO policies can be modified if business requirements change. You can check the existing policies with the following query:
SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled FROM USER_ILMPOLICIES;
🔄 Modify an Existing ADO Policy
/* You can disable or delete an ADO policy in a table with the following */ ALTER TABLE sales_ado ILM DISABLE POLICY P1; ALTER TABLE sales_ado ILM DELETE POLICY P1; /* You can disable or delete all ADO policies in a table with the following */ ALTER TABLE sales_ado ILM DISABLE_ALL; ALTER TABLE sales_ado IL; DELETE_ALL; /* You can disable or delete an ADO policy in a partition with the following */ ALTER TABLE sales MODIFY PARTITION sales_q1_2012 ILM DISABLE POLICY P2; ALTER TABLE sales MODIFY PARTITION sales_q1_2012 ILM DELETE POLICY P2; /* You can disable or delete all ADO policies in a partition with the following */ ALTER TABLE sales MODIFY PARTITION sales_q1_2012 ILM DISABLE_ALL; ALTER TABLE sales MODIFY PARTITION sales_q1_2012 ILM DELETE_ALL;
📌 Takeaways
🔹 Enabling Heat Map is required (ALTER SYSTEM SET HEAT_MAP = ON;).
🔹 ADO optimizes storage automatically based on real-time access patterns.
🔹 Different compression techniques include Row Store Compression (OLTP) and Hybrid Columnar Compression (HCC).
🔹 ADO policies can be applied at table or partition levels.
🔹 Policies can be modified by deleting and re-adding them.
🔹 Monitoring ADO policies using USER_ILMPOLICIES helps track active policies.
🚀 By using ADO, you reduce storage costs, improve performance, and ensure your database remains optimized without manual intervention. You can refer to Oracle Documentation in the following link: here
💬 Are you using ADO? Let’s discuss in the comments! 🚀