Re: SQL queries

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Tue, 12 Mar 2002 13:33:37 -0800
Message-ID: <F001.004262E6.20020312133337@fatcity.com>

> Robertson Lee - lerobe wrote:
>
> All,
>
> Following SQL runs for ages (almost 2 hours)
>
> select * from table1 addr,
> table2 pers,
> table3 lookup
> table4 cust
> where cust.customer_key = lookup_customer_key
> and lookup_address_key = addr_address_key
> and lookup.person_key = pers.person_key
> and rownum < 1000;
>
> when this is changed to
>
> select /*+ FIRST_ROWS */
> ADDR.*,
> PERS.*,
> LOOKUP.*,
> CUST.*
> from table4 cust,
> table2 pers,
> table3 lookup
> table1 addr
> where cust.customer_key = lookup.customer_key + 0
> and lookup.address_key = addr.address_key
> and pers.person_key = lookup.person_key + 0
> and rownum < 1000;
>
> this runs instantaneously. I realise that 99.99% of the improvement is
> down to the first_rows hint BUT, why does the SQL tool use the list of
> table aliases with .* after it AND what on earth are the + 0s' on two
> lines of the predicate list.
>
> Confused
>
> Lee
>
> PS. The Tool is SQLExpert brought to you by those nice blokes at
> cool-tools (Cheers Mark Leith !!) and is proving absolutely priceless
> here at the moment.
>

+0 is an oooooooooooooooooold way to tell Oracle 'thou shalt not use the

index on this column'. Written as it is, it instructs Oracle to do a full scan of the 'lookup' table (which is probably the shortest one). Also, /*+ FIRST_ROWS */ makes nested loops almost irresistible. You could probably have got the same results minus +0s with the /*+ ORDERED */ hint and by listing the tables in the FROM clause as

    from table3 lookup,

         ... < other tables in any order > ...

plus perhaps a couple of INDEX() hints.

You should run an EXPLAIN if you want the full picture.

This said, 'rownum < 1000' doesn't make much sense to me. I mean, when you return as many rows as 1000, you usually try to order them in a way or another, which is not the case here.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Mar 12 2002 - 15:33:37 CST