Re: FIRST_ROWS(n) with nested queries

"Chris Richardson" <cer_at_acm.org> wrote in message news:ae723b11.0402081540.99d0006_at_posting.google.com...
> Oracle 9.2.0.1 executes the following query using nested loops:
>
> 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.
>
>
> Here is the plan for the first query:
>
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=23 Card=20
> Bytes=1900)
> 1 0 NESTED LOOPS (Cost=23 Card=20 Bytes=1900)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FTGO_ORDER' (Cost=3
> Card=200000 Bytes=17400000)
> 3 2 INDEX (FULL SCAN DESCENDING) OF
> 'FTGO_ORDER_EXT_ID_UNQ' (UNIQUE) (Cost=2 Card=20)
> 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'FTGO_RESTAURANT'
> (Cost=1 Card=1 Bytes=8)
> 5 4 INDEX (UNIQUE SCAN) OF 'FTGO_RESTAURANT_PK' (UNIQUE)
>
>
> Here is the plan for the second query:
>
> 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=20
> Bytes=260)
> 1 0 COUNT (STOPKEY)
> 2 1 VIEW (Cost=2 Card=20 Bytes=260)
> 3 2 SORT (ORDER BY STOPKEY) (Cost=12232 Card=200000 Bytes=
> 19000000)
> 4 3 HASH JOIN (Cost=264 Card=200000 Bytes=19000000)
> 5 4 TABLE ACCESS (FULL) OF 'FTGO_RESTAURANT' (Cost=2
> Card=1000 Bytes=8000)
> 6 4 TABLE ACCESS (FULL) OF 'FTGO_ORDER' (Cost=237
> Card=200000 Bytes=17400000)
>
> Do anyone know why Oracle is chosing this particular execution plan
> even though it is less efficient than the first?
> Thanks.

Well, at the risk of being horribly simplistic, it's choosing this particular execution plan because your hint forces it to do so. A hint possibly appropriate for one query can't be expected to be appropriate for a completely different query. What happens if you just let the optimiser do its thing, without intervention via hints from you?

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------