Re: FIRST_ROWS(n) with nested queries

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 11 Feb 2004 08:57:59 +0100
Message-ID: <oonj201l6a6pqb9ccedejgd4qj6e550spm@4ax.com>

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. Received on Wed Feb 11 2004 - 01:57:59 CST