RE: USE_NL with or without ORDERED

!! 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
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: elkinsl_at_flash.net

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