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.

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.
>
>
>
Received on Fri Jul 18 2003 - 07:00:54 CEST