Re: Index fragmentation

About "create index...online" Copied from Article on IDS Support site : http://www-01.ibm.com/support/docview.wss?rs=630&context=SSGU8G&context=SSZ2HS&context=SSP6X2&context=SSVHPS&context=SSHPYE&q1=create+index+online&uid=swg21219742&loc=en_US&cs=utf-8&lang=en " Explanation of the create index online process During a parallel index build, IBM® Informix® Dynamic Server™ reads data pages from the table and creates an index item for each row. The Pre-image copies of the data pages being updated are stored in a separate 'pre-image partition', one in each dbspace that contains a table fragment. A pimage thread is then started to copy the pre-images of these modified data pages. Once the index build is complete, the pre-image thread will terminate. A checkpoint is done to mark the beginning of pre-imaging for the table that the index is built upon. It will also mark the start of the updator logging: all the updates done on the table are logged to the updator log when the online index creation is in progress. The plog thread will be started to process the updater log requests, which manage the storing of updates into a temp partition as well as applying changes to the index after completion. Each thread applies the updates back to the index for the fragment of which they are responsible. " I already executed some tests on IDS 11.10 and monitor how works the create index online and found just a one problem , If your ONLIDX_MAXMEM are to small  and your updates (in parallel) on the table alter a mass of data bigger then you set to memory , this buffer are "swapped" to dbspace where the fragment index exists , if this occur to much times, at the end you will have a index with a lot of extents and if your dbspace don't have space enough to full index + ulog buffer , the create index will abort ... During execution of the index creation the extents will appear something like this (looking with oncheck -pe , ONLIDX_MAXMEM= 5120Kb , pg size = 2k):   table                 offset size   -------             -------- ---------   table_xyz.indexA         100 100   ulog_010043              200 2560   table_xyz.indexA        2760 500   ulog_010043             3260 2560   table_xyz.indexA        3820 350 After the create index finish will stay like this   table                 offset size   -------             -------- ---------   table_xyz.indexA         100 100   free                     200 2560   table_xyz.indexA        2760 500   free                    3260 2560   table_xyz.indexA        3820 350 The same behave will occur with the tables extents because the Pre-Images (pimage threads/buffers) are save on the table dbspaces/fragments... And to finish , at the end of create index a exclusive lock are necessary on table , so this is the reason to *always* use SET LOCK MODE TO WAIT on the session where execute the create index. I don't tested with IDS 11.50 to check if some behave changed.... but must be careful to use the CREATE INDEX ONLINE. My opnion , this "swap" should never occur on the same dbspace of the index/table, we have temporary dbspaces for this situations... this is a big defect... Cesar --- Em ter, 9/6/09, Richard Kofler <richard.kofler@chello.at> escreveu: De: Richard Kofler <richard.kofler@chello.at> Assunto: Re: Index fragmentation Para: informix-list@iiug.org Data: Terça-feira, 9 de Junho de 2009, 18:37 Neil Truby schrieb: > IDS 10.0FC9 on Solaris 10 > > I have a site where an index has hot the 16g limit for a tablespace. > It seems from the fine manual that you cannot fragment an index across > dbspaces round-robin (I suppose this is meaningless), only by > expression. But I don't really want to do this; I just want to get > around the 16g limit. Any ideas? > > Is it true that the "online index build" facility in IDS 10 is actually > nothing of the sort? > > thx > N Hi Neil, I must admit I did not fully understand what you mean with the last sentence, but here goes: CREATE INDEX ... ONLINE and DROP   INDEX ... ONLINE we do not recommend, and therefore I never did sufficient testing. I think (pls, whoever knows more about it, do correct me if I am wrong!) it is a feature for small indexes. Read about the env variable ONLIDX_MAXMEM, and see the default ... I *think* one cannot use memory under control of the MGM, aka PDQ memory, when using the 'ONLINE' keyword. The difference is, that it does not put an exclusive lock on the table, whereas a normal CREATE INDEX does need exclusive locking. I did not test what happens with an update changing values of the column which is used in the index. The SQL syntax docs V10 has some info on SET LOCK MODE TO WAIT, deadlock resolution and DROP INDEX. So there *are* concurrency and race condition effects. After seeing this back in 2007, we decided not to test it until there is need. But never was. To test things like this is definitely not an easy task for a rainy sunday afternoon! dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com