透過sql trace比較常規 not in 、minus、not exists效率

測試目的：

業務中，有兩張表，A 表資料900w，B表資料3200w。A表的表結構欄位包含B表表結構所有欄位，現在要求出A表中不存在B表中的資料。
針對現網，模擬做了如下實驗。

1：在無索引的狀態下，比較 not in、minus及not exists查詢效率
2：在建立索引的狀態下，比較 not in、minus及not exists查詢效率

測試環境：

OS: linux
DB: oracle 10.2.0.4

測試步驟：

首先建立兩張表t1 、t2 ,要求將t1 表中不存在t2表中的結果集取出來

SQL> connect hr/hr;
Connected.
SQL> create table t1 as select * from dba_objects where rownum<=55000;
Table created.
SQL> create table t2 as select * from dba_objects where rownum<=50000;
Table created.

在沒有建立索引的情況下，比較三種的查詢效率。

1：not in 方式

SQL> alter session set sql_trace=true;
Session altered.
SQL> select count(*) from t1 where object_id not in (select object_id from t2);
  COUNT(*)
----------
      5000
SQL>
SQL> SELECT c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' trace_file_name  
        from v$process a,v$session b,v$parameter c,v$instance d
         where a.addr=b.paddr
        and b.audsid=userenv('sessionid')
        and c.name='user_dump_dest';
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/admin/ora10/udump/ora10_ora_9548.trc
SQL>

[oracle@test1 ~]$ tkprof /home/oracle/admin/ora10/udump/ora10_ora_9548.trc /home/oracle/1.txt

[oracle@test1 ~]$ more 1.txt
********************************************************************************
select count(*)
from
t1 where object_id not in (select object_id from t2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2    246.47     241.00        848   20660194          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    246.47     241.01        848   20660196          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=20660194 pr=848 pw=0 time=241005013 us)
   5000   FILTER  (cr=20660194 pr=848 pw=0 time=240435636 us)
  55000    TABLE ACCESS FULL T1 (cr=761 pr=473 pw=0 time=220166 us)
  50000    TABLE ACCESS FULL T2 (cr=20659433 pr=375 pw=0 time=240235907 us)
********************************************************************************

2：測試 minus

SQL> select count(*) from (select object_id from t1 minus select object_id from t2);
  COUNT(*)
----------
      5000
SQL> SELECT c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' trace_file_name  
             from v$process a,v$session b,v$parameter c,v$instance d
             where a.addr=b.paddr
          and b.audsid=userenv('sessionid')
           and c.name='user_dump_dest';
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/admin/ora10/udump/ora10_ora_9548.trc
SQL> alter session set sql_trace=false;
Session altered.

[oracle@test1 ~]$ tkprof /home/oracle/admin/ora10/udump/ora10_ora_9548.trc /home/oracle/1.txt

[oracle@test1 ~]$ more 1.txt
********************************************************************************
select count(*)
from
(select object_id from t1 minus select object_id from t2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.16       0.16          0       1452          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.16       0.16          0       1454          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1452 pr=0 pw=0 time=160710 us)
   5000   VIEW  (cr=1452 pr=0 pw=0 time=167855 us)
   5000    MINUS  (cr=1452 pr=0 pw=0 time=157852 us)
  55000     SORT UNIQUE (cr=761 pr=0 pw=0 time=65764 us)
  55000      TABLE ACCESS FULL T1 (cr=761 pr=0 pw=0 time=34 us)
  50000     SORT UNIQUE (cr=691 pr=0 pw=0 time=60896 us)
  50000      TABLE ACCESS FULL T2 (cr=691 pr=0 pw=0 time=39 us)
********************************************************************************

3：測試 not exists

SQL> alter session set sql_trace=true;
Session altered.
SQL> select count(*) from t1 where not exists (select object_id from t2);
  COUNT(*)
----------
         0
SQL> select count(*) from t1 where not exists (select object_id from t2 where t1.object_id=t2.object_id);
  COUNT(*)
----------
      5000
SQL> SELECT c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' trace_file_name  
             from v$process a,v$session b,v$parameter c,v$instance d
              where a.addr=b.paddr
            and b.audsid=userenv('sessionid')
             and c.name='user_dump_dest';
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/admin/ora10/udump/ora10_ora_9548.trc
SQL>

[oracle@sxit-test1 ~]$ tkprof /home/oracle/admin/ora10/udump/ora10_ora_9548.trc /home/oracle/2.txt
TKPROF: Release 10.2.0.4.0 - Production on Tue Oct 11 10:11:24 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

[oracle@test1 ~]$ more 2.txt

********************************************************************************
select count(*)
from
t1 where not exists (select object_id from t2 where t1.object_id=
  t2.object_id)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.08       0.08          0       1452          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.08       0.08          0       1454          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1452 pr=0 pw=0 time=84203 us)
   5000   HASH JOIN ANTI (cr=1452 pr=0 pw=0 time=82201 us)
  55000    TABLE ACCESS FULL T1 (cr=761 pr=0 pw=0 time=39 us)
  50000    TABLE ACCESS FULL T2 (cr=691 pr=0 pw=0 time=46 us)


