Re: Returning record X-Y from a table
Date: Fri, 18 Jul 2003 11:48:10 -0600
Message-ID: <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.
"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:48:10 CEST