Home » RDBMS Server » Server Administration » Rebuliding index issue (Oracle 9i)
Rebuliding index issue [message #279357] Wed, 07 November 2007 22:04 Go to next message
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 #279398 is a reply to message #279357] Thu, 08 November 2007 01:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

alter index index_name coalesce
Re: Rebuliding index issue [message #279403 is a reply to message #279398] Thu, 08 November 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Coalesce does not reduce the size of the index.
It just compact the data in the leaves.
The freed blocks are put in the free list and are not returned back to the tablespace.

Regards
Michel
Re: Rebuliding index issue [message #279404 is a reply to message #279357] Thu, 08 November 2007 02:14 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel I mean after coalesce can we resize by alter database datafile index_data_file resize ...
Re: Rebuliding index issue [message #279408 is a reply to message #279404] Thu, 08 November 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it but no more than previously to the index coalesce as index coalesce does not change index allocation.

Regards
Michel
Re: Rebuliding index issue [message #279444 is a reply to message #279357] Thu, 08 November 2007 08:10 Go to previous messageGo to next message
joy_division
Messages: 4641
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 Go to previous messageGo to next message
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 #279717 is a reply to message #279357] Fri, 09 November 2007 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I want to your opinions on this. Should I use "SHRINK SPACE" or REBUILD INDEX"?
>Which one is more effective?
What metric is used to measure "effectiveness"?
What do your benchmark test show YOU?
Re: Rebuliding index issue [message #279718 is a reply to message #279357] Fri, 09 November 2007 13:18 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64131
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
Previous Topic: Reading from a control file
Next Topic: Invalid objects and ORA-00942 error
Goto Forum:
  


Current Time: Wed Dec 07 12:35:45 CST 2016

Total time taken to generate the page: 0.12442 seconds