!! Please do not post Off Topic to this List !!
Eduard,
Someone else commented on this as well a while back. This behavior is not
unexpected and is documented and explained in the docs. But, your example
doesn't exactly fit the scenario described in the manuals -- e.g. the CBO
chooses as the "driving", or "outer", table the table for which the USE_NL
hint was specified, thus rendering the hint "meaningless". Since your
example contains no constraining criteria other than a join between the two
tables, I am guessing that even though the table for which the USE_NL hint
is specified is the inner table in the plan, the CBO is thinking it's going
to return most of the rows from employee and goes ahead and does the FTS and
HJ. This doesn't exactly fit the reason the docs give for using ORDERED with
NL, but, it does seem like a logical approach, especially if one gets picky
about the meaning of "forced" -- more on that below.
Anyway, the 8i Tuning and Designing for Performance Guide includes the
following comment in the section on join operations:
"Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint.
Oracle uses these hints when the referenced table is forced to be the inner
table of a join, and they are ignored if the referenced table is the outer
table."
Note the comment "recommended". Also note the comment "forced". In your
case, employee isn't "forced" to be the inner table since the CBO "chose" it
as the inner table. I suppose it would only be considered to be "forced" to
be the inner table if the ORDERED hint was used. This is a slight change
from the 7.3 docs which stated:
"The USE_NL and USE_MERGE hints must be used with the ORDERED hint...."
Notice the use of "must" instead of "recommended". Anyway, I guess you
learned it is a good practice to include the ORDERED hint whenever using the
USE_NL hint. FWIW, I have been aware of this behavior for quite some time,
but, it wasn't until a couple of years ago that I stumbled across the
comment in the docs regarding the ORDERED hint in conjunction with USE_NL.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> Shevtsov, Eduard
> Sent: Thursday, September 13, 2001 7: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: Larry Elkins
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).
