Re: Hint for using a nested loop for an in subquery
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <kennaim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Nov 2006 22:17:31 -0000
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
----- Original Message -----
From: "Ken Naim" <kennaim@xxxxxxxxx>
To: "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, November 03, 2006 9:27 PM
Subject: 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@xxxxxxxxxxxxxxxxxx]
Sent: Friday, November 03, 2006 3:53 PM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
--
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
