RE: SQL queries

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: root_at_fatcity.com [mailto:root_at_fatcity.com]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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alec Macdonell
  INET: amacdonell_at_usscript.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).