| Rebuliding index issue [message #279357] |
Wed, 07 November 2007 22:04  |
kaustubh
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
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.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Rebuliding index issue [message #279444 is a reply to message #279357] |
Thu, 08 November 2007 08:10  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| kaustubh wrote on Wed, 07 November 2007 23:04 |
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.
|
Why would you want do that? What are you going to gain? A few couple of hundred megabytes or a few gigabytes? In a few days, you'll be back up to the same size. Add disk and let your database grow the way it was meant (after all, it is a database).
The amount of time wasted in time, effort, money and headaches in trying to do Obsessive Tuning Disorder (as anacedent calls it) is far outweighed by just letting the database be a database.
|
|
|
|
| Re: Rebuliding index issue [message #279709 is a reply to message #279357] |
Fri, 09 November 2007 12:47 |
kaustubh
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
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.
|
|
|
|
|
|
| Re: Rebuliding index issue [message #279718 is a reply to message #279357] |
Fri, 09 November 2007 13:18 |
kaustubh
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Sorry if I have used any wrong words. I am not a DBA.
I would like to know which one ("SHRINK SPACE" or REBUILD INDEX")according to you should be used?
Also are these two approaches used for the same purpose?
I would like to get your opinion on this.
|
|
|
|
| Re: Rebuliding index issue [message #279780 is a reply to message #279718] |
Sat, 10 November 2007 03:52 |
 |
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The purpose of these are different, they execute differently, I advice you to read or read again the documentation about both clauses with their many options.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
