测试目的：

业务中，有两张表，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这步。