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

rollingpig · 发表于 2005-8-10 12:58

今天，发现production上有一个SQL 耗了特多CPU time,于是抓了下来，看看是否可以tuning .

原始 SQL 如下(table名字改了一下)：

[php]
DELETE FROM test_table_1 a
   WHERE NOT EXISTS (SELECT *
                        FROM test_table_2 b
                     WHERE a.parent_id = b.id)

执行时间 60S
Buffer get: 160W
执行计划

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

DELETE STATEMENT Optimizer Mode=CHOOSE 1         2069
  DELETE TEST_TABLE_1
HASH JOIN ANTI 1    26    2069
   TABLE ACCESS FULL TEST_TABLE_1 5 M 106 M 1210
   TABLE ACCESS FULL TEST_TABLE_2 46 K 181 K 48


其中
test_table_1 row count 为 4百万
test_table_2 为为 4万

TEST_TABLE_1上的 parent_id上有index.

...
[/php]

rollingpig · 发表于 2005-8-10 13:23

现在来试一下变换

用not in 代替 not exists

[php]

DELETE FROM test_table_1 a
WHERE parent_id NOT IN (SELECT id
FROM test_table_2 b)

..
[/php]
在9i 里面，Oracle把这个语句和前面的语句看为同一个语句，当然执行计划和执行时间也没有差别。

rollingpig · 发表于 2005-8-10 13:40

第二种变换（minus）

用minus,

把 not exists 部分先用minus去掉，再用in 来选择data。
[php]
DELETE FROM test_table_1 a
   WHERE parent_id IN (SELECT parent_id
                        FROM test_table_1
                        MINUS
                        SELECT ID
                        FROM test_table_2 b)

执行时间： 8S
Buffer get :20000

执行计划

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

DELETE STATEMENT Optimizer Mode=CHOOSE 2 G      23052
  DELETE TEST_TABLE_1
HASH JOIN 2 G 89G 23052
   VIEW SYS.VW_NSO_1 5 M 63 M 5488
      MINUS
      SORT UNIQUE 5 M 19 M
         INDEX FAST FULL SCAN TEST_INDX_1 5 M 19 M 457
      SORT UNIQUE 46 K 181 K
         TABLE ACCESS FULL TEST_TABLE_2 46 K 181 K 48
   TABLE ACCESS FULL TEST_TABLE_1 5 M 106 M 1210

速度提高了不少。
...
[/php]

rollingpig · 发表于 2005-8-10 13:46

在 minus之前加个 distinct看看
[php]
DELETE FROM test_table_1 a
   WHERE parent_id IN (SELECT distinct parent_id
                        FROM test_table_1
                        MINUS
                        SELECT ID
                        FROM test_table_2 b)

执行时间： 8S
Buffer get :20000

执行计划

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

DELETE STATEMENT Optimizer Mode=CHOOSE       2 G       23052
  DELETE TEST_TABLE_1
HASH JOIN       2 G 89G 23052
   VIEW SYS.VW_NSO_1 5 M 63 M 5488
      MINUS
      SORT UNIQUE       5 M 19 M
         INDEX FAST FULL SCAN TEST_INDX_1 5 M 19 M 457
      SORT UNIQUE       46 K 181 K
         TABLE ACCESS FULL TEST_TABLE_2 46 K 181 K 48
   TABLE ACCESS FULL TEST_TABLE_1 5 M 106 M 1210

和上一个一模一样。
由于用了in 的操作符，Oracle内部就给加了个  (SORT UNIQUE       )
[/php]

Matilda · 发表于 2005-8-10 13:47

沙发

rollingpig · 发表于 2005-8-10 14:08

可以用 in ,当然就可以用 exists 或者join 了(delete不好用join，只好改为SELECT)
[php]
Join

SELECT * FROM (SELECT DISTINCT parent_id
                                 FROM TEST_TABLE_1
                              MINUS
                              SELECT id
                                 FROM TEST_TABLE_2 b ) tmp,TEST_TABLE_1 a
   WHERE  tmp.parent_id = a.id
