Similar to the Oracle Database capabilities of Heat Map and Automatic Data Optimization (ADO) for data segments, the Oracle Database 21c capability of Automatic Data Optimization for Indexes supports data lifecycle management on indexes by enabling organizations to set policies that automatically optimize index storage. 

ADO-based storage optimization

When an index ADO policy qualifies for execution, the database automatically determines which index optimization action to implement (compress, merge or rebuild index). In the example below, the “OPTIMIZE” clause instructs the ADO policy to automatically optimize the index (Oracle Database automatically determines the best optimization action and implements that action) when the policy condition (no modification for 3 days) is met and the policy is executed.

ALTER INDEX orders_idx ILM ADD POLICY
OPTIMIZE AFTER 3 DAYS OF NO MODIFICATION;

The “optimize” actions include:

  • Compress portions of the key values in an index segment
  • Merge the contents of index blocks where possible to free blocks for reuse
  • Rebuild index to improve space usage and access speed

ADO-based storage tiering

Index storage tiering is not based upon the ADO condition clause (i.e. after “x” days of NO MODIFICATION) as is index storage optimization, and instead is based upon tablespace space pressure. The justification for making storage tiering dependent on “space pressure” is exactly as you might imagine, the belief that users will want to keep as much data as possible on their high performance (and most expensive) storage tier, and not move data to a lower performance storage tier until it is absolutely required. The exception to the storage pressure requirement are storage tiering policies with the ‘READ ONLY’ option, these are triggered by a heat-map based condition clause.

The value for the ADO parameter TBS_PERCENT_USED specifies the percentage of the tablespace quota when a tablespace is considered full. The value for TBS_PERCENT_FREE specifies the targeted free percentage for the tablespace. When the percentage of the tablespace quota reaches the value of TBS_PERCENT_USED, ADO begins to move index segments so that percent free of the tablespace quota approaches the value of TBS_PERCENT_FREE. This action by ADO is a best effort and not a guarantee.

You can set ILM ADO parameters with the CUSTOMIZE_ILM procedure in the DBMS_ILM_ADMIN PL/SQL package, for example:

BEGIN
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,85):
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,25):
END;

In this example, when a tablespace reaches the fullness threshold (85%) defined by the user, the database will automatically move the coldest table/partition(s) in the tablespace to the target tablespace until the tablespace quota has at least 25 percent free. Of course, this only applies to tables and partitions that have a “TIER TO” ADO policy defined (see examples below). This frees up space on your tier 1 storage (ACTIVE tier) for the segments that would truly benefit from the performance while moving colder segments, that don’t need Tier 1 performance, to lower cost Tier 2 storage (LESS ACTIVE/COLD Tier).

For example:

ALTER TABLE orders ILM ADD POLICY TIER TO lessactivetbs;
ALTER INDEX orders_idx ILM ADD POLICY TIER TO lessactivetbs;

In this simple TIER TO example, Oracle Database automatically evaluates the ADO policies (and tablespace fullness) to determine when data and index segments are eligible to move to a different tablespace. This ensures data accessibility and performance, while reducing the storage footprint even further – with no additional burden placed on database administrators or storage management staff.

Similar to ADO data segment storage tiering ADO index segment storage tiering also operates at the segment level, so when an ADO policy implements storage tiering the entire segment is moved and this movement is one direction, meaning that ADO storage tiering is meant to move colder segments from high performance storage to slower, lower cost storage. If an ADO index optimization policy, and a storage tiering policy both qualify for execution, the database will execute both operations in a single segment reorganization step (similar to a data segment).

There are plenty of other new features in Oracle Database 21c to learn about as well.