Implementing ILM for Data Warehouse Database

Information Lifecycle Management (ILM) is the practice of applying policies for the effective management of information throughout its useful life. Implementing ILM become more beneficial in data warehouse environments, where we are required to keep historical data. Data Warehouse databases can grow exponentially and It makes complete sense to move data to different storage tier though its life cycle to save cost and availability.

Benefits of implementing ILM 

  1. Works at the database level with no application changes
  2. Reduces disk space requirements for all types of data
  3. Allows you to automate the movement of data to lower-cost storage based on activity patterns
  4. Lowers query times by keeping only active data on high performance storage
  5. Increases availability of critical information on the most reliable storage

How Implement ILM using Oracle database features  

  1. Use Oracle ADO to create policies for data compression and data movement between different compression level and different tiers of storage.
  2. Use Oracle 12c feature  Heat Map to track data usage
  3. Use Table Partitioning to move historical data to low performance storage tier
  4. Use Advance / HCC compression to compress historical data to same or low cost storage tier

Note : Oracle 12c introduce 2 new feature call Automatic Data Optimization (ADO)  and Heat Map, Both features are part of Oracle Advance Compression Option.