oracle中优化left join的工作心得

最近领导要求一个任务，大致可以如下解释。

有A表和B表，两个表均有ID和DATE字段，A表存储一个完整的数列SEQ，B表描述了A数列的某段所携带的信息VAL，标记了起始数列号SEQ_START和终止数列号SEQ_END，要求生成一个根据B表标记A表指定数列VAL值的表

A表：

id     date              seq

1      2011/12/1   1

1      2011/12/1   2

1      2011/12/1   3

1      2011/12/2   1

1      2011/12/2   2

1      2011/12/2   3

2      2011/2/1      1

2      2011/2/1      2

2      2011/2/1      3

2      2011/2/1      4

B表：

id     date              seq_start     seq_end     val

1      2011/12/1   1                     2                   'val1'

1      2011/12/2   2                     3                   'val2'

2      2011/2/1     2                     3                   'val3'

完成表：

id     date              seq      val

1      2011/12/1   1           'val1'

1      2011/12/1   2           'val1'

1      2011/12/1   3

1      2011/12/2   1

1      2011/12/2   2           'val2'

1      2011/12/2   3           'val2'

2      2011/2/1      1

2      2011/2/1      2           'val3'

2      2011/2/1      3           'val3'

2      2011/2/1      4

根据以上条件写的语句：

select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md
from (
	select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
	from daa05@dzcxyh c,(
		select jh,create_date,
			count(completion_id) cds
		from (
			select a.well_desc jh,b.completion_id,
				to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
			from cd_well_source@a2 a,cd_completion_t@a2 b
			where b.well_id=a.well_id
		)
		group by jh,create_date
	) d
	where c.jh=d.jh
) e left join (
	select a.well_desc jh,
		to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
		b.completion_id,b.completion_name,b.completion_number,
		nvl(b.top_md,0) top_md,
		nvl(b.bottom_md,0) bottom_md
	from cd_well_source@a2 a,cd_completion_t@a2 b
	where b.well_id=a.well_id
	order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and ((e.syds>=f.top_md and e.syds+e.syhd<=f.bottom_md) or (f.top_md=0 or f.bottom_md=0));

最后发现在最末端on的or条件开销太大

on e.jh=f.jh and e.create_date=f.create_date and ((e.syds>=f.top_md and e.syds+e.syhd<=f.bottom_md) or (f.top_md=0 or f.bottom_md=0))

于是把left join尽量都改写为where，同样的执行结果，where的开销远比left join小

优化后的

select g.*,h.completion_id,h.completion_name,h.completion_number,h.top_md,h.bottom_md
from (
	select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
	from daa05@dzcxyh c,(
		select jh,create_date,
			count(completion_id) cds
		from (
			select a.well_desc jh,b.completion_id,
				to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
			from cd_well_source@a2 a,cd_completion_t@a2 b
			where b.well_id=a.well_id
		)
		group by jh,create_date
	) d
	where c.jh=d.jh
) g left join (
	select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md
	from (
		select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
		from daa05@dzcxyh c,(
			select jh,create_date,
				count(completion_id) cds
			from (
				select a.well_desc jh,b.completion_id,
					to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
				from cd_well_source@a2 a,cd_completion_t@a2 b
				where b.well_id=a.well_id
			)
			group by jh,create_date
		) d
		where c.jh=d.jh
	) e ,(
		select a.well_desc jh,
			to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
			b.completion_id,b.completion_name,b.completion_number,
			nvl(b.top_md,0) top_md,
			nvl(b.bottom_md,0) bottom_md
		from cd_well_source@a2 a,cd_completion_t@a2 b
		where b.well_id=a.well_id
		order by a.well_desc
	) f
	where e.jh=f.jh and e.create_date=f.create_date and ((e.syds>=f.top_md and e.syds+e.syhd<=f.bottom_md) or (f.top_md=0 or f.bottom_md=0))
) h
on g.jh=h.jh and g.create_date=h.create_date and g.xch=h.xch and g.syds=h.syds;

这样就是人类能忍受的程度了