記一次not in 和 minus的優化

優化前：

select count(t.id)

  from test t

 where t.status = 1

   and t.id not in (select distinct a.app_id

                      from test2 a

                     where a.type = 1

                       and a.rule_id in (152, 153, 154))

  17:20:57 laojiu>@plan

plan_table_output

————————————————————————————————————————-

plan hash value: 684502086

—————————————————————————————-

| id  | operation           | name             | rows  | bytes | cost (%cpu)| time     |

|   0 | select statement    |                  |     1 |    18 |   176k  (2)| 00:35:23 |

|   1 |  sort aggregate     |                  |     1 |    18 |            |          |

|*  2 |   filter            |                  |       |       |            |          |

|*  3 |    table access full| test      |  1141 | 20538 |   845   (2)| 00:00:11 |

|*  4 |    table access full| test2 |     1 |    12 |   309   (2)| 00:00:04 |

predicate information (identified by operation id):

—————————————————

   2 – filter( not exists (select /*+ */ 0 from “test2” “a” where

              “a”.”type”=1 and (“a”.”rule_id”=152 or “a”.”rule_id”=153 or

              “a”.”rule_id”=154) and lnnvl(“a”.”app_id”<>:b1)))

   3 – filter(“t”.”status”=1)

   4 – filter(“a”.”type”=1 and (“a”.”rule_id”=152 or “a”.”rule_id”=153 or

              “a”.”rule_id”=154) and lnnvl(“a”.”app_id”<>:b1))

statistics

———————————————————-

          0  recursive calls

          0  db block gets

    1762169  consistent gets

          0  physical reads

          0  redo size

        519  bytes sent via sql*net to client

        492  bytes received via sql*net from client

          2  sql*net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

21 rows selected.

優化後：

 select count(*) from(

 select t.id

   from test t

  where t.status = 1

 minus

 select distinct a.app_id

   from test2 a

  where a.type = 1

    and a.rule_id in (152, 153, 154))

17:23:33 laojiu>@plan

plan hash value: 631655686

————————————————————————————————–

| id  | operation             | name             | rows  | bytes |tempspc| cost (%cpu)| time     |

|   0 | select statement      |                  |     1 |       |       |  1501   (2)| 00:00:19 |

|   1 |  sort aggregate       |                  |     1 |       |       |            |          |

|   2 |   view                |                  |  1141 |       |       |  1501   (2)| 00:00:19 |

|   3 |    minus              |                  |       |       |       |            |          |

|   4 |     sort unique       |                  |  1141 | 20538 |       |   846   (2)| 00:00:11 |

|*  5 |      table access full| test      |  1141 | 20538 |       |   845   (2)| 00:00:11 |

|   6 |     sort unique       |                  | 69527 |   814k|  3632k|   654   (2)| 00:00:08 |

|*  7 |      table access full| test2 | 84140 |   986k|       |   308   (2)| 00:00:04 |

   5 – filter(“t”.”status”=1)

   7 – filter(“a”.”type”=1 and (“a”.”rule_id”=152 or “a”.”rule_id”=153 or

              “a”.”rule_id”=154))

          1  recursive calls

       2240  consistent gets

        516  bytes sent via sql*net to client

          2  sorts (memory)

在優化sql的時候，我們需要轉變一下思路，等價的改寫sql；

改寫後的sql由于邏輯讀得到了天翻地覆的改變，很快得到結果。

第一條sql執行計劃中有一個函數，lnnvl(“a”.”app_id”<>:b1)，lnnvl(exp)

如果exp的結果是false或者是unknown,那麼lnnvl傳回true;

如果exp的結果是true,傳回false.