Oracle 的 in 和 not in ――性能實踐
2024-08-29 13:31:32
供稿：網(wǎng)友
oracle 的 in 和 not in ――性能實踐
在很多軟件系統(tǒng)中，系統(tǒng)的性能很打程度上有數(shù)據(jù)庫的性能決定。以前也曾經(jīng)做過很多次關于性能方面的各種測試，特別是關于oracle的，我想到也應該記錄下來一部分，為大家共享。
事情發(fā)生在我們的系統(tǒng)從sqlserver移植到oracle，用戶在一個查詢的操作上等待的時間無法忍受了，我們關于這個查詢的處理與原來的方式一下，難道sqlserver 同oracle有什么地方不一樣么，讓我們來看看oracle有什么地方有問題，或者是我們使用的有問題？
業(yè)務問題大概可以這樣描述，一個父表，一個子表，查詢的結(jié)果是找到子表中沒有使用父表id的記錄，這種情況估計很多系統(tǒng)都會牽涉得到。讓我們來舉一個例子：
表一： 父表 parent
編號
字段
類型
說明
1.
id
varchar2(10)
主鍵
2.
name
varchar2(100)
名稱
表二： 子表 childen
編號
字段
類型
說明
1.
id
varchar2(10)
主鍵
2.
pid
varchar2(10)
主表的表示
3.
name
varchar2(100)
名稱
父表存儲父親，子表存儲孩子，然后通過pid和父表關聯(lián)，查詢需要的結(jié)果是找到尚未有孩子的父親。
我們來看一下查詢語句的寫法：
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 )
正常理解下，這個語句應該更加費時，但是事實完全出乎意料，這條語句不僅僅在子表存在大量記錄的情況下速度良好，在子表少量數(shù)據(jù)的情況下速度也非常的好，基本在1秒內(nèi)完成。
這個結(jié)果可以很明顯的證明oracle 在子查詢的內(nèi)部處理的時候，使用 in 和 not in 的巨大區(qū)別，希望用到這種方式的用戶注意，也期待有人解釋其中的問題。
附錄： 測試數(shù)據(jù)的語句
-- 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;
