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
