Re: use_hash HINT
/*+ use_hash(x) */ will force a hash join if the 'x' is the target for a hash join. This does not exclude 'x' from being the driver table in a nested or merge join. Typically an additional ordered predicate might be required to avoid the join inputs being swapped over. hth connor --- Pillai, Rajesh [EMAIL PROTECTED] wrote: Hi All, Please give me inputs on this hint and the affect of having a large HASH_AREA. Thanks, Rajesh = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
use_hash HINT
Hi All, Please give me inputs on this hint and the affect of having a large HASH_AREA. Thanks, Rajesh
RE: USE_HASH hint
Thanks for posting the results. I'm endlessly fascinated (and frustrated) by Optimizer behavior. Jay Miller x48355 -Original Message- Sent: Sunday, September 02, 2001 5:50 PM To: Multiple recipients of list ORACLE-L The problem with the USE_HASH hint is due to the order of the tables in the FROM clause. The hint was: /*+ ORDERED USE_HASH(c) */ But in the FROM clause the c table was listed first. Without the hint I got a Nested Loops, with the hint I got a Sort-Merge. But when I simply switched the order of the tables in the FROM clause I got the Hash Join. I am on 8.1.6 on Solaris, and tried several combinations to be sure this problem was related to the requirement that when you have two tables in the from clause, the one you specify in the USE_HASH hint must be listed last. Guess this behavior makes sense when you think about it. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 02, 2001 12:20 AM Does anyone know why a USE_HASH hint that's properly coded might not work? Without it my query uses nested loops, but with it the plan changes to sort-merge, not a hash join like I want. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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).
Re: USE_HASH hint
I find it interesting that the optimizer seems to be increasingly fussy about hints as time passes. Once upon a time, it used to be the case that hints could be very sketchy and you would get the result you wanted. Now it seems that your hints have to be very precise, or Oracle may find a path that allows it to ignore your hint. In a recent example I found that in: select /*+ use_nl(t2) *./ . . . from t1, t2 etc... the hint was ignored, and a hash join was used, but if I used the hints: /*+ ordered use_nl(t2) */ then the pair of hints was obeyed. My interpretation was that Oracle examined paths with the order (t2, t1) since I had not STOPPED it from considering different orders, and discovered that a hash join was a good option - then, having decided that my use_nl was 'wrong' with respect to its chosen path it ignored it, but then switched the join inputs to end up with my desired ordering, but rejected path. 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 [EMAIL PROTECTED] Date: 04 September 2001 22:30 |Thanks for posting the results. I'm endlessly fascinated (and frustrated) |by Optimizer behavior. | |Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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).
USE_HASH hint
Does anyone know why a USE_HASH hint that's properly coded might not work? Without it my query uses nested loops, but with it the plan changes to sort-merge, not a hash join like I want. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
Re: USE_HASH hint
The problem with the USE_HASH hint is due to the order of the tables in the FROM clause. The hint was: /*+ ORDERED USE_HASH(c) */ But in the FROM clause the c table was listed first. Without the hint I got a Nested Loops, with the hint I got a Sort-Merge. But when I simply switched the order of the tables in the FROM clause I got the Hash Join. I am on 8.1.6 on Solaris, and tried several combinations to be sure this problem was related to the requirement that when you have two tables in the from clause, the one you specify in the USE_HASH hint must be listed last. Guess this behavior makes sense when you think about it. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 02, 2001 12:20 AM Does anyone know why a USE_HASH hint that's properly coded might not work? Without it my query uses nested loops, but with it the plan changes to sort-merge, not a hash join like I want. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
