Re: cluster index

"Marek Branecki" <m.branecki@pbg.pl> wrote: :I've got a table, create cluster index on some columns :then declare cursor and browse table with fetch next :and everything is ok, i can see my data in proper order. :Next I add some rows and when i browse new rows are :at end. I cannot set order declaring cursor because it's :for update. : :I use Informix SE 5.0 on ICL. :Can anybody help ? If Informix where to keep the cluster order at all times inserts would be very slow indeed. The clustering is done only when you execute the alter statement. Thereafter new rows are inserted at the end or in free space (due to later deletes) in the middle of the table. Also be aware that you *might* not even get the table in the clustering order right after an alter statement is executed (before new rows are inserted). This might happen due to different extents of your table beeing placed in non contigous space on your disk. As a matter of prinsiple you *never* get a data returned in any spesific order unless you include an order by in your select statement. This can't be emphasised enough. Anything else is due to luck and luck only (or unluck as you see it) It's an unfortunate problem with Informix databases that you can't do and order by and for update at the same time. (You can't do joins either when you spesify for update - a very primitive behaviour to say the least). If you must read in a spesific order you will have to do regular updates via the primary key. Remember to prepare the update statement and execute it to gain maximum speed. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org