Re: First N records in SELECT

Slavik Anosov wrote: > > Does anybody know something in Informix ODS 7.1 that perform functions of > Oracle's ROWNUM pseudo-column? I need to retrieve only first N records in > select. > No, there is no such feature. Assuming that you are using ESQL-C or 4GL you can simply close the CURSOR after fetching the appropriate number of rows. If your problem is that you only want 'N' rows back from dbaccess you are out of luck. You'll need to write a 4GL or ESQL-C application. If your problem is really that a sort is taking too long to return the first few rows, read on. There is a new feature being planned for R7.3, a SET OPTIMIZATION command (dare I say hint?) to cause the optimizer to favor a query path which would get the first 'N' rows in the least time rather than optimize for total cost. If your problem is the same as the one which caused us to lobby for this new feature the solution is coming. For now you can try reducing optimization levels to MEDIUM only (not the suggested scheme) or even DROP DISTRIBUTIONS which will force the optimizer to behave like 5.0x and use the existing indexes. (Of course this assumes that an index exists that matches your ORDER BY clause.) Bloomberg lobbied for this enhancement because we normally only want the first 15-20 rows of a query returning thousands of matches. The normal optimization causes an index to be selected which does not match the ORDER BY clause, even though such an index exists, forcing a sort. While 5.0x chose the "CORRECT" index 7.xx correctly chooses the "WRONG" index. The total time for the query, fetching all 10,000 rows, is 13 seconds using the "WRONG" index and sorting. The total time, for all 10,000 rows, using the "RIGHT" index and no sort is 41 seconds. So the optimizer is doing the right thing, it is just that doing the sort causes the first 18 rows to be returned in 12.5 seconds while using the other index and not sorting causes the first 18 rows to be returned in under one second! [Interestingly reducing statistics to MEDIUM on this table caused a third index to be used, still requiring a sort but returning all rows in 14 seconds with the first 18 rows showing up in 13.5 seconds, DAMN BUT THAT OPTIMIZER IS (too damn) SMART!] Our applications have an enforced timeout in 10 seconds after a user request so we have had to DROP DISTRIBUTIONS on this table to prevent the sort (the other indexes are needed for other queries). This new optimizer goal setting feature will solve this problem permanently and transparently by forcing the optimizer to evaluate only the cost of returning the first (few) row(s). I do not know what the final syntax will be but we will be able to surround these troublesome queries with something like: EXEC SQL SET OPTIMIZATION FIRST FETCH; ... ... EXEC SQL SET OPTIMIZATION TOTAL COST; Art S. Kagel