优化前：
我要评论
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.
如对本文有疑问， 点击进行留言回复！！
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论