Managing Policies for Automatic Data Optimization

You can specify policies for ADO at the row, segment, and tablespace level when creating and altering tables with SQL statements. By specifying policies for ADO, you can automate data movement between different tiers of storage within the database. These policies also enable you to specify different compression levels for each tier, and to control when the data movement takes place. The scope of policies for ADO can be specified as SEGMENT, ROW, or GROUP.

The ILM clauses of the SQL CREATE and ALTER TABLE statements enable you to create, delete, enable or disable a policy for ADO. An ILM policy clause determines the compression or storage tiering policy and contains additional clauses. When you create a table, you can add a new policy for ADO. You can alter the table to add more policies or to enable, disable, or delete existing policies. You can add policies to an entire table or a partition of a table. ILM ADO policies are given a system-generated name, such P1, P2, ... Pn.

A segment level policy executes only one time. After the policy executes successfully, it is disabled and is not evaluated again. However, you can explicitly enable the policy again. A row level policy continues to execute and is not disabled after a successful execution.

The default mappings for compression that can be applied to group policies are:

• COMPRESS ADVANCED on a heap table maps to standard compression for indexes and LOW for LOB segments.

• COMPRESS FOR QUERY LOW/QUERY HIGH on a heap table maps to standard compression for indexes and MEDIUM for LOB segments.

• COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH on a heap table maps to standard compression for indexes and HIGH for LOB segments.

The compression mapping cannot be changed. GROUP can only be applied to segment level policies. The storage tiering policies are applicable only at the segment level and cannot be specified at the row level.

You can customize policies with the ON PL/SQL_function option which provides the ability to determine when the policy should be executed. The ON PL/SQL_function option is available only with segment level policies. For example:

CREATE OR REPLACE FUNCTION my_custom_ado_rules (objn IN NUMBER) RETURN BOOLEAN;

ALTER TABLE sales_custom ILM ADD POLICY COMPRESS ADVANCED SEGMENT 
     ON my_custom_ado_rules;