From EShevtsov@flagship.ru Thu, 13 Sep 2001 12:28:41 -0700 From: "Shevtsov, Eduard" Date: Thu, 13 Sep 2001 12:28:41 -0700 Subject: Re: USE_NL with or without ORDERED Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: USE_NL with or without ORDERED 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
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 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. ++++++++++++++++++++ John -----Original Message----- From: Shevtsov, Eduard [mailto:EShevtsov@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> SQL> select name, value   2  from v$parameter   3  where name = 'optimizer_mode'; NAME ----------------------------------- VALUE ----------------------------------- optimizer_mode CHOOSE SQL> desc employees  Name                    --------------------  EMP_ID                  MGR_ID                  LAST_NAME               FIRST_NAME              HIREDATE                JOB                     SALARY                 SQL> desc courses  Name                    --------------------  CRS_ID                  SHORT_NAME              DESCRIPTION             DAYS                    DEV_ID                  CAT_ID                  LAST_UPDATE SQL> explain plan set statement_id = '37'   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 ------------------------------------------ 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 ----------------------------------------- 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: http://www.orafaq.com -- Author: Shevtsov, Eduard   INET: EShevtsov@flagship.ru 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@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). **********************************************************************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.**********************************************************************