Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Rebuild index issue in Oracle 9i

Rebuild index issue in Oracle 9i

Hi All,

We have found that index datafile size in our database has become very large. We are using Oracle 9i
We are trying to rebuild the index and then resize the index datafile. But it's not of much gain. The index data file size has reduced by very low margin.

We know that dropping and recreating indexes is an option. But we want to know if there's any other better way by which we can reduce the index size.

Please let us know.

Thanks and regards,
Kaustubh Kane.
6 REPLIES
Murat SULUHAN
Honored Contributor

Re: Rebuild index issue in Oracle 9i

Hi Kaustubh

Please look at OracleMan's blog about index rebuilding.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112

Best Regards
Murat
Murat Suluhan
Kapil Jha
Honored Contributor

Re: Rebuild index issue in Oracle 9i

may be ur database is very heavy.....in this case no chance of reducing....
if index rebuild is taking time you can rebuild index online.
Otherwise,recreate....
BR,
kapil
I am in this small bowl, I wane see the real world......
Arturo Galbiati
Esteemed Contributor

Re: Rebuild index issue in Oracle 9i

Hi,
instead of drop and create the index (this inavalidate teh index) you can move the index into another tablespace and move back to the original one. This will compact the index. You could some put same parametyer druing to move to chnage the block size, pct free and so on.
Take care top have a tablespace greater them the index size *2 to avoid problem with the space.
If you want to go ahead with this solution (I used it several times) I can provide you script to do this in an auatomatic way.
Let me know ...

HTH,
Art
Hein van den Heuvel
Honored Contributor

Re: Rebuild index issue in Oracle 9i

If you are really interested in these matters then be sure to read the referred Ask-Tom article, and specifially the presentation it references by Richard Foote:
http://www.actoug.org.au/Downloads/oracle_index_internals.pdf

The index size might just be what is needed, or what it will become again over time anyway.
- Consider leaving it alone unless the space it totally unreasonable (more than 3x what is needed?)
- Verify the FREE SPACE in the index
- Consider alter index coalesce;

Arturo>> move the index into another tablespace and move back to the original one

What does 'move' mean? drop + create?
Why 'move'it back? If a new one is created, then why not use it where it is?

Arturo>> You could some put same parametyer druing to move to chnage the block size, pct free and so on.

Now that is better advice. If one decides that rebuilding an index is warranted and worth the effort and aggravation, then do it properly and use the opportunity to select the appropriate options. If you don't know how to to that, then you might not want to rebuild in the first place.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting




Re: Rebuild index issue in Oracle 9i

Hi All,
Thanks a lot for so many replies.

Finally I achieved it. After some search we found that we were not using DEALLOCATE UNUSED.
After using it along with ANALYZE and REBUILD we were able to reduce the size of index datafile.

We achieved this in Oracle 9i.

Now we want to the same thing in Oracle 10g. On doing some search i found that in 10g "SHRINK SPACE" is used to do a similar job that "REBUILD INDEX" does.

I want to your opinions on this. Should I use "SHRINK SPACE" or REBUILD INDEX"?

Which one is more effective?

Could you please let me know.

Thanks and regards,
Kaustubh Kane.
HGN
Honored Contributor

Re: Rebuild index issue in Oracle 9i

Hi

You have assigned points to only 1 out of the total 53 responses received for your various, it is nice to assign points to peopel who help, read the forum guidelines on assigning points.

Rgds

HGN