Re: Informix Optimiser: Should I use it?

In article <4p2nrf$su6@cssun.mathcs.emory.edu>, Cheryl Kendricks <cherylk@prod1.jcdc.doleta.gov> writes >Good 2cents Sally!!! Avoid forcing the query with DUMMY stuff if at all >possible. Let the Engine do the WORK you paid ($$$$$$) for! Again version >is critical when you talk this subject, as well as, the DB DESIGN!! I agree. >} > This may seem like a strange question, but our software supplier >} > has told us that we must on no account update the database >} > statistics, as it will (a) make queries run more slowly, and >} > (b) make the rows return in the wrong order - they rely upon >} > the implied order of rows returned by their chosen index rather than >} > using "order by". >} > >} > They force Informix to use their chosen index by declaring a >} > dummy data column, with a standard value (always "0"), and putting >} > this at the start of the "where..." clause (eg. "where index_dummy1 >} > = "0" ...). The reason we mustn't run "update statistics" is that >} > the optimiser might decide to use a different index if it's allowed >} > to know how many rows are in each table. >} > >} > This means that they are effectively bypassing the Informix optimiser >} > by specifying their own access paths. >} > Sounds like very very very poor Software Engineering practice. >} >} I think you need a new Informix consultant! Sometimes it's worth >} ============================================================================ >} Sally Woolrich | This mail contains my personal >} sally@excelsis.demon.co.uk | views not those of my employer! Again I agree, who every said you can not run update statistics has clearly written an etermely crap piece of software. It sounds as if they are trying to get the rows back in the order they have been inserted into the database. You can do this under pre 7,1 versions of Informix by 'forcing' the optimizer to use no indexes and sequentially scan the table but a) it is unreliable as this is not documented behaviour for Informix b) depending upon how Informix lays out out the data it may or may not work. If it is OnLine and data is spread across multiple chunks and/or extents it will almost certainly fail. c) Under OnLine 7.00 and above with fragmented tables it wlll fail (OK it theory 0.01% of the time it might work but very few people are that lucky). So when you move Informix versions and/or move the database you will probably find that it no longer works. By then of course you software supplier may no longer be supporting the softwate.... SCREAN AT THEM LIKE MAD and if they argue and say it will work ask a) will it work under OnLine 7.1 with fragmentated table b) will it work if you move machines and have to unload/reload the database. If they still say it will work then note WHY they say it will work, e-mail me and I will try to prove their arguments false. I HATE bad software engineering as I have fixed so much of it in past including something similar to this (I put a serial field in the table and use that to order by) PS Can anyone for Informix that the next row you insert will get a higher serial value that the previous one inserted ? This sounds correct but I'm still not convinced as I have never seen it in documented anywhere. -- David Williams