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.

[Nicolas Payre]

"news" <jb*******@multicorpora.ca> wrote in message
news:Vq******************@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.
How do you in wich order the will be output since there is no ORDER BY in
your query??

But anyway the point is that if you want to avoid scanning the whole table
(to produce the rownum that you use) you should add column to your table,
say RECID, and assign a unique number to RECID with a sequence. Then if you
create a index on this column RECID, you will get your performance...
SELECT *
FROM table t WHERE
(ROWNUM BETWEEN 1 AND Y) AND
(whatever condition you want on your table) Thanks for your advice.

JB Fidelia.

[Maximus]

"news" <jb*******@multicorpora.ca> wrote in message
news:Vq******************@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.