RE: Strange Behavior with SQL using IN
- From: "Michael Dinh" <mdinh@xxxxxxxxx>
- To: <ron.crisco@xxxxxxxxxxxx>
- Date: Thu, 15 Apr 2010 14:21:49 -0700
Thanks Ron. How do I prevent the transformation because of incorrect
results?
I thought it might have been optimizer_secure_view_merging from
googling, but that did not solve the issue.
Here is a better example
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> >
truncate table t1;
Table truncated.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> >
truncate table t2;
Table truncated.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > insert
into t1 values(1);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > insert
into t1 values(2);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > /
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > insert
into t2 values(1);
1 row created.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > commit;
Commit complete.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > SELECT
* FROM t1 c WHERE c.id1 IN (SELECT id1 FROM t2 a);
ID1
----------
1
1
1
2
2
2
6 rows selected.
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> > SELECT
* FROM t1 c WHERE c.id1 IN (SELECT id2 FROM t2 a);
ID1
----------
1
1
1
OPS$ORACLE@test10g:PRIMARY <mailto:OPS$ORACLE@test10g:PRIMARY> >
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
individual or entity to which it is addressed, and may contain
information that is privileged, confidential and exempt from disclosure
under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN
PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU
ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT
DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS
EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of
this email (and attachments) is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. Please notify the sender of the
error and delete the e-mail you received. Thank you.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ron Crisco
Sent: Thursday, April 15, 2010 1:42 PM
To: Michael Dinh
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Strange Behavior with SQL using IN
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:953229
842074
<http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:95322
9842074> 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 <mailto: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:
