The +0 does disable the indexes and I agree with Iain I do not know why you
would disable them here.
Rick
"Nicoll, Iain
(Calanais)" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<iain.nicoll@cal cc:
anais.com> Subject: RE: SQL queries
Sent by:
[EMAIL PROTECTED]
03/12/2002 12:55
PM
Please respond
to ORACLE-L
Lee,
The +0 looks bizarrely like the old trick to stop indexes being used though
it would appear that here you wouldn't want to do this. Have you got the
explain plans and what version is it?
Iain Nicoll
-----Original Message-----
Sent: Tuesday, March 12, 2002 4:24 PM
To: Multiple recipients of list ORACLE-L
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.
TIA
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