执行时间 8S
Buffer get: 20000

Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop

SELECT STATEMENT Optimizer MODE=CHOOSE 5 M      4493
  HASH JOIN 5 M 237 M 4493
VIEW 9 K 125 K 3161
   MINUS
      SORT UNIQUE 9 K 38 K
      INDEX FAST FULL SCAN TEST_INDX_1 5 M 19 M 457
      SORT UNIQUE 46 K 181 K
      TABLE ACCESS FULL TEST_TABLE_2 46 K 181 K 48
TABLE ACCESS FULL TEST_TABLE_1 5 M 164 M 1210

用exists的

DELETE FROM TEST_TABLE_1 a  WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT parent_id
                                 FROM TEST_TABLE_1
                              MINUS
                              SELECT id
                                 FROM TEST_TABLE_2 b )
WHERE parent_id  = a.parent_id
执行效率惨不忍睹

...
[/php]

rollingpig · 发表于 2005-8-10 14:24

除了上面几种，还有一种不是很常用的方法
就是(outer join + is null )（也是为了方便，先用SELECT来演示,）

[php]
select * from test_table_1 a,test_table_2 b
where a.parent_id = b.id(+)
and b.id is null

在本例中执行效率也挺差的。（超过1min）
Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop

SELECT STATEMENT Optimizer MODE=CHOOSE 24 M      3311
  FILTER
HASH JOIN OUTER
   TABLE ACCESS FULL TEST_TABLE_1 5 M 164 M 1210
   TABLE ACCESS FULL TEST_TABLE_2 46 K 5 M 48

。。
[/php]

[php]

加个 distinct 看看

SELECT a.* FROM  TEST_TABLE_2 b ,(SELECT DISTINCT parentid FROM TEST_TABLE_1) a
                              WHERE a.parentid = b.id(+)
                                    AND b.id IS NULL

执行时间：1S
Buffer gets : 1000

Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop

SELECT STATEMENT Optimizer MODE=CHOOSE 47 K      3117
  FILTER
HASH JOIN OUTER
   VIEW 9 K 125 K 3067
      SORT UNIQUE 9 K 38 K 3067
      INDEX FAST FULL SCAN TEST_INDX_1 5 M 19 M 457
   TABLE ACCESS FULL TEST_TABLE_2 46 K 181 K 48

...
[/php]

rollingpig · 发表于 2005-8-10 14:33

看来最后一种是最有效哦
改成DELETE形式看看

[php]
DELETE FROM TEST_TABLE_1 WHERE parentid IN (
SELEC parentid FROM  TEST_TABLE_2 b ,(SELECT DISTINCT parentid FROM TEST_TABLE_1) a
                              WHERE a.parentid = b.id(+)
                                    AND b.id IS NULL
)

成功了，执行时间 1S (实际上是 800-900ms)
Buffer_gets ：1000
执行计划
Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop

DELETE STATEMENT Optimizer MODE=CHOOSE 6 K      119
  DELETE TEST_TABLE_1
HASH JOIN SEMI 6 K 171 K 119
   TABLE ACCESS FULL TEST_TABLE_1 6 K 91 K 26
   VIEW SYS.VW_NSO_1 2 M 30 M 71
      FILTER
      HASH JOIN OUTER
         INDEX FAST FULL SCAN TEST_INDX_1 6 K 18 K 23
         TABLE ACCESS FULL TEST_TABLE_2 46 K 136 K 47

...
[/php]

rollingpig · 发表于 2005-8-10 14:34

可能not exists / not in 还有其他变换形式，大家可以讨论讨论。

violing · 发表于 2005-8-10 14:35

这两个表一大一小,正好用HASH,速度可以提高到秒级,不信你就试试

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

第一种变换(not in)

第二种变换（minus）

(minus + distinct )

用HASH