| 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
Hmm, is a merge join okay enough? I mean if you really want to use a sledge
hammer to evaluate the table2 subquery first you can do something like this:
SQL> r
1 select something
2 from (select t2.t2rownum, t1.something 3 from table1 t1, 4 (select rownum t2rownum, somethingelse 5 from table2 6 where somethingelse2 between '2' and '4' order by somethingelse) t2 7* where t1.somethingelse = t2.somethingelse) x
SOMETHING
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=3 Bytes=63) 1 0 MERGE JOIN (Cost=6 Card=3 Bytes=63)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (TABLE) (Cost=
2 Card=5 Bytes=70)
3 2 INDEX (FULL SCAN) OF 'TABLE1_SOMETHINGELSE' (INDEX) (C
ost=1 Card=5)
4 1 SORT (JOIN) (Cost=4 Card=3 Bytes=21)
5 4 VIEW (Cost=3 Card=3 Bytes=21)
6 5 SORT (ORDER BY) (Cost=3 Card=3 Bytes=42)
7 6 COUNT
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (TABLE
) (Cost=2 Card=3 Bytes=42)
9 8 INDEX (RANGE SCAN) OF 'TABLE2_SOMETHINGELSE2'
(INDEX) (Cost=1 Card=3)
SQL> select * from table1;
SOMETHING SOMETHINGE
---------- ----------
A1 B A2 C A3 D A4 E A5 F
SQL> select * from table2;
SOMETHINGE SOMETHINGE
---------- ----------
B 1 C 2 D 3 E 4 F 5
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On
Behalf Of Ken Naim
Sent: Friday, November 03, 2006 4:27 PM
To: 'Jonathan Lewis'; oracle-l_at_freelists.org
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_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
>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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 03 2006 - 16:58:45 CST
 |
 |