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