Sort & Subsetting in Oracle

Hello All,

I want a bit of advice. I am sure someone has done it before.

Background:

Most of our queries will require sorting followed by subsetting. Most often, subsetting involves returning the first N rows of a sorted query result (and is accompanied by user interface text like "showing 1 to 10 of 30 items"). Other, less common queries, will require returning something like N+1 to 2*N rows ("showing 11 to 20 of 30 items").

How we are doing:

In principle, this can be done in either the Oracle queries (so that every query has to be constructed to do sort & subset) or in the Tcl
(in

which case, we just get all the results from the query and sort and subset them with a single function call in Tcl).

Right now we do it in

Tcl: we return the full search results from Oracle to the web server, apply the sort criteria in Tcl, and then grab the required rows and display them. This is very inefficient when the user does a search that
produces a large number of found items because all the found items have to be returned through the connection between the database server and the
web server. This ties up database handles as well as network bandwidth.

In short, the implementation of sort-and-subset in Tcl is much easier, but I believe that performance issues will eventually force us to implement sort-and-subset in Oracle.

I have a couple of questions regd. this:

(1) I believe that we have had some trouble constructing queries that
correctly implement sort-and-subset. The problems are

(a) that we end up with subset-and-sort, which is not the same thing at
all,

and

(b) we can write a query that returns the first N rows, but not one
that
returns the N+1 to 2*N rows. Can you provide a sample of a query which correctly implements this functionality?

(2) Do you have any insight or opinions on the practical impact of
doing sort-and-subset in Oracle vs. Tcl?

It strikes me that this is a requirement that must be very common
(particularly for web-enabled databases). Does Oracle have any special
functionality tailored to this problem in particular?

Thanks in advance,

Santanu

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 08 2001 - 00:58:28 CST