今天在itpub论坛看到一则贴子,楼主反应以下sql执行缓慢:
create table test_20140230 as
select to_number(201401) deal_date, e.src_terminal_id, e.cont_type
from (select *
from test1 a
where a.forward_time >= 201401 || '00000000'
and a.forward_time < 201402 || '00000000'
and exists
(select TERMINAL_ID
from test2 b
where b.oper_type in (1, 2, 3)
and b.oper_time >= to_date(201401, 'yyyymm')
and b.oper_time <
trunc(add_months(to_date(201401, 'yyyymm'), 1))
and b.terminal_id = a.src_terminal_id
and b.dest_id = a.dst_terminal_id
and b.info_id = a.info_id
and (to_date(a.forward_time, 'yyyyMMddHH24miss') -
b.oper_time) < 1
and decode(b.type,
1,
1,
2,
1,
6,
1,
7,
1,
3,
2,
4,
2,
5,
2) = a.cont_type)) e,
(select distinct d.mobile
from (select *
from test3
where act_id in ('192065', '130444')
and substr(register_time, 1, 6) <= 201401) c,
(select *
from test4
where substr(create_time, 1, 6) <= 201401
and status = 1) d
where c.fee_terminal_id = d.mobile) f
where e.src_terminal_id = f.mobile
但将以上红色标识代码先创建为临时表,再用临时表代替执行上面sql时,速度明显加快。楼主想问个所以然。
因为实际工作中,像楼主这种优化方式,我用过很多,一直以为,借用临时表,先将需要数据抽取出来,然后再根据应用需要,从临时表中
直接抽取数据,这样肯定快的多,只是会牺牲掉临时表的维护。没想到,还有更优化的方式,那就是使用no_merge。
将以上红色部分代码select *from test1 a 改为select /*+ no_merge*/ *from test1 a 此hints方式与创建临时表方式优化是等效的。
网上度娘了下no_merge用法,没有很详细的解释,个人认为,就是一个子查询或视图,本身单独执行不慢,但放入一个子查询中时,oracle会打散原来的执行计划,为了将此子查询或视图,作为一个整体,不被打散,提供no_merge hints方式。
如:查询系统表的二个视图
SELECT COUNT(*) FROM DBA_SEQUENCES, DBA_OBJECTS;
耗时约半小时,单独执行这二个视图count查询时,都在8秒内。
使用no_merge hints如下:
SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ COUNT(*) FROM DBA_SEQUENCES A, DBA_OBJECTS B;
约1分钟。