在很多软件系统中
,系统的性能很打程度上有数据库的性能决定
。以前也曾经做过很多次关于性能方面的各种测试
,特别是关于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;
事情发生在我们的系统从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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10294527/viewspace-122292/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10294527/viewspace-122292/