Re: FIRST_ROWS(n) with nested queries

Jaap W. van Dijk wrote:
>
> On 10 Feb 2004 14:06:04 -0800, cer_at_acm.org (Chris Richardson) wrote:
>
> ...
> >> >
> >> > SELECT /*+ FIRST_ROWS(20) */ o.*, r.name
> >> > FROM FTGO_ORDER o, FTGO_RESTAURANT r
> >> > WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
> >> > ORDER BY o.ORDER_EXT_ID DESC ;
> >> >
> >> > The JDBC client reads the first 20 rows from the ResultSet and the
> >> > performance is pretty good.
> >> >
> >> > However, when I changed the query to use ROWNUM to limit the number of
> >> > rows returned Oracle used a full table scan, and HASH JOIN followed by
> >> > an explicit sort:
> >> >
> >> > SELECT /*+ FIRST_ROWS(20) */ order_id
> >> > FROM (SELECT o.*, r.name
> >> > FROM FTGO_ORDER o, FTGO_RESTAURANT r
> >> > WHERE o.RESTAURANT_ID = r.RESTAURANT_ID ORDER BY o.ORDER_EXT_ID
> >> > DESC ) WHERE ROWNUM < 21;
> >> >
> >> > This query takes a lot longer than the first.
> >> >
> ...
>
> Dit you consider the INDEX_DESC hint? How does this work:
>
> SELECT /*+ INDEX_DESC (o FTGO_ORDER_EXT_ID_UNQ) */ o.*, r.name
> FROM FTGO_ORDER o, FTGO_RESTAURANT r
> WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
> AND ROWNUM < 21
>
> Jaap.

Hmmmm...and if your index ever gets dropped, or renamed, or is a not a candidate for use because of column nullability...then....ooppps...data corruption.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"