Re: large indexes

> When building an index, is there a way to following its progress in detai? If you run 'onstat -D' you can watch the pages read from each chunk. (You can also use this to watch the progress of ontape.) Obviously, after the reading of data pages is complete, the keys have to be sorted. See below. > Periodically we must build indexes on very large tables (+3.5 gigs, > fragmented). This takes hours. Sometimes it takes so long, that we become > suspicious and break the process before the index is completed. However, > when we do this we are in the dark. We don't know if something is > "wrong", or if the index is 90% done. oncheck -pe gives us some > information about the dbspace in qestion (oh, I forget to mention that > the indexes are always built in their own dedicated dbspaces), but its > obviouly not up-to-date or complete. Got any suggestions? I'm assuming you have a tempdbs large enough to build the indexes in question, as I haven't tried this trick when sorting uses Unix files in /tmp. Anyway, 'onstat -t' will show you a list of all open tablespaces. When the index is being built, tons of temporary tables are created to hold keys and fragid/rowids. These tables start out small (sometimes only 32 pages) and are each sorted. Then, the engine begins a process of merging these little tables two at a time. Watch as the number of tablespaces decrease and the size of each increases. Eventually, you will have two temporary tablespaces, sorted in key order, and the merging of these two is what builds the actual index. > Also, it appears that while an index is being built on a table the table > is exclusively locked and therefore unavailable to the users. Is this > just a fact of life or does anyone (a) know a way aroundt this or (b) > know how to speed up the buidling of indexes. Make sure that you have sufficient raw space allocated to your tempdbs. > I'll throw in a last > question. Is it worth fragmenting indexes, and if so, under what > circumstances? By fragmenting I mean the same as fragmenting data. I > don't mean letting the index be fragmented with the data, but instead > into its own dedicated fragments. I haven't run performance comparisons here, so I can't comment. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.