RE: SQL queries

[Alec Macdonell]

I assume you are using Rules based optimizer and not the Cost based. In the Rules based world the structure of the SQL statement effect the path the optimiozer chooses. The second statement differs from the first in three ways, first the FIRST_ROWS hint, second the table1 addr is moved to the last table in the from clause and third "+ 0" are added to a few where conditions. The FIRST_ROW hint I have never used so I do not know its effect on the query but I would recommend adding it to the first and seeing the effect it has. Perhaps that alone is responsible for the speed increase. The move of addr to the last table is significant since all things being equal the Rules based optimizer will use this table to drive the query. The access to the lookup table now can only be indexed on the lookup.address_key since the other two references to it have the "+ 0" added to them column names. (If an index existed on lookup.customer_key referencing lookup.customer_key + 0 in a where clause would suppress using that index)

 

My guess looking at these queries the change of addr to the last table and the addition of the "+ 0" are the reason the query is running faster. In the first query the optimizer probably did full table scans on lookup and addr at least and perhaps on all the tables. While in the second query it started with addr only accessed lookup using the index on address_key they access cust using the customer_key (sounds like a PK to me) and pers using the person_key (again sounding like a PK).

 

To answer your own question lookup utlplan.sql in your ORACLE_HOME. This creates a PLAN_TABLE in the schema it is run. You can then run explain plan on each query. This will show you tghe indexes and paths the optimizer is going to use.

 

Alec

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Robertson Lee - lerobe
Sent: Tuesday, March 12, 2002 8:24 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL queries

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.