Oracle in 查询优化
用习惯了 Mysql,今天突然遇到 Oracle 里的 in 查询贼慢,记录下优化过程
参考此文章:《oracle where in 优化,如何优化 in 操作》,hint 的确很有用:Select /*+ use_concat */ Item.* From Item Item Where Item.Item_Id in (15056,14697);
但这只能解决单表查询的问题
我遇到的是:
select
A.user_id,
sum(B.money) as amount
from
A inner join B on A.id = B.master_id
inner join C on B.c_id = C.id
where
C.category_id in (1,2,...,100)
group by A.user_id
其中涉及到 explain 解释器,索引方式,最终结论:
select
A.user_id,
sum(B.money) as amount
from
A inner join B on A.id = B.master_id
inner join C on B.c_id = C.id
where
(
C.category_id = 1
or C.category_id = 2,
...
or C.category_id = 100
)
group by A.user_id
90秒变2秒
本作品采用《CC 协议》,转载必须注明作者和本文链接