分别执行要join的两个表速度都很快,用inner join,left join,right join执行也很快。就是用full outer join后就挂了。
查看执行计划,要join的其中一个表没用到我强制指定的那个索引。那个表很大,不用这个索引肯定会很慢的。为什么用了full outer join后强制索引就失效了呢?有什么解决的办法啊?我的SQL语句如下:
select t0.*,
'' as le,
decode(tt.out_num, null, 0, tt.out_num) as out_num,
decode(tt.fac_id, null, t0.fac_id, tt.fac_id) as fac_id_new,
decode(tt.machine_id, null, t0.machine_id, tt.machine_id) as machine_id_new,
decode(tt.line_id, null, t0.line_id, tt.line_id) as line_id_new
from (select 'LCM3' as fac_id,
c.shift_id,
c.line_id,
c.machine_id,
d.tact_time,
round((c.run_time - d.over_time) / 3600, 2) as work_time,
round(d.over_time / 3600, 2) as yc_time,
to_char(c.s_date, 'yyyy-mm-dd hh24:mi:ss') as s_date,
to_char(c.e_date, 'yyyy-mm-dd hh24:mi:ss') as e_date,
c.panel_count
from (select shift_id,
line_id,
machine_id,
sum(tac_time * panel_count) as run_time,
min(s_date) as s_date,
max(e_date) as e_date,
sum(panel_count) + 1 as panel_count
from leo_touru_t
where t_date = '2010-10-07'
and shift_id = 'A'
and work_ctr = '100'
group by fac_id, line_id, t_date, shift_id, machine_id
order by line_id, s_date) c,
(select shift_id,
line_id,
machine_id,
sum(tac_time * panel_count) as over_time,
tact_time || '+' || warp as tact_time
from (select t3.shift_id,
t3.line_id,
t3.machine_id,
t3.tac_time -
decode(t4.tact_time, null, 0, t4.tact_time) -
decode(t4.warp, null, 0, t4.warp) as tac_time,
t3.panel_count,
decode(t4.tact_time, null, 0, t4.tact_time) as tact_time,
decode(t4.warp, null, 0, t4.warp) as warp
from leo_touru_t t3, leo_tact t4
where t3.t_date = '2010-10-07'
and t3.shift_id = 'A'
and t3.line_id = t4.mach_id(+)
and t3.machine_id = t4.module(+)
and t3.work_ctr = t4.work_ctr(+)
and t3.work_ctr = '100'
and t3.tac_time >
(decode(t4.tact_time, null, 0, t4.tact_time) +
decode(t4.warp, null, 0, t4.warp))) tt
group by line_id, shift_id, machine_id, tact_time, warp
order by line_id) d
where c.line_id = d.line_id
and c.line_id in ('01', '02', '03', '04', '05', '06', '07', '08')
and c.machine_id = d.machine_id) t0
full outer join (select /*+ index(t WIP_TRANSACTION_INDX3) +*/
'LCM3' as fac_id,
substr(b.prod_id, 1, 11) as machine_id,
c.line_id,
count(t.lot_id) as out_num
from wip_transaction t,
brmproduct_auo b,
brmline c
where t.prod_key = b.prod_key(+)
and t.line_key = c.line_key(+)
and t.txn_datetime > '20101007 070000'
and t.current_step_id = '100'
and t.create_user_key <> '1060476'
and c.line_id in ('01', '02', '03', '04')
group by c.line_id, substr(b.prod_id, 1, 11)) tt
on t0.fac_id = tt.fac_id
and t0.line_id = tt.line_id
and t0.machine_id = tt.machine_id
order by t0.fac_id, t0.line_id, t0.s_date
查看执行计划,要join的其中一个表没用到我强制指定的那个索引。那个表很大,不用这个索引肯定会很慢的。为什么用了full outer join后强制索引就失效了呢?有什么解决的办法啊?我的SQL语句如下:
select t0.*,
'' as le,
decode(tt.out_num, null, 0, tt.out_num) as out_num,
decode(tt.fac_id, null, t0.fac_id, tt.fac_id) as fac_id_new,
decode(tt.machine_id, null, t0.machine_id, tt.machine_id) as machine_id_new,
decode(tt.line_id, null, t0.line_id, tt.line_id) as line_id_new
from (select 'LCM3' as fac_id,
c.shift_id,
c.line_id,
c.machine_id,
d.tact_time,
round((c.run_time - d.over_time) / 3600, 2) as work_time,
round(d.over_time / 3600, 2) as yc_time,
to_char(c.s_date, 'yyyy-mm-dd hh24:mi:ss') as s_date,
to_char(c.e_date, 'yyyy-mm-dd hh24:mi:ss') as e_date,
c.panel_count
from (select shift_id,
line_id,
machine_id,
sum(tac_time * panel_count) as run_time,
min(s_date) as s_date,
max(e_date) as e_date,
sum(panel_count) + 1 as panel_count
from leo_touru_t
where t_date = '2010-10-07'
and shift_id = 'A'
and work_ctr = '100'
group by fac_id, line_id, t_date, shift_id, machine_id
order by line_id, s_date) c,
(select shift_id,
line_id,
machine_id,
sum(tac_time * panel_count) as over_time,
tact_time || '+' || warp as tact_time
from (select t3.shift_id,
t3.line_id,
t3.machine_id,
t3.tac_time -
decode(t4.tact_time, null, 0, t4.tact_time) -
decode(t4.warp, null, 0, t4.warp) as tac_time,
t3.panel_count,
decode(t4.tact_time, null, 0, t4.tact_time) as tact_time,
decode(t4.warp, null, 0, t4.warp) as warp
from leo_touru_t t3, leo_tact t4
where t3.t_date = '2010-10-07'
and t3.shift_id = 'A'
and t3.line_id = t4.mach_id(+)
and t3.machine_id = t4.module(+)
and t3.work_ctr = t4.work_ctr(+)
and t3.work_ctr = '100'
and t3.tac_time >
(decode(t4.tact_time, null, 0, t4.tact_time) +
decode(t4.warp, null, 0, t4.warp))) tt
group by line_id, shift_id, machine_id, tact_time, warp
order by line_id) d
where c.line_id = d.line_id
and c.line_id in ('01', '02', '03', '04', '05', '06', '07', '08')
and c.machine_id = d.machine_id) t0
full outer join (select /*+ index(t WIP_TRANSACTION_INDX3) +*/
'LCM3' as fac_id,
substr(b.prod_id, 1, 11) as machine_id,
c.line_id,
count(t.lot_id) as out_num
from wip_transaction t,
brmproduct_auo b,
brmline c
where t.prod_key = b.prod_key(+)
and t.line_key = c.line_key(+)
and t.txn_datetime > '20101007 070000'
and t.current_step_id = '100'
and t.create_user_key <> '1060476'
and c.line_id in ('01', '02', '03', '04')
group by c.line_id, substr(b.prod_id, 1, 11)) tt
on t0.fac_id = tt.fac_id
and t0.line_id = tt.line_id
and t0.machine_id = tt.machine_id
order by t0.fac_id, t0.line_id, t0.s_date
语句执行慢可能是因为里面和外面都用到排序(order by)。
再次看看你full join前后两个结果集数据有好大,因为你采用的full join 如果数据大,当然也会很慢的
select column from
(SQL语句1)
full outer join
(SQL语句2)
on 条件单独执行SQL语句1和SQL语句2都很快的,如果写成
select column from
(SQL语句1)
left join
(SQL语句2)
on 条件结果也是很快就出来了,就是用了full outer join就挂了
2.少用全表扫描,例如t.create_user_key <> '1060476'中<>,就是全表扫描
/*+ index(t WIP_TRANSACTION_INDX3) +*/失效了
只是SQL语句2里面用到的WIP_TRANSACTION是一张两千多万笔记录的大表,查看full outer join的执行计划,没有走强制指定的WIP_TRANSACTION_INDX3这个索引