Oracle 的 in 和 not in ――效能實踐

oracle|效能
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;