Re: Strange Behavior with SQL using IN
- From: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Thu, 15 Apr 2010 15:41:43 -0500
Michael,
If you use 10053 tracing, you will see that the CBO does this transformation
for you. Here are some excerpts of the 10053 trace relevant to that query.
For discussion about why EXISTS vs IN, Tom explains nicely at
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:953229842074
<http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:953229842074>
Ron
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
"C"."ID1"=ANY (SELECT "C"."ID1" FROM "T2" "A")
...
FPD: Considering simple filter push in SEL$1 (#1)
FPD: Current where clause predicates in SEL$1 (#1) :
EXISTS (SELECT 0 FROM "T2" "A")
FPD: Considering simple filter push in SEL$2 (#2)
FPD: Current where clause predicates in SEL$2 (#2) :
:B1=:B2
On Thu, Apr 15, 2010 at 2:33 PM, Michael Dinh <mdinh@xxxxxxxxx> wrote:
> Have you seen this behavior before?
>
> Thanks Michael.
>
>
> OPS$ORACLE@db05:PRIMARY> SELECT * FROM t1 c WHERE c.id1 IN (SELECT id1
> FROM t2 a);
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T2" "A" WHERE :B1=:B2))
> 3 - filter(:B1=:B2)
>
>
>
Other related posts:
