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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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.
**********************************************************************
