select sum(unit)
from t_ppp a
where a.customer_1 = 211212
and ( exists (select 1
from t_pp
where snd_id = 4
and customer_1 = 211212
and status not in (3, 4, 5, 7)))
or exists (select 1
from t_cp
where snd_id = 4
and customer_1 = 211212
and (state = 1 or pause_date >= add_months(sysdate, -3)))
and a.fst_id = 1111
and a.snd_id = 4;
t_ppp中PK为fst_id和snd_id,t_ppp有两个索引,列customer_1的normal索引和PK的索引问题:
查询语句如上时对t_ppp表进行的是按索引查询,可是当把a.customer_1 = 211212条件写在两个exists后面的任何地方,或者去掉这条条件,那对t_ppp表的查询都是全表查询?实在没搞明白,望各位高手指点迷津
from t_ppp a
where a.customer_1 = 211212
and ( exists (select 1
from t_pp
where snd_id = 4
and customer_1 = 211212
and status not in (3, 4, 5, 7)))
or exists (select 1
from t_cp
where snd_id = 4
and customer_1 = 211212
and (state = 1 or pause_date >= add_months(sysdate, -3)))
and a.fst_id = 1111
and a.snd_id = 4;
t_ppp中PK为fst_id和snd_id,t_ppp有两个索引,列customer_1的normal索引和PK的索引问题:
查询语句如上时对t_ppp表进行的是按索引查询,可是当把a.customer_1 = 211212条件写在两个exists后面的任何地方,或者去掉这条条件,那对t_ppp表的查询都是全表查询?实在没搞明白,望各位高手指点迷津
from t_ppp a
where a.customer_1 = 211212
and a.fst_id = 1111
and a.snd_id = 4
and ( exists (select 1
from t_pp
where snd_id = a.snd_id
and customer_1 = a.customer_1
and status not in (3, 4, 5, 7)))
or exists (select 1
from t_cp
where snd_id = a.snd_id
and customer_1 = a.customer_1
and (state = 1 or pause_date >= add_months(sysdate, -3)));
就是一条一条往where靠近
而exists只要查到就退出.效率会更高一些.
就是一条一条往where靠近
((((a and b) or c) and f) and g),则可以采取索引。
(b or c ) and f and g,由于必须先执行OR条件,则为全表,为了满足两个OR,ORACLE判断必须FULLscan一下。
所以,注意把可以AND在一起的,用()包起来,也可以尝试用rule中的强制索引,最好是前者。