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.
**********************************************************************
