> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
