Re: large indexes

icc@injersey.com wrote: >When building an index, is there a way to following its progress in detai? >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? >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. 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. >Oh some background information. The tables in questions have a row size of >appromixately 650 bytes (please don't ask me why the table is so large), >and 35 million rows. Each table has four indexes ranging from 8 bytes >(excluing overhead) to 29 bytes (excluding overhead). We have very similar large tables, and we fragment tables and indexes in their own dbspaces. However, there is some info you didn't provide, such as your version of Informix, the platform, what type of disks, raw vs cooked, RAID level, striping, etc. I can tell you this much as to where we improved performance. Our engine is 7.14 running on Sun SparcCenter 2000e (10 cpus), Solaris 2.4. We used to have 8 gig of tempspace as specified by DBSPACETEMP. Building a unique index on a 60 mil row table to appx. 4 1/2 hours. We increased tempspace to 14 gig and the index build dropped to 1 1/2 hours. We also set PDQRIORITY very high during the index build - to a number like 90 or 100. I don't know if this will help your situation, but it might be a start. Barry Leb National Linen Service 1420 Peachtree Street MS #314 Atlanta, GA 30309 (404) 853-6119 (404) 853-6485 fax e-mail: barryleb@mindspring.com