Which Oracle index is a good candidate for a rebuild

oracle-logoThis script determines whether an index is a good candidate for a rebuild or for a bitmap index. All indexes for a given schema or for a subset of schema’s are analyzed (except indexes under SYS and SYSTEM)
.
Instructions

Execution Environment:

Access Privileges:

Requires DBA privileges in order to be executed.

Usage:

Instructions:

Copy the script into the file ind_analyze.sql. Execute the script from SQL*Plus connected with a user with DBA privileges. The script requires to parameters:

  1. Name of the output file where the report will be generated
  2. Name of the SCHEMA to be analyzed.

Sample Output

Rebuild the index when:

  • Deleted entries represent 20% or more of the current entries.
  • The index depth is more then 4 levels.

Possible candidate for bitmap index:

  • When distinctiveness is more than 99%

Script:

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
The following two tabs change content below.

Anil Panda

Lead DBA
Anil is working as a DBA Lead in a reputated MNC. He loves to watch Cricket and WWE in his leisure time. He is always busy with helping others. Lastly he is a great fan of Mr. Sachin Tendulkar (Cricketer).

One thought on “Which Oracle index is a good candidate for a rebuild

Leave a Reply