Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: FIRST_ROWS(n) with nested queries

Re: FIRST_ROWS(n) with nested queries

From: Chris Richardson <cer_at_acm.org>
Date: 10 Feb 2004 14:06:04 -0800
Message-ID: <ae723b11.0402101406.3ed2a6d9@posting.google.com>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4026cdd2$0$4249$afc38c87_at_news.optusnet.com.au>...
> "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?
>

It uses the same HASH JOIN based plan (with the same performance problems).

> Regards
> HJR
Received on Tue Feb 10 2004 - 16:06:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US