** Oracle ** ** 的 ** ** in ** ** 和 ** ** not in ** ――性能实践
在很多软件系统中，系统的性能很打程度上有数据库的性能决定。以前也曾经做过很多次关于性能方面的各种测试，特别是关于 oracle 的，我想到也应该记录下来一部分，为大家共享。
事情发生在我们的系统从 sqlserver 移植到 oracle ，用户在一个查询的操作上等待的时间无法忍受了，我们关于这个查询的处理与原来的方式一下，难道 sqlserver 同 oracle 有什么地方不一样么，让我们来看看 oracle 有什么地方有问题，或者是我们使用的有问题？
业务问题大概可以这样描述，一个父表，一个子表，查询的结果是找到子表中没有使用父表 id 的记录，这种情况估计很多系统都会牵涉得到。让我们来举一个例子：
表一： 父表 parent
编号
|
字段
|
类型
|
说明
---|---|---|---
1.
|
Id
|
Varchar2(10)
|
主键
2.
|
Name
|
Varchar2(100)
|
名称
表二： 子表 childen
编号
|
字段
|
类型
|
说明
---|---|---|---
1.
|
Id
|
Varchar2(10)
|
主键
2.
|
Pid
|
Varchar2(10)
|
主表的表示
3.
|
Name
|
Varchar2(100)
|
名称
父表存储父亲，子表存储孩子，然后通过 pid 和父表关联，查询需要的结果是找到 尚未有孩子的父亲 。
我们来看一下查询语句的写法：
** select ** * from parent where id not in ( select pid from childen)
这种标准的写法在子表存在 50 万条的记录的时候，查询时间超过了 10 秒，远远大于原来的 sql server 服务器的一秒。我在解决的时候想到了一个方法：
** select ** * from parent where id in
( select id from parent minus select pid from childen )
正常理解下，这个语句应该更加费时，但是事实完全出乎意料，这条语句不仅仅在子表存在大量记录的情况下速度良好，在子表少量数据的情况下速度也非常的好，基本在 1 秒内完成。
这个结果可以很明显的证明 oracle 在子查询的内部处理的时候，使用 in 和 not in 的巨大区别，希望用到这种方式的用户注意，也期待有人解释其中的问题。
附录： 测试数据的语句
_ -- create parent table _
drop table parent ;
create table parent (id varchar ( 10 ), name varchar ( 100 ), primary key (id) );
_ -- create childen table _
drop table childen;
create table childen(id varchar ( 10 ),pid varchar ( 10 ), name varchar ( 100 ), primary key (id) );
_ -- Create/Recreate primary, unique and foreign key constraints _
alter table CHILDEN
add constraint fk_123 foreign key (PID)
references parent (ID);
_ -- add test date for parent _
_ -- Created on 2004-11-29 by GUIP _
declare
_ -- Local variables here _
i integer ;
begin
_ -- Test statements here _
i := 0 ;
delete from parent ;
loop
i := i + 1 ;
dbms_output.put_line(i);
insert into parent (id, name ) values (i, 'name ' || i);
if (i mod 100 = 0 ) then
commit ;
end if ;
exit when i > 1000 ;
end loop ;
commit ;
end ;
_ -- add test date for childen _
_ -- Created on 2004-11-29 by GUIP _
declare
_ -- Local variables here _
i integer ;
j integer ;
begin
_ -- Test statements here _
i := 0 ;
delete from childen ;
loop
j := 0 ;
loop
i := i + 1 ;
j := j + 1 ;
insert into childen(id, pid, name ) values (i, j, 'name ' || j);
if (i mod 100 = 0 ) then
commit ;
end if ;
exit when j>= 50 ;
end loop ;
exit when i >= 10000 * 50 ;
end loop ;
commit ;
end ;