RE: Hint for using a nested loop for an in subquery

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
for
> 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
>

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.13.24/514 - Release Date: 02/11/2006

--
http://www.freelists.org/webpage/oracle-l