Re: Returning record X-Y from a table

"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 - 06:11:15 CEST