Re: Select a subset of records

On Oct 9, 11:53 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <totarope..._at_yahoo.it> a écrit dans le message de news: 1160407105.060051.141..._at_m7g2000cwm.googlegroups.com...
> | Hi,
> |
> | I'm an italian student and I've a problem with the selection of subsets
> | of records.
> | I do this selection with the following query:
> |
> | SELECT cols
> | FROM (
> | SELECT cols
> | FROM table
> | WHERE where_clause
> | ORDER BY order_clause
> | )
> | WHERE ROWNUM BETWEEN limit1 AND limit2
> |
> | I think that this query is not efficient!
> | Can you help me?
> |
> | Thank you.
> | Giuseppe
> |
> | P.S.: Scuse me for my terrible english!!!
> |
>
> Goto AskTom (http://http://asktom.oracle.com)
> Put "limit result set" in the search box
> Read the fifth (at this moment) item.
>
> Regards
> Michel Cadot

The link did not work for me but here is one to a related topic: http://asktom.oracle.com/pls/ask/f?p=4950:8:2521689219361910845::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064

Run an explain plan on the query. Efficient or not selecting from an ordered subset with a stopkey function is the only option when the ordered data desired selection criteria is not on an indexed value allowing an indexed range scan to be used to retrieve the data in the desired order.

How efficient these queries turn out to be in practice may surprise you.

HTH -- Mark D Powell -- Received on Tue Oct 10 2006 - 10:05:54 CDT