Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> USE_NL with or without ORDERED

USE_NL with or without ORDERED

From: Shevtsov, Eduard <EShevtsov_at_flagship.ru>
Date: Thu, 13 Sep 2001 04:38:55 -0700
Message-ID: <F001.0038CEC2.20010913044518@fatcity.com>

!! 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_at_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_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). Received on Thu Sep 13 2001 - 06:38:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US