Re: USE_NL with or without ORDERED

From: Shevtsov, Eduard <EShevtsov_at_flagship.ru>
Date: Thu, 13 Sep 2001 12:28:41 -0700
Message-ID: <F001.0038DC2A.20010913123024@fatcity.com>

Hi John,
 
thanks for the explanation. I think you are right in general, but do you think
it's a little out of logic. I have two tables 'a' and 'b'. If I point
the table 'b' as an inner table for N-L join with the hint USE_NL(b), what is
the table 'a'? Wouldn't it be a driving table in *N-L* ?
 
Regards,
Ed
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   Picked this up on metalink - seems

  to cover your case well +++++++++++++++++ 

  Here is a good excerpt from Development on hints and the   CBO... Query hints are used to restrict the number of   alternative execution plans the optimizer has to choose from. The optimizer   will still pick the cheapest plan from all of the alternatives considered. So   for example, you could get a situation where the optimizer picks a   

  plan which does not contain a nested-loops join even though   your query specified a USE_NL() hint. However, by   combining hints you can restrict the optimizers search   space to a single plan if you wish. For example the   query select /*+ ordered use_nl(b) */ a.x from a, b   where a.y = b.y; will only consider the plan "a NL b"   because the combination of hints limits the search   space to this single alternative. So if you have a <FONT   size=2>query for which you want to fix the execution plan, you may need to   use a combination of hints to restrict the search   space to a single possibility. <FONT

  size=2>++++++++++++++++++++ 

  John
  -----Original Message----- From:
  Shevtsov, Eduard [<A
  href="mailto:EShevtsov_at_flagship.ru">mailto:EShevtsov_at_flagship.ru]   Sent: 13 September 01 13:45 To:
  Multiple recipients of list ORACLE-L Subject: USE_NL   with or without ORDERED
  !! Please do not post Off Topic to this List !!Hi List,   

  did anybody notice that sometimes CBO ignores USE_NL hint   without ORDERED
  The following example was taken from oracle docs:   SQL> SQL> <FONT
  size=2>SQL> select name, value   2  from   v$parameter   3  where name =
  'optimizer_mode';
  NAME <FONT

  size=2>----------------------------------- <FONT 
  size=2>VALUE <FONT 
  size=2>----------------------------------- <FONT 

  size=2>optimizer_mode CHOOSE
  SQL> desc employees <FONT

  size=2> Name                   
   -------------------- <FONT 
  size=2> EMP_ID                 

<FONT

  size=2> MGR_ID                 

<FONT

  size=2> LAST_NAME             
<FONT

  size=2> FIRST_NAME            
<FONT

  size=2> HIREDATE              
<FONT

  size=2> JOB                    

<FONT

  size=2> SALARY                 
  

  SQL> desc courses <FONT

  size=2> Name                   
   -------------------- <FONT 
  size=2> CRS_ID                 

<FONT

  size=2> SHORT_NAME            
<FONT

  size=2> DESCRIPTION           
<FONT

  size=2> DAYS                   

<FONT

  size=2> DEV_ID                 

<FONT

  size=2> CAT_ID                 

   LAST_UPDATE
  SQL> explain plan set statement_id = '37' <FONT   size=2>  2  for   3  select /*+
  ordered use_nl(e) */  
  4         e.first_name
    5  ,     
  e.last_name   6 

  ,      c.short_name   
  7  from  courses c, employees e   
  8  where  e.emp_id = c.dev_id   9  

  ;
  Explained.
  SQL> @opt\explain_n  statement
  id: 37
  Query Plan <FONT

  size=2>------------------------------------------ <FONT 

  size=2>SELECT STATEMENT   Cost = 1022     NESTED LOOPS     TABLE ACCESS FULL
  COURSES     TABLE ACCESS BY INDEX ROWID   EMPLOYEES       INDEX UNIQUE
  SCAN EMP_PK
  SQL> ed Wrote file
  afiedt.buf
    1  explain plan set statement_id = '38'     2  for   3 
  select /*+ use_nl(e) */  
  4         e.first_name
    5  ,     
  e.last_name   6 
  ,      c.short_name  
  7  from  courses c, employees e   8*
  where  e.emp_id = c.dev_id SQL> /
  Explained.
  SQL> @opt\explain_n  statement
  id: 38
  Query Plan <FONT

  size=2>----------------------------------------- <FONT 

  size=2>SELECT STATEMENT   Cost = 84     HASH JOIN     TABLE ACCESS FULL
  COURSES     TABLE ACCESS FULL
  EMPLOYEES
  QUESTION: why does the CBO ignore USE_NL without ORDERED ?   

  Regards, Ed --
  Please see the official ORACLE-L FAQ: <A target=_blank

  href="http://www.orafaq.com">http://www.orafaq.com <FONT 
  size=2>-- Author: Shevtsov, Eduard <FONT 
  size=2>  INET: EShevtsov_at_flagship.ru 

  Fat City Network Services    -- (858)   538-5051  FAX: (858) 538-5051 San Diego,   California        -- Public Internet access   / Mailing Lists <FONT

  size=2>-------------------------------------------------------------------- 

  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).
<FONT

  size=3>**********************************************************************This 

  email and any attachments may be confidential and the subject oflegal   professional privilege. Any disclosure, use, storage or copyingof this   email without the consent of the sender is strictly prohibited.Please   notify the sender immediately if you are not the intendedrecipient and   then delete the email from your inbox and do notdisclose the contents to   another person, use, copy or store theinformation in any

  medium.**********************************************************************

Received on Thu Sep 13 2001 - 14:28:41 CDT