June 8, 2005 at 4:13 pm
I have discovered that there is a serious flaw in the code of the reindex procedure that I have submitted in the Scripts section of this site. It will report that indexes have been rebuilt that weren't actually rebuilt. This is due to a misconception on my part that when a clustered index is rebuilt, all non-clustered indexes in the same table are also rebuilt. Problem is, this is only true for non-unique clustered indexes. I have submitted an updated version of the procedure. It is currently pending approval by the moderators of the site. If you are currently using this procedure, PLEASE watch for the new version and implement it in place of the current version asap! You'll be able to recognize the new version due to the comments placed at the top of the code, which start out as...
"NOTE: A colleague recently asked..."
I am very sorry for any inconvenience that this has caused.
Steve
June 9, 2005 at 12:13 pm
I was checking this script out (really neat script btw!) and came accross something odd. Of the 4 indexes that were called out as needing to be rebuilt, 2 of them were items that I could not find anywhere. Both had the format t as the name of the index, but neither table has an index of that name and no indexes with that name format were ever created to my knowledge. Also, I could not find this name in sysindexes, sysobjects or anywhere else I looked.
Any clues where or what these mystery indexes might be or where they came from?
Steve G.
June 9, 2005 at 12:40 pm
Did it start with _wasys??
Those are statistics, there is also another prefix that is used by the index tuning wizard but I forgot it.
June 10, 2005 at 6:05 pm
Nope. They all had the name of the table prefixed with a lower case 't'. Did a little more digging and found that the script actually pulled out about 30 of these (I have considerably more than 30 tables in the db). Interestingly, they all have an indexid of 255.
Steve G.
June 10, 2005 at 7:40 pm
Do you have text columns (just a hunch)?
June 15, 2005 at 9:59 pm
Sorry that I haven't responded. I've not been able to spend any time here lately. Remi has it. Indid 255 is an entry for tables that have text or image data.
Steve
edit -
This is actually documented in the code -
-- Build the command to populate the DBA_Index_Stats table. This is the table
-- that is used to determine which indexes should be rebuilt, and in what order.
-- Note:
-- indid = 0 = Heap
-- indid = 1 = Clustered index
-- indid = 2 - 249 = nonclustered index
-- indid = 255 is NOT an index, it is an entry for tables specifying that the table has text
-- or image column(s).
-- This procedure will ignore indid = 0 or indid = 255!
June 16, 2005 at 11:43 am
Thanks, Remi and Steve! Mystery solved.
Steve G.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply