一次SQL Tuning引出来的not in , not exists 语句的N种写法

sdusun

这些方法各有千秋啊！多谢楼主！个人比较倾向于outer jion + null condition，不过有时候别的方法可能更快，只能试着来啦。

space6212

outer jion + null condition用在小表与大表关联，且返回数据较小的情况下效率会非常高
否则可能效率反而更底下 了

SONGHEFEI

9i 中好像exists 与 in 基本相同！！

baggio_yang

佩服，大開眼界

flyways

开眼界,其实具体情况采取具体方法.

sky_heaven

呵呵。 终于拜读完了。 不过还是要自己写一下。 是啊， 感觉in和exists通过执行计划看不出什么区别啊。

cc59

还在顶啊.

foreverlee

在测试机上测试了一把
发现和楼主测试的结构有些不同
表，索引，索引字段已经分析过.
我感觉区别在
1 NL探测Inner table的次数和这个结果记录数.
2 子查询效率，INDEX FAST FULL SCAN 代价.


[PHP]



SQL>
SQL> select count(*) from track;

  COUNT(*)
----------
  12337495

Elapsed: 00:00:00.01
SQL> select count(*) from terminal_user;

  COUNT(*)
----------
       305

Elapsed: 00:00:00.00
SQL>
SQL> set autotrace traceonly
SQL>
SQL>
SQL> select tu.* from terminal_user tu
  2   where tu.tu_id not in (select distinct tu_id from track);

36 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    27 |  1944 |   614   (1)|
|   1 |  NESTED LOOPS ANTI |                   |    27 |  1944 |   614   (1)|
|   2 |   TABLE ACCESS FULL| TERMINAL_USER     |   305 | 20740 |     3   (0)|
|*  3 |   INDEX RANGE SCAN | TRACK_TUID_IDX |    11M|    42M|     2   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TU"."TU_ID"="TU_ID"

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        627  consistent gets
          0  physical reads
         80  redo size
       4224  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

由于terminal_user 仅有305条记录，
而且track表相关字段有索引，INDEX RANGE SCAN使得探测发挥了很高的效率.因此这个NL效率很高.
我们看到整个sql执行的Cost仅为614。


再来看 outer jion + null condition
SQL>
SQL> select tu.*
  2    from terminal_user tu,
       ( select distinct tu_id from track ) tk
where tu.tu_id = tk.tu_id(+)
   and tk.tu_id is null;  3    4    5  

36 rows selected.

Elapsed: 00:00:03.15

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |    27 |  2187 | 10897  (16)|
|*  1 |  HASH JOIN RIGHT ANTI   |                   |    27 |  2187 | 10897  (16)|
|   2 |   VIEW                  |                   |   278 |  3614 | 10894  (16)|
|   3 |    HASH UNIQUE          |                   |   278 |  1112 | 10894  (16)|
|   4 |     INDEX FAST FULL SCAN| TRACK_TUID_IDX |    12M|    47M|  9365   (3)|
|   5 |   TABLE ACCESS FULL     | TERMINAL_USER     |   305 | 20740 |     3   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TU"."TU_ID"="TK"."TU_ID"

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      42111  consistent gets
          0  physical reads
          0  redo size
       4224  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

我们可以看到 select distinct tu_id from track 的访问路径为 INDEX FAST FULL SCAN
而Cost就为9365.


SQL> select distinct tu_id from track;

278 rows selected.

Elapsed: 00:00:03.27

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   278 |  1112 | 10894  (16)|
|   1 |  HASH UNIQUE          |                   |   278 |  1112 | 10894  (16)|
|   2 |   INDEX FAST FULL SCAN| TRACK_BK_TUID_IDX |    12M|    47M|  9365   (3)|
--------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      42180  consistent gets
          0  physical reads
        124  redo size
       5627  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        278  rows processed

SQL> select /*+ INDEX(track,TRACK_BK_TUID_IDX)*/ distinct tu_id from track
  2  ;

278 rows selected.

Elapsed: 00:00:03.69

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   278 |  1112 | 43705   (5)|
|   1 |  SORT UNIQUE NOSORT|                   |   278 |  1112 | 43705   (5)|
|   2 |   INDEX FULL SCAN  | TRACK_BK_TUID_IDX |    12M|    47M| 42176   (1)|
-----------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      41907  consistent gets
          0  physical reads
        132  redo size
       5627  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        278  rows processed

我发现select distinct tu_id from track无论是FFS还是IFS效率都不怎么样.


所以这里就产生一个问题.子查询的效率会受到数据量(影响到索引段的大小)的影响.
SQL> set autotrace off
SQL> analyze index TRACK_BK_TUID_IDX validate structure;

Index analyzed.

Elapsed: 00:00:15.65

SQL> select BLOCKS,USED_SPACE,DISTINCT_KEYS,LF_ROWS_LEN from index_stats;

    BLOCKS USED_SPACE DISTINCT_KEYS LF_ROWS_LEN
---------- ---------- ------------- -----------
     42752  198198860           278   197520787


[/PHP]

所以我感觉还是多做测试，用事实说话.

foreverlee

[PHP]
把not exists这种情况的执行计划补上.同not in这种情况

SQL> select tu.* from terminal_user tu
  2   where not exists ( select *
                      from track tk
                     where tu.tu_id = tk.tu_id);  3    4  

36 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    27 |  1944 |   614   (1)|
|   1 |  NESTED LOOPS ANTI |                   |    27 |  1944 |   614   (1)|
|   2 |   TABLE ACCESS FULL| TERMINAL_USER     |   305 | 20740 |     3   (0)|
|*  3 |   INDEX RANGE SCAN | TRACK_BK_TUID_IDX |    11M|    42M|     2   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TU"."TU_ID"="TK"."TU_ID"

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        628  consistent gets
          0  physical reads
        140  redo size
       4224  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed
[/PHP]

jiangjp2000

使用NOT EXISTS要比outer jion + null condition快一倍。