FIRST_ROWS(n) with nested queries

From: Chris Richardson <cer_at_acm.org>
Date: 8 Feb 2004 15:40:07 -0800
Message-ID: <ae723b11.0402081540.99d0006@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. Received on Sun Feb 08 2004 - 17:40:07 CST