Re: Search engine like behaviour for result sets?

From: Steven Franklin <steven.franklin_at_wcom.com>
Date: Wed, 28 Jul 1999 14:17:08 GMT
Message-ID: <379F1143.498AE3E6@wcom.com>

Jerry Gitomer wrote:

> Hi Ryan,
>
> You can do this using the MINUS operator in conjunction with
> WHERE rownum < n.
>
> SELECT * FROM <table> WHERE rownum < 21
> MINUS
> SELECT * FROM <table> WHERE rownum < 11;
>

This works until you want row 50000 to 50020. i.e. with very small queries this is an acceptable speed, but your re-running the query for every page. The first pages will be very fast, but as you try to page deeper into the query, paging will get slower and slower retrieving rows that your just going to ignore anyway. (i.e. rows 1 thru 49999).

> .
> As long as no one is adding rows to the table while a user is
> browsing it this will work. If a row is added while the user is
> browsing it may or may not be visible to the user. This is going
> to depend on where Oracle inserts the row. For example, if the
> user is now browsing the 9th block of the table and Oracle
> inserts a row in the first block the user won't see it.
>
> You could also build a temporary table with one entry per row
> consisting of the rowid and a sequence number. You could then
> set up a query that would select rows with rowids whose sequence
> number is between two values. If a row is added to the table
> after the temporary table is built the user won't see it.
>

Which is what I've done. If order is defined, and you can guarantee that new rows will appear at the end of the ordered list. Then you can just rerun your query with an additional clause to start after the last row, ignoring rowids already added to the temporary table.

select rowid
from <qry> new_rows,

        (select rowid
         from (<qry>)
         where <ordered_col> = max_ordered_col) existing_rows
where (ordered_col >= max_ordered_col) and
          (new_rows.rowid = existing_rows.rowid(+)) and
          (existing_rows.rowid is null)

order by ordered_col

>
> I am sure there are other ways of doing what you want, but
> these are the two I have used.
>
> regards
> Jerry Gitomer
>
> RyanO_at_aclweb.com wrote in message
> <7nmlqq$ljg$1_at_nnrp1.deja.com>...
> >Hi All,
> >
> >We have a database of products in Oracle and we need to place
> search
> >functionality within the project now. To make the results
> readable we
> >need to display them a set at a time, such as a search engine
> does.
> >
> >Our simple select procedure is as follows:
> >
> > Procedure RetrieveAllProducts(ProdCur OUT curProd) Is
> > Begin
> > open ProdCur for select * from Products;
> > End RetrieveAllProducts;
> >
> >This works well with our environment (Active Server Pages are
> used to
> >call this function from NT Server).
> >
> >We now need to limit the result set so that we can 'window'
> through
> >them a page at a time. For example, we want to say 'use the
> search
> >term "joe" and give the tenth through twentieth records'. If a
> user
> >decides to see more then we issues the same query, but from the
> >twentieth record onwards.
> >
> >We cannot guarantee the order in which the results are returned,
> or
> >that there is a column with an ID which we can use to filter.
> >
> >Any ideas?
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
Received on Wed Jul 28 1999 - 09:17:08 CDT