• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1018
  • Last Modified:

How do I test if an Index needs to be rebuilt or not?


Is there a way to tell if an Index needs to be rebuilt or not?

I would like to setup a nightly process to check an index and if the index meets a certain criteria, then rebuild the index.  What tables would I check to see if an Index needs to be rebuilt?

Thanks,
Troy
0
tdsimpso
Asked:
tdsimpso
  • 5
  • 2
1 Solution
 
jwittenmCommented:
In reality, indexes only rarely need to be rebuilt, and then only if you have reason to believe they are the cause of a problem.  Like water, they will seek their own level of comfort.  More important is analyzing them, and coalescing them if you have a lot of deletes from your index.
You can check the blevel in dba_indexes if you are determined to, but you will find that if it is higher than you expect and you rebuild it, it will rise to that level again and remain pretty constant.  In short, rebuild if you have reason to believe that your index is causing a problem.
0
 
jwittenmCommented:
Also, if you want to see more about your indexes, there is index_stats and v$segment_statistics.
0
 
tdsimpsoAuthor Commented:

Do you have any input about Oracle Text Context indexes?

Thanks,
Troy
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jwittenmCommented:
Ahhh.  No, I have to say I have very limited knowledge of Oracle Text.  Perhaps someone else will have more input.
0
 
tdsimpsoAuthor Commented:

That is okay.  I'll open another question for Oracle Text Indexes.

What happens to a Normal Index if I delete all the data in a table and refresh it?

OR

If I delete 25% to 50% of the data and reinsert it?

Thanks,
Troy
0
 
jwittenmCommented:
First of all, for text indexes, check ctx_user_pending for index values that have not yet been merged with the text index.

Second, it all depends on how the deletes are done.  If it's a FIFO type situation, the empty blocks are released back to the freelist for reuse.  If you delete a value here and there, you may end up with blocks that have space in them, but that space will be reused when new values are inserted.  To reiterate: Empty index blocks are reused.  Empty space in index blocks are reused (in most cases).  You can play around with your processes and the views listed previously, as well as: "alter session set events 'immediate trace name treedump level <index name>;' to see what your index actually looks like.  You may find documentation on reading the dump at oracle.technet.com, metalink, asktom.oracle.com,www.hotsos.com, www.jlcomp.demon.co.uk and www.ixora.com.au.
0
 
jwittenmCommented:
In addition to ctx_user_pending, try ctx_pending for all ctx indexes.  These views are owned by ctxsys.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now