select a.* from t a where a.type='a' and exists(select 1 from t b where b.type='b' and a.pid=b.pid)
这样速度是增加了不少,但是无法查出关联的记录中同一个字段的数据 也即类似select a.type,a.pid,a.name,b.name from (select * from t where t.type='a') a,(select * from t where t.type='b') b where a.pid=b.pid
那只能按你这个语句写了: select a.type,a.pid,a.name,b.name from (select * from t where t.type='a') a,(select * from t where t.type='b') b where a.pid=b.pid
from t a
where a.type='a' and exists(select 1 from t b where b.type='b' and a.pid=b.pid)
也即类似select a.type,a.pid,a.name,b.name from (select * from t where t.type='a') a,(select * from t where t.type='b') b
where a.pid=b.pid
select a.type,a.pid,a.name,b.name from (select * from t where t.type='a') a,(select * from t where t.type='b') b
where a.pid=b.pid
我试了下2W就很慢,用不用exists有将近10倍的差别
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19010 | 891K| 175 (6)| 00:00:03 |
|* 1 | HASH JOIN RIGHT SEMI| | 19010 | 891K| 175 (6)| 00:00:03 |
|* 2 | TABLE ACCESS FULL | T1 | 19010 | 204K| 87 (5)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 19010 | 686K| 87 (5)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72M| 3584M| 1416 (89)| 00:00:17 |
|* 1 | HASH JOIN | | 72M| 3584M| 1416 (89)| 00:00:17 |
|* 2 | TABLE ACCESS FULL| T1 | 19010 | 278K| 87 (5)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T1 | 19010 | 686K| 87 (5)| 00:00:02 |
---------------------------------------------------------------------------
在sqlplus里面,set autotrace on就可以了啊
pid+type是unique key 吗
select a.* from t a, t b
where a.pid=b.pid and a.type='a' and b.type='b'这句sql的执行计划和原先的一样吗
where a.pid=b.pid and a.type='a' and b.type='b'