From jonathan@jlcomp.demon.co.uk Mon, 17 Sep 2001 03:46:09 -0700 From: "Jonathan Lewis" Date: Mon, 17 Sep 2001 03:46:09 -0700 Subject: Re: USE_NL with or without ORDERED Message-ID: MIME-Version: 1.0 Content-Type: text/plain This is perfectly reasonable (assuming you are still following my example) By instructing Oracle that joins INTO both C and E should be constrained to nested loop joins, you have stopped Oracle from calculating E -> C using a hash join, and therefore stopped it from finding the hash join with swapped sides. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -----Original Message----- To: Multiple recipients of list ORACLE-L Date: 17 September 2001 11:03 |Jonathan, | |If you haven't droped your test tables yet could you try /*+ USE_NL(c e) */ |Yeah, sounds strange, but... | |SQL> select /*+ use_nl(c e) */ | 2 e.first_name | 3 , e.last_name | 4 , c.short_name | 5 from courses c,employees e | 6 where e.emp_id = c.dev_id | 7 / | |1018 rows selected. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: jonathan@jlcomp.demon.co.uk 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@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).