Re: FIRST_ROWS(n) with nested queries

cer_at_acm.org (Chris Richardson) 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 ;

Yup. It's a simple query and you're hinting for FIRST_ROWS(20), which (usually!) picks nested loops.

> 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:

Of course. Read on:
>
> SELECT /*+ FIRST_ROWS(20) */ order_id
> FROM

This:
> (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 )

is an unqualified query, with no predicates restricting the number of rows eligible and only an equi-join condition. Prime territory for a hash join optimization.
> WHERE ROWNUM < 21;
>
> This query takes a lot longer than the first.

Not surprised. It MUST retrieve all rows that satisfy the dynamic view join, then sort them, then optimize retrieval of the 20 first rows of the result...

Solutions? The INDEX_DESC is not bad, but God help you if you ever lose the index. Why not stick with the original?

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Feb 11 2004 - 18:39:22 CST