今天看文档时,感觉这个结论适用范围不是那么广。
select pk_invmandoc
from t1
where pk_corp = '1363'
and exists
(select pk_invbasdoc
from t2
where t2.pk_invbasdoc = t1.pk_invbasdoc
and t2.pk_invbasdoc in
('10424510001000035983', '10424510001000031969',
'10424510001000017301', '10424510001000025209',
'10424510001000001972', '10424510001000025350',
'0001781000000003MLV7', '000178100000003MGUEL',
'10424510001000035572', '000178100000000FPAMV',
'0001781000000008Z3ZC', '000178100000003L8AVA',
'10424510001000037663', '000178100000002KL75B')
不要关联t2, pk_invbasdoc直接从t1中过滤,语句可改为
select pk_invmandoc from t1
where pk_corp = '1363' where pk_invbasdoc in( );
做个测试:
drop table t1;
create table t1 as
select object_id,object_name
from dba_objects
where rownum<=10;
drop table t2;
create table t2 as
select object_id,object_name
from dba_objects
where rownum<=20;
select object_id from t1;
select object_id from t2;
从查询中发现t1和t2前10个结果集都一样,t2比t1多后10个结果集,也就是说t1结果集是t2结果集的子集。
1、下面in里用t1结果集加上17(t2中结果)。
SQL> select t2.object_id, object_name
2 from t2
3 where exists
4 (select object_id
5 from t1
6 where t1.object_id = t2.object_id
7 and t2.object_id in (20, 44, 28, 15, 29, 3, 25, 39, 51, 26, 17));
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
20 ICOL$
44 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
39 I_IND1
51 I_CDEF2
26 I_PROXY_ROLE_DATA$_1
10 rows selected
2、直接用t2过滤in条件,这11个数据都在t2中,所以会出来11行数据。
SQL> select t2.object_id, object_name
2 from t2
3 where t2.object_id in (20, 44, 28, 15, 29, 3, 25, 39, 51, 26, 17);
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
20 ICOL$
44 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
39 I_IND1
51 I_CDEF2
26 I_PROXY_ROLE_DATA$_1
17 FILE$
11 rows selected
从上面两个例子发现:
其实当exists子查询满足条件——两表关联后的结果集包含in结果集,这时才能像上面那样不关联子查询而直接在外查询中过滤。
这当然需要对业务有足够的了解和把握。否则也只能按上面那样写语句,然后寻求其他优化方法了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24496749/viewspace-723570/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24496749/viewspace-723570/