RE: USE_NL with or without ORDERED

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Thu, 13 Sep 2001 10:34:35 -0700
Message-ID: <F001.0038D925.20010913103522@fatcity.com>

Hi Ed,

how many records are in the tables?

Hash join is favored when there aren't so many records and there's enough memory to slam together the result set. 

I think you aren't seeing typical behavior.  Try this on much larger tables and see what happens. 

ORDERED and USE_NL can work together.  I've done it.

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117

-----Original Message-----

From:   Shevtsov, Eduard [SMTP:EShevtsov_at_flagship.ru]
Sent:   Thursday, September 13, 2001 8:45 AM
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_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 - 12:34:35 CDT