Re: Failure of ordered select statements in Informix 4.10

In article <34C86BF2.37E5@staff.richmond.ac.uk>, Paul McManus <mcmanup@staff.richmond.ac.uk> writes >Can anyone help me sort out this mystery? > >We have a pretty large database containing all of our student data. It >comprises Informix-4GL, Informix-SQL and Informix-SE, version 4.10, >running under Ultrix version 4.2. Last Monday, for no obvious reason, it >started to have problems on select statements which contain an "order >by" statement. This seems to be limited to statements which it expects >to return a large number of rows (2 billion, according to "set explain" >- is it reasonable for it to expect that many?). Smaller ordered selects Yes, the value is usually totally wrong but the number of digits in the cost usually gives a guide as to how long the query will take. Cost = 10 fast Cost = 2000000 Slow! >are okay. Within 4GL programs, this sometimes seems to manifest itself >in the form of problems with array limits, even though there aren't >anywhere near enough records going into the array for that to happen. >Even worse, it seems that some programs select a different number of >rows each time they are run (sometimes every student on the system), >without reporting any errors. > >When running a select via the isql interface, a large one (eg, of the 2 >billion expected rows variety) will run nice and quickly without the >"order by", but stick the sort on and it seems to hang. The cursor drops >to the line with the select keyword, and stays there. No messages about >"Running...". And, it doesn't even start to build the temporary table - Isql does not give a Running message...it just sits there waiting for the query to complete. The order by means Informix has to sort the results which is what takes the time. >all it does is the first step of copying the full SQL program to the >temp directory. I have left a couple running overnight, but no joy. To >add to my woes, when the system is in this state, the SQL menu system >stops working and no one else can access the database. Probably because it is doing a lot of disk I/O. I assume from the mention of engine directories below you are using Standard Engine. Is $DBTEMP set in your environment. This points to where SE will create temporary files. Watch this directory and see what is in it. If it is not set look at /tmp. > >We have a test version of the database on a different partition of the >disk, and we get the same problem there, so I assume that it's not a >table problem. Interestingly, "set explain" also expects 2 billion rows >when looking at test data, even though there are a significantly lower >number of records. > >So far, I have attempted the following corrective action (in escalating >order of panic): > >- run "update statistics" >- rebuilt the indexes Run update statistics again AFTER rebuilding the indexes?? >- checked virtual memory and swapping (no problems that I can see) >- copied the engine directories to another disk partition and run it >from there >- reinstalled SQLEXEC from backup > >Has anyone ever had this problem, or does anybody have any suggestions >as to what my next course of action should be? > >All ideas gratefully received. > >Paul McManus (P.McManus@richmond.ac.uk) >Richmond, the American International University in London) Not Richmond next to Twickenham? I live in Teddingtion!! -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care