select * from t1 where object_id in (select object_id from t2 where t1.object_id=t2.object_id and rownum<10);
create table t1 as select * from dba_objects
create table t2 as select * from dba_objects
子查询返回1行还是100行,都没有意义
SQL> select * from t1 where object_id in (select object_id from t2 where t1.object_id=t2.object_id and rownum<10);
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 4165492305
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 6624 | 4736 (1)| 00:00:57 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 72109 | 14M| 228 (1)| 00:00:03 |
|* 3 | FILTER | | | | | |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
3 - filter("OBJECT_ID"=:B1)
4 - filter(ROWNUM<10)
5 - filter("T2"."OBJECT_ID"=:B1)
这里T1表的OBJECT_ID 传入到子查询中,子查询里限制条数毫无意思
等价于
select * from t1 where object_id in (select object_id from t2 where t1.object_id=t2.object_id );
测试;
SQL> select count(t2.object_id) from t2 ,t1 where t1.object_id=t2.object_id and t2.object_id=3;
COUNT(T2.OBJECT_ID)
-------------------
512
SQL> select object_id from t1 where object_id=3;
OBJECT_ID
----------
3
档T1把OBJECT_ID=3 传入到子查询时,那么子查询会返回512行
相当于:
select* from t1 where object_id in (select object_id from t2 where object_id=3);
select * from t1 where object_id in (3,3,3,3,3,3,3,3,3................);512个
这个和
select * from t1 where object_id in (3)没有任何区别:
这个子查询加rownum 除了阻止子查询展开外,没有任何意思。