Re: How to select first N rows?

Jacob Salomon wrote: > > Xavi Cheto wrote: > > > > Cosmo Lee <"* N O - S P A M * cosmo"@echonyc.com> escribi' en art'culo > > <347780AD.D83651DE@echonyc.com>... > > > Is there a way to select the first N rows from a table? > > > > > > i.e.: select the first 20 row from a table > > > > > > > Try : > > > > SELECT field FROM table t1 > > WHERE 20 > ( SELECT COUNT(*) FROM table t2 > > WHERE t1.field > t2.field ) > > ORDER BY field> > > > This selects the first 20 records of the following SELECT : > > > > SELECT field FROM table > > ORDER BY field> > > > Hope that helps. > > YIKES! > > It might give'm what he wants but at the cost of scanning the whole > blessed table to get the results of a query with a correlated subquery! > The more essential question might be: Is there a way to tell the engine > to return oly the first 20 (or N) rows of a query? Sally's 4GL solution > does not quite cut it, since by the time the app has gotten 20 rows from > the engine, the engine itself has internally set up for the first 87 (or > some other arbitrarily large count) rows. Thus, it fails as a time > saver, the primary reason for desiring such a feature. (Are you > listening right now, Art? ;-) > I have heard rumors that in 7.3 there is a syntax for telling the engine > to stop searching after finding the first N rows for a query. If > someone knows of an earlier release with this feature and/or the syntax > for this, please post it here. I think it was something like: > select first(20) column, column,.... from .. blah... If you mean the SET OPTIMIZATION FIRST_ROWS enhancement it is only meant to cause the optimizer to favor an index for selecting rows such that a sort using temporary tables is not needed. It is so that the first few rows are returned as quickly as possible. This breaks the pure cost optimization strategy which often, correctly, selects another index for filtering requiring a sort to satisfy UNIQUE/DISTINCT and ORDER BY clauses. It does no affect the number of rows returned. I will send out my spies and scouts, Jake, and see what other features I can report on (I am Beta testing 7.3 and under NDA, so I cannot speak about unannounced and unleaked features). BTW Latest bug: There is a bug discovered in 7.1x, 7.2x whereby the optimizer was left the option of filtering for the 2nd and following columns of a multi-part index using the index pages or by reading the data pages. The apparent thinking was that if the data pages had to be read anyway for some other filter it might be just as fast to ignore the index pages after the first key column. Sounds good, but, it turns out that the optimizer ALMOST ALWAYS reads the data pages to filter the 2nd+ key columns and RARELY uses the index pages for this without making any notation to that effect in the sqexplain file causing unexplained performance problems caused by unnecessary I/Os. This is fixed in 7.3, in part using the code added to support ORDERED MERGE and SET OPTIMIZATION FIRST_ROWS, to favor index filtering when available. I am lobbying for a back port of this fix to 7.24. Anyone interested in this back port please contact Informix Support and reference CASE #688753 (I neglected to get the BUG ID#). Art S. Kagel