In Version 8 through Version 12, does a REORGANIZE TABLE statement that does not specify an index (no PRIMARY KEY, FOREIGN KEY or INDEX clause) effectively reorganize ALL the indexes on that table while it is deleting and reinserting the rows?
If not, what effect does it have on the indexes? Does it just reorganize the clustered index, or if there is no clustered index does it just reorganize the primary key index?
Part of me says "yes, it reorganizes all the indexes, how could it avoid doing that?" ...but I don't know how iAnywhere Engineering accomplishes many wondrous things :)
Whatever the answer is, the Help should explain it: http://dcx.sybase.com/index.html#1201/en/dbreference/reorganize-table-statement.html
I have done a test, I looked at sa_index_density for a table and reorganized it. All indexes changed most have afterwards less leaf pages, but some got even more after the reorganize.
So to me it seems, that all indexes are reorganized too, not only the table pages.
answered 09 Jun '11, 08:35