Hello Jan, the rowno predicate works BEFORE applying an ORDER BY predicate. See
http://www.sapdb.org/7.4/htmhelp/8c/ccce30c71c11d2a97100a0c9449261/content.htm for further details. This is not a bug. Regards Alexander Schr�der SAP Labs Berlin > -----Original Message----- > From: Jan Hlavat� [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 06, 2003 12:48 PM > To: [EMAIL PROTECTED] > Subject: [BUG!] Bad interaction between ORDER BY and ROWNO predicate > > > Hi, > > I'm attempting to come up with a query that returns a page of records > from a large ordered table, to allow paged view of a large table in a > given order. Basic idea is to get first 25 records beginning > with given > starting record in the table order. > > I have a compound index across all the fields of the ORDER BY clause. > Now what I was trying to do is to retrieve given number of records > following a specified record, using where caluse to limit records to > these that are >= starting record in the given ORDER. > Without any limits on number of records returned, correct records seem > to be retrieved in correct order, but all of them are returned (i.e. > from the staring record to the end of the table). > EXPLAIN shows my index is correctly used for that. > > Now I tried to limit number of records by adding AND ROWNO <= 25 to > WHERE clause, expecting to get just first 25 records returned by > previous query. > > BUT what i got is a random 25 records which are >= starting record! > This does not make sense. What use would ROWNO have if the records are > randomly chosen? > In addition, EXPLAIN plan shows strange things now - index not used, > full table scan, table is copied!? > All i did is add AND ROWNO <= 25 to preivously working WHERE clause. > > Looks like the ROWNO predicate is applied at a wrong time, before the > ORDER BY is taken into account. > In my opinion, it should be applied last, so that it limits the number > of records that would otherwise be returned to first n. > > How do I fix or workaround this? I need to apply the ROWNO predicate > after ORDER! Generating all the records and trowing away all but first > 25 of them is not acceptable (unfortunately I'm in an > environment where > I can't limit the number of rows returned from query on client). > > Oracle had optimizer hints to help with such situation, but I didn't > find anything like that on SapDB. > > I tried to create a view and then select from it using rowno but it > didn't accept ORDER BY in view definition. > > Tried on 7.4 beta on Linux. > > Jan > > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
