• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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