| Post SHRINK Actions : REBUILD INDEX & CBO Stats ? [message #376247] |
Tue, 16 December 2008 10:44  |
TomG
Messages: 11
Registered: December 2008
|
Junior Member |
|
|
Hi,
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
| Quote: | "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
| Quote: | "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 !
Tom
|
|
|
|
| Re: Post SHRINK Actions : REBUILD INDEX & CBO Stats ? [message #376307 is a reply to message #376247] |
Tue, 16 December 2008 21:36  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
