You can specify compression at the segment-level within a table using a segment-level compression tiering policy. In combination with the row-level compression tiering policy, you have fine-grained control over how the data in your database is stored and managed.
Example 5-6 illustrates how to create policies for ADO to enforce a compression and storage tiering policy on the sales_ado table, reflecting the following business requirements:
Bulk Load Data
Run OLTP workloads
After six months with no updates, compress for Archive High
Move to low cost storage
Example 5-6 Using segment-level compression and storage tiering
/* Add a segment level compression policy after 6 months of no changes */ ALTER TABLE sales_ado ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 6 MONTHS OF NO MODIFICATION; Table altered. /* Add storage tier policy */ ALTER TABLE sales_ado ILM ADD POLICY TIER TO my_low_cost_tablespace; SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled FROM USER_ILMPOLICIES; POLICY_NAME POLICY_TYPE ENABLED ------------------------ ------------- ------- ... P6 DATA MOVEMENT YES P7 DATA MOVEMENT YES