Home » RDBMS Server » Performance Tuning » Post SHRINK Actions : REBUILD INDEX & CBO Stats ? (10gR2)
Post SHRINK Actions : REBUILD INDEX & CBO Stats ? [message #376247] Tue, 16 December 2008 10:44 Go to next message
Messages: 11
Registered: December 2008
Junior Member

I'm working on SHRINK feature implementation on 10gR2.
If this feature is now OK I have more questions about possible "Post Shinking Actions", ie :
1. Do I need to rebuild impacted (themselves or the underlying table) indexes ?
2. Do I need to re-gather CBO statistics ?

Browsing the web and some books allowed me to find different opinions about these points. Eg :
- http://www.squaredba.com/rebuild-indexes-gather-statistics-after-table-shrink-14.html
"Just a quick tip. When you finish a table segment shrink, it is a good practice to rebuild any indexes on that table as well as gather statistics on that table. These tasks help Oracle to create a better execution plan for the SQL query related to the table".
- http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-09-10.8045550726
"Unlike the ALTER TABLE....MOVE statement, indexes do not need to be rebuilt after the shrink operation is executed"

- J.Lewis in his CBO Fundamentals book seems "only" put warnings on SHRINK for CLUSTERING FACTOR.
- etc...

I cannot find a definite answer neither in docs nor on the web.
By nature I would tend to re-gather CBO statistics (SHRINK will likely change number of blocks, etc...) but not necessarily rebuild the indexes.
What is your opinion please ?
Thanks !
Re: Post SHRINK Actions : REBUILD INDEX & CBO Stats ? [message #376307 is a reply to message #376247] Tue, 16 December 2008 21:36 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I haven't investigated it, but I can answer theoretically:

In the process of shrinking a table, some rows will be migrated between blocks.

No rows are deleted and no rows are updated, so the keys stored in an index will not change; only the ROWIDs that they point to will change.

Rebuilding an index would help with fragmentation, but without updates or deletes, fragmentation will not be affected.

Since rows will have new neighbors, the clustering factor will certainly be affected. Especially tables where two or more rows with the same index key were inserted at the same time; previously adjacent rows may have been migrated to separate blocks, thereby lowering the clustering factor.

So I agree with Lewis, only some indexes will be affected, and then the DEGREE to which they will be affected will depend on the number of rows migrated and where they were migrated to.

I would recommend re-gathering statistics on indexes where:
- Two or more rows are inserted in the same transaction with the same index key.
- The index column is populated from the date inserted.
- The index column is populated from a Sequence.

Ross Leishman

Previous Topic: Histograms: 32 character limit generates inaccurate stats
Next Topic: SQL tuning
Goto Forum:

Current Time: Wed Jul 26 19:59:55 CDT 2017

Total time taken to generate the page: 0.09621 seconds