求 DBA 带飞， sql 优化问题，左链接字句太多，加上分析排序，效率很慢

select u.final_ng_cnt,
v.color_cnt,
v.color_total,
y.weight_total,
y.weight_pass_cnt,
z.residual_total,
z.residual_total - z.last_ng_cnt as residual_pass_cnt,
p.package_total,
p.package_pass_cnt
from (select 'aaa' as batch_no,
sum(case
when t.result = 0 then
1
else
0
end) final_ng_cnt,
count(distinct t.code) as final_total
from (select a.item,
a.code,
a.result,
a.datetime,
a. recordid,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid
where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(nvl(b.batch_no, '@@@'), '@@@') = 0
and b.batch_no = 'aaa') t
where t.final_flag = 1) u
left join (select 'aaa' as batch_no,
sum(case
when c.first_rs = 1 then
1
else
0
end) color_cnt,
count(c.code) as color_total

from (select b.batch_no,
a.item,
a.code,
a.result as first_rs,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '00000000')) as first_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid


where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(a.item, '0019') > 0
and b.batch_no = 'aaa') c
where c.first_flag = 1
) v on u.batch_no = v.batch_no
left join (select 'aaa' as batch_no,
sum(case
when instr(g.result, '0') = 0 then
1
else
0
end) weight_pass_cnt,
count(g.code) as weight_total
from (select k.code,
wm_concat(k.item) rank,
wm_concat(k.result) as result
from (select distinct c. recordid,
c.batch_no,
c.line_num,
c.station_id,
c.thread_id,
c.item,
c.code,
c.result,
c.datetime,
c.first_flag
from (select a. recordid,
b.batch_no,
a.line_num,
a.station_id,
a.thread_id,
a.item,
a.code,
a.result,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid

where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(a.item, '0823') > 0
and b.batch_no = 'aaa'
union all
select a. recordid,
b.batch_no,
a.line_num,
a.station_id,
a.thread_id,
a.item,
a.code,
a.result,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid

where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(a.item, '0923') > 0
and b.batch_no = 'aaa'
union all
select a. recordid,
b.batch_no,
a.line_num,
a.station_id,
a.thread_id,
a.item,
a.code,
a.result,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000')) as first_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid

where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(a.item, '1023') > 0
and b.batch_no = 'aaa') c) k
group by k.code) g
where instr(g.rank, '0823') > 0
and instr(g.rank, '0923') > 0
and instr(g.rank, '1023') > 0) y on u.batch_no =
y.batch_no
left join (select 'aaa' as batch_no,
sum(count(c.code)) as residual_total,
sum(sum(case
when c.final_flag = 1 and c.result = 0 then
1
else
0
end)) last_ng_cnt
from (select b.batch_no,
a.line_num,
a.station_id,
a.thread_id,
a.item,
a.code,
a.result,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '0000000') desc) as final_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid


where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and instr(a.item, '0823') = 0
and instr(a.item, '0923') = 0
and instr(a.item, '1023') = 0
and instr(a.item, '23') > 0
and b.batch_no = 'aaa') c
group by c.code) z on u.batch_no = z.batch_no
left join (select 'aaa' as batch_no,
sum(case
when g.first_rs = 1 and g.first_flag = 1 then
1
else
0
end) package_pass_cnt,
count(distinct g.code) as package_total

from (select 'aaa' as batch_no,
a.item,
a.code,
a.result as first_rs,
a.datetime,
dense_rank() over(partition by a.code, a.fruit_name, a.item order by nvl(a. recordid, '00000000')) as first_flag
from fruit_table_a a
inner join fruit_table_b b on a. recordid =
b.productid

where instr(a.code, '000000000000') = 0
and instr(a.code, '9999999') = 0
and b.batch_no = 'aaa'
and instr(a.item, '0020') > 0) g) p on u.batch_no =
p.batch_no