Database Administration (MOSC)

MOSC Banner

Identifying and removing unused indexes

Hi all, I'm planning to identify and remove unused indexes from my production databases in order to reduce the database sizes.

This would be my first time performing this activity & I did some google around and found some guide online on how to do it. I gone through and summarize the steps as below.

  1. Identify top 100 largest indexes & turn on monitoring.
  2. Leave it for couple of months with monitoring on.
  3. Check the indexes to see which of the indexes are used or not used.
  4. Turn off monitoring for indexes which are used.
  5. Check those unused indexes for constraints.
  6. Set the unused indexes to invisible for a few weeks/months.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center