Strange Behavior with SQL using IN
- From: "Michael Dinh" <mdinh@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Apr 2010 12:33:05 -0700
Have you seen this behavior before?
Thanks Michael.
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 15 12:30:43 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > create table
t1(id1 int not null);
Table created.
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > create table
t2(id2 int not null);
Table created.
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > insert into
t1 values (1);
1 row created.
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > insert into
t2 values(1);
1 row created.
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > commit;
Commit complete.
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > set autotrace
traceonly
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05:PRIMARY> > SELECT * FROM
t1 c WHERE c.id1 IN (SELECT id1 FROM t2 a);
Execution Plan
----------------------------------------------------------
Plan hash value: 915695074
------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 13 | 6 (0)|
00:00:01 |
|* 1 | FILTER | | | | |
|
| 2 | TABLE ACCESS FULL | T1 | 1 | 13 | 3 (0)|
00:00:01 |
|* 3 | FILTER | | | | |
|
| 4 | TABLE ACCESS FULL| T2 | 1 | | 3 (0)|
00:00:01 |
------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T2" "A" WHERE :B1=:B2))
3 - filter(:B1=:B2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
510 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
OPS$ORACLE@db05:PRIMARY <mailto:OPS$ORACLE@db05: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.
Other related posts:
