OraFAQ Forum: Performance Tuning » Shrinking index

Home » RDBMS Server » Performance Tuning » Shrinking index (Oracle 10.2.0.3,Windows server 2003 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shrinking index [message #531408]

Tue, 15 November 2011 22:24

eafaisal
Messages: 12
Registered: November 2011

Junior Member

Hi All,

We are having a table that is having 75 millions records.We purged 5 millions records from that table.Then we issued the command ALTER TABLE SHRINK SPACE CASCADE for that table.It was taking around 4 hours to complete.We are having 4 indexes on that table.Can anybody suggest the solution to reduce the duration?.

Thanks.

Report message to a moderator

Re: Shrinking index [message #531409 is a reply to message #531408]

Tue, 15 November 2011 22:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can anybody suggest the solution to reduce the duration?.
The duration can be reduced to ZERO by NOT issuing the SQL!
Yes, I AM serious if another similar purge will occur within this decade.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

Report message to a moderator

Re: Shrinking index [message #531412 is a reply to message #531409]

Tue, 15 November 2011 23:21

eafaisal
Messages: 12
Registered: November 2011

Junior Member

Hi,

There is no clear solution in your reply.

Report message to a moderator

Re: Shrinking index [message #531413 is a reply to message #531412]

Tue, 15 November 2011 23:23

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do NOT execute the statement. Is this not a clear "solution"?

Regards
Michel

Report message to a moderator

Re: Shrinking index [message #531417 is a reply to message #531413]

Wed, 16 November 2011 00:07

eafaisal
Messages: 12
Registered: November 2011

Junior Member

Then,how to reclaim the unused space?.Is there any other solution?.

Thanks.

Report message to a moderator

Re: Shrinking index [message #531418 is a reply to message #531417]

Wed, 16 November 2011 00:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Then,how to reclaim the unused space?.Is there any other solution?.

why does any space need to be "reclaimed"?

Report message to a moderator

Re: Shrinking index [message #531419 is a reply to message #531418]

Wed, 16 November 2011 00:17

eafaisal
Messages: 12
Registered: November 2011

Junior Member

The table is occupying a huge space.We want to reduce the size of it.That's why i tried to shrink after purging the records.

Thanks.

Report message to a moderator

Re: Shrinking index [message #531424 is a reply to message #531419]

Wed, 16 November 2011 01:30

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

5 out of 75, the most you can retirieve is 6.7%.
Is it worth to make a down plan for this?
Will there be no more inserts, never?

Regards
Michel

Report message to a moderator

Previous Topic:	index monitoring
Next Topic:	Simple inserts Slow (merged 6)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 16 22:47:19 CDT 2025