Returning record X-Y from a table

[news]

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.

[Maximus]

Re: Returning record X-Y from a table

"news" <jbfidelia@mult icorpora.cawrot e in message
news:VqxRa.2765 $124.13491@wagn er.videotron.ne t...

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.