********************************************************************************

透過以上測試，在表中無空值的情況下，not exists的查詢效率確實最高，而not in效率最低。





在建立索引的情況下，比較三種的查詢效率。

SQL> desc dba_objects;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
SQL> create index in_t1 on t1(OBJECT_ID);
Index created.
SQL> create index in_t2 on t2(OBJECT_ID);
Index created.


1: 測試not in方式

SQL> select count(*) from t1 where object_id not in (select object_id from t2);
  COUNT(*)
----------
      5000

sql trace 結果如下：

********************************************************************************
select count(*)
from
t1 where object_id not in (select object_id from t2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          6          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4    499.95     488.83        848   41320388          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8    499.96     488.83        848   41320394          0           2
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=20660194 pr=848 pw=0 time=241005013 us)
   5000   FILTER  (cr=20660194 pr=848 pw=0 time=240435636 us)
  55000    TABLE ACCESS FULL T1 (cr=761 pr=473 pw=0 time=220166 us)
  50000    TABLE ACCESS FULL T2 (cr=20659433 pr=375 pw=0 time=240235907 us)
********************************************************************************

2：測試 minus方式

SQL> select count(*) from (select object_id from t1 minus select object_id from t2);
  COUNT(*)
----------
      5000


sql trace 結果如下：

********************************************************************************
select count(*)
from
(select object_id from t1 minus select object_id from t2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          6          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.31       0.30          0       2904          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.31       0.31          0       2910          0           2
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1452 pr=0 pw=0 time=160710 us)
   5000   VIEW  (cr=1452 pr=0 pw=0 time=167855 us)
   5000    MINUS  (cr=1452 pr=0 pw=0 time=157852 us)
  55000     SORT UNIQUE (cr=761 pr=0 pw=0 time=65764 us)
  55000      TABLE ACCESS FULL T1 (cr=761 pr=0 pw=0 time=34 us)
  50000     SORT UNIQUE (cr=691 pr=0 pw=0 time=60896 us)
  50000      TABLE ACCESS FULL T2 (cr=691 pr=0 pw=0 time=39 us)
********************************************************************************

3：not exists方式
  
SQL> select count(*) from t1 where not exists (select object_id from t2 where t1.object_id=t2.object_id);
  COUNT(*)
----------
      5000

sql trace 結果如下：

********************************************************************************
select count(*)
from
t1 where not exists (select object_id from t2 where t1.object_id=
  t2.object_id)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          6          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        4      0.15       0.15        111       2331          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.16       0.16        111       2337          0           2
Misses in library cache during parse: 2
Parsing user id: 55  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1452 pr=0 pw=0 time=84203 us)
   5000   HASH JOIN ANTI (cr=1452 pr=0 pw=0 time=82201 us)
  55000    TABLE ACCESS FULL T1 (cr=761 pr=0 pw=0 time=39 us)
  50000    TABLE ACCESS FULL T2 (cr=691 pr=0 pw=0 time=46 us)
********************************************************************************


從測試結果可以看出，加不加索引 not exists的查詢效率是最高的。

而加索引了，查詢的效率反而降低了。

那麼，請問大牛們，對於日常中的這兩個大表，能不能最佳化一下我的查詢語句，從而再次提高查詢效率？

對Tkprof命令輸出的解釋：

首先解釋輸出檔案中列的含義：
CALL：每次SQL語句的處理都分成三個部分
Parse：這步將SQL語句轉換成執行計劃，包括檢查是否有正確的授權和所需要用到的表、列以及其他引用到的物件是否存在。
Execute：這步是真正的由Oracle來執行語句。對於insert、update、delete操作，這步會修改資料，對於select操作，這步就只是確定選擇的記錄。
Fetch：返回查詢語句中所獲得的記錄，這步只有select語句會被執行。
COUNT:這個語句被parse、execute、fetch的次數。
CPU：這個語句對於所有的parse、execute、fetch所消耗的cpu的時間，以秒為單位。
ELAPSED：這個語句所有消耗在parse、execute、fetch的總的時間。
DISK：從磁碟上的資料檔案中物理讀取的塊的數量。一般來說更想知道的是正在從快取中讀取的資料而不是從磁碟上讀取的資料。
QUERY：在一致性讀模式下，所有parse、execute、fetch所獲得的buffer的數量。一致性模式的buffer是用於給一個長時間執行的事務提供一個一致性讀的快照，快取實際上在頭部儲存了狀態。
CURRENT：在current模式下所獲得的buffer的數量。一般在current模式下執行insert、update、delete操作都會獲取buffer。在current模式下如果在快取記憶體區發現有新的快取足夠給當前的事務使用，則這些buffer都會被讀入了快取區中。
ROWS: 所有SQL語句返回的記錄數目，但是不包括子查詢中返回的記錄數目。對於select語句，返回記錄是在fetch這步，對於insert、update、delete操作，返回記錄則是在execute這步。

http://blog.csdn.net/tianlesoftware/article/details/5632003