RE: USE_NL with or without ORDERED

From: Hallas John <John.Hallas_at_btcellnet.net>
Date: Thu, 13 Sep 2001 07:08:09 -0700
Message-ID: <F001.0038D30F.20010913071528@fatcity.com>

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




**********************************************************************
This email and any attachments may be confidential and the subject of
legal professional privilege.  Any disclosure, use, storage or copying
of this email without the consent of the sender is strictly prohibited.
Please notify the sender immediately if you are not the intended
recipient and then delete the email from your inbox and do not
disclose the contents to another person, use, copy or store the
information in any medium.
**********************************************************************

Received on Thu Sep 13 2001 - 09:08:09 CDT