Re: How to locate N'th row in a cursor efficiently ?

There is no way to determine the order of records returned by a SQL statement, unless you impose ordering.

Assuming that the you could add an ordering to the table via a column which uniquely identifies each row, one could then selectively select the next set without doing the first set..

However, since you are using a string -- I would assume that the SQL can be variable and such a scheme would be rendered useless eg the where clause excludes
some of the rows anyway.

Another plan is to create a temporary table (assuming that each user can be uniquely
associated with a specific table and name). Populate the table once.
Then return the current set of rows (n to m). This would allow very short row set eg 10 without added cost.

The major issue would be remembering to destroy the table.

One idea is to create at table with the user name and a time/day stamp in it. Another procedure could delete anything over some specificed time like an hour old. Look at the
dbms_job package for more information.

Mike Krolewski

Matthew wrote:

> Hello,
>
> In our project, I've written a stored procedure that can accept a SQL
> statement as a string, call the DBMS_SQL package to parse it and fetch the
> result set.
> Since this s.p. will be called by some server side program to produce a web
> page, it will return rows no. n to m (i.e. a segment of the cursor), in
> order to give the 'Next' & 'Previous' paging effect. This is because the
> result set could be more than 10K rows.
>
> Currently, I just repeatedly fetch the records until n, and then return the
> next m rows.
> My question is: It there any more efficient way to do this ? (e.g. any way
> to SKIP n rows instead of fetch n times...)
> I have thought of using the WHERE clause to avoid the unwanted rows.
> However, it can accept any SQL statement, it may contains different WHERE
> clause and different ORDER BY clause, therefore is very hard to modify the
> WHERE clause of the SQL statement...
>
> Any idea ???
>
> Thanks & Regards
> Matthew
Received on Sat Jun 13 1998 - 00:00:00 CEST