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.
