- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Rebuild index issue in Oracle 9i
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-07-2007 03:06 PM
тАО11-07-2007 03:06 PM
Rebuild index issue in Oracle 9i
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-07-2007 04:57 PM
тАО11-07-2007 04:57 PM
Re: Rebuild index issue in Oracle 9i
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-07-2007 05:08 PM
тАО11-07-2007 05:08 PM
Re: Rebuild index issue in Oracle 9i
if index rebuild is taking time you can rebuild index online.
Otherwise,recreate....
BR,
kapil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-08-2007 07:54 PM
тАО11-08-2007 07:54 PM
Re: Rebuild index issue in Oracle 9i
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-09-2007 12:59 AM
тАО11-09-2007 12:59 AM
Re: Rebuild index issue in Oracle 9i
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-09-2007 05:46 AM
тАО11-09-2007 05:46 AM
Re: Rebuild index issue in Oracle 9i
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-09-2007 07:16 AM
тАО11-09-2007 07:16 AM
Re: Rebuild index issue in Oracle 9i
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