Oracle 23c New Features for DBA'sStarting 20c, one do not need to bother about index rebuilding, shrinking, managing them. We can automate this using feature Automatic Data Optimization option + marking the indexing to certain tier/policies.
The optimization process includes actions such as compressing, shrinking, or rebuilding the indexes:
- Compress: Compresses portions of the key values in an index segment (~3 times)
- Shrink: Merges the contents of index blocks where possible to free blocks for reuse
- Rebuild: Rebuilds an index to improve space usage and access speed
How to get started
SQL> alter system set HEAT_MAP = ON;
And there are two options ,
ADD POLICY TIER in order to perform the operation on a say low cost/ tier 2 tablespace when tier 1 storage is under space pressure
ADD POLICY OPTIMIZE in order to kick off the process after a certain number of days passes without accessing the index
Some examples
- Alter index index1 on schema.table(col1) ILM add policy optimize after 10 days of no modifications;
- Alter index index1 ILM add policy tier tablespacename;
- Alter index index1 on schema.table(col1) ILM add policy optimize after 5 days of no access;
License required for Advanced compression option and ADO
Follow Me!!!