Re: Returning record X-Y from a table

"news" <jbfidelia_at_multicorpora.ca> wrote in message news:6FURa.15074$124.178058_at_wagner.videotron.net...
> That will probably only work in the is never or rarely updated. In the
 case
> records are added an deleted in the table will you have to regenerate the
> sequenced
> field each time the table is modified ? And what if you use differents
> sorting criteria, do you need a sequenced field for each ORDER BY clauses
 ?
>
> thanks.

In a nutshell, you want field(s) in your ORDER BY clause to be indexed, that way queries will use the indexed data to return a subset and not have to process the entire record set. Any select based on a non-indexed field typcially requires processing the entire record set.

>
> "Maximus" <qweqwe_at_qwqwewq.com> wrote in message
> news:DrKRa.451401$3C2.12311562_at_news3.calgary.shaw.ca...
> > "news" <jbfidelia_at_multicorpora.ca> wrote in message
> > news:VqxRa.2765$124.13491_at_wagner.videotron.net...
> > > Hi there,
> > >
> > > Looking for a way of returning the Xth to Yth records of an Oracle
 database
> > > I thought using ROWNUM. Here is the solution I came up with.
> > >
> > > SELECT * FROM
> > > (
> > > SELECT
> > > ROWNUM RECID,
> > > t.*,
> > > FROM
> > > table t
> > > WHERE
> > > (ROWNUM BETWEEN 1 AND Y) AND
> > > (whatever condition you want on your table)
> > > )
> > > WHERE
> > > RECID >= X
> > >
> > > It work great for me so far, but my tables aren't very big (a few
 thousands
> > > records).
> > >
> > > Is anybody aware of any kind of issues using this query as the table
 get
> > > bigger and, if yes, is there any way to optimize this. (They are
 history
> > > tables and will definitely get huge at some point).
> > >
> > > I assumed that as this query is processed entirely on the Oracle
 server
 the
> > > performance sould be better than if I had queried the top Y records
 and
> > > filtered out the top X records on the client side.
> > >
> > > Thanks for your advice.
> > >
> > > JB Fidelia.
> >
> > Your above query is not very efficient, ROWNUM is a pseudo column so it
> > means querying the entire database (at least up to "Y" records) every
 time
> > to retrieve a subset. For example, supposing you had 100,000 records in
> > your table and you wanted to retrieve just the last 5, your query would
> > require scanning 99,995 records just to get to the first retreivable
 record.
> > As the previous poster mentioned, it's better to create a unique,
 indexed
> > field and assign it a value from a sequence, and do your queries based
 on
> > this field instead of ROWNUM.
> >
> >
>
>
Received on Fri Jul 18 2003 - 19:55:58 CEST