Re: force a query to an index

Hi,

I would like to use an "order by" command, but in this case I have to create an inner-select-statement to get only 3 records instead of all: select id,Nr from
 ( select id,Nr from table where Nr > 4711 order by Nr )  where rownum <= 3;

And this inner-select-statement is much slower than: select id,Nr from table
  where Nr > 4771 and rownum <= 3;

But these two select-statements only have the same result-set if in the second statement the index(Nr) is used.

Does anybody know a correct select-statement (with order by) which is as fast as the second one ?
Please let me know !!

Ralf

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<dKaca.82623$S_4.57235_at_rwcrnsc53>...
> He might be trying to get the data in a different order by asking Oracle to
> use the index to retrieve the data. If that is what he wants, the data in a
> particular order, then he should use an order by and not try and rely on
> unspecified behavior.
> Jim
>
Received on Fri Mar 14 2003 - 12:38:26 CET