| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Hint for using a nested loop for an in subquery
If you want the subquery to be the target of a nested loop, the hint is NL_SJ, but you also said you wanted the subquery to be executed first - which means you want it as the first table in the join; if this is the case you put unnest in the subquery and use_nl(table1) in the main query
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> Thanks, I have used in this query but I am still getting a hash join or a
> filter instead of nested loops causing the query to run for minutes instead
> of 285 milliseconds, so what I need is hint that will tell the query to run
> nested loops against the in subquery?
>
> thanks
>
> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: Friday, November 03, 2006 3:53 PM
> To: kennaim_at_gmail.com; oracle-l_at_freelists.org
> Subject: Re: Hint for using a nested loop for an in subquery
>
>
> If you want to execute the subquery first, the hint
> you want is UNNEST in the subquery.
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: "Ken Naim" <kennaim_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> Sent: Friday, November 03, 2006 8:10 PM
> Subject: Hint for using a nested loop for an in subquery
>
>
>>I have used a hint in the past to force a query to use a nested loop join >> for a sub query however the name of the hint escapes me. I have googled
>> it unsuccessfully. Anyone remember it offhand? >> >> Query looks like >> >> Select something >> From table1 >> Where somethingelse in (select somethingelse from table2 where >> somethingelse2 between x and y) >> >> The ideal plan would evaluate the subquery first then use nested loops >> against an index belonging to table1. I have tried using the no_unnest, >> various index and use_nl hints unsuccessfully. >> >> Thanks, >> Ken >>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 03 2006 - 16:17:31 CST
 |
 |