优化前：

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_TABLE_OUTPUT ————————————————————————————————————————- 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 | ————————————————————————————————– Predicate Information (identified by operation id): ————————————————— 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)) 21 rows selected. Statistics ———————————————————- 1 recursive calls 0 db block gets 2240 consistent gets 0 physical reads 0 redo size 516 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed

在优化sql的时候，我们需要转变一下思路，等价的改写sql；

改写后的sql由于逻辑读得到了天翻地覆的改变，很快得到结果。

第一条sql执行计划中有一个函数，LNNVL(“A”.”APP_ID”<>:B1)，lnnvl(exp)

如果exp的结果是false或者是unknown,那么lnnvl返回true;

如果exp的结果是true,返回false.