问题是：当出现数据量特别大的时候，sql该如何优化，谢谢。
[code="java"]
select t.cate_1_id,
max(t.supplier_count),
max(t.supplier_t_count),
max(t.supplier_a_count),
max(t_rate),
max(a_rate),
max(t.vcode_count),
max(vcode_rate),
max(t.supplier_l_count),
max(l_rate),
max(t.supplier_b_count),
max(b_rate),
max(t.examiner),
(select asset.name
from assetcategory asset
where asset.categoryid = t.cate_1_id),
max(t.category_name) as category_name,
max(t.parentcategoryid) as parentCode
from (with supplier_cnt as
(select count(cate_1_id) as cnt, cate_1_id
from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
null,
sp.prodtype_id,
get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
sp.supplier_id
from t_rsp_supplier_basis sb
left join t_rsp_supplier_prodtype sp
on sb.supplier_id = sp.supplier_id
left join user_ u
on sb.user_id = u.userid
where 1 = 1) t
group by t.cate_1_id), supplier_status_cnt as (select count(cate_1_id) as cnt,
cate_1_id as prodtype_id,
manage_status
from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
null,
sp.prodtype_id,
get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
sp.supplier_id,
sb.manage_status
from t_rsp_supplier_basis sb
left join t_rsp_supplier_prodtype sp
on sb.supplier_id =
sp.supplier_id
left join user_ u
on sb.user_id =
u.userid
where 1 = 1) t
group by t.cate_1_id,
t.manage_status), vcode_cnt as (select count(cate_1_id) as cnt,
cate_1_id as prodtype_id
from (select distinct decode(get_latest_parent_bysubid(sp.prodtype_id),
null,
sp.prodtype_id,
get_latest_parent_bysubid(sp.prodtype_id)) cate_1_id,
sp.supplier_id
from t_rsp_supplier_basis sb
left join t_rsp_supplier_prodtype sp
on sb.supplier_id =
sp.supplier_id
left join user_ u
on sb.user_id =
u.userid
where 1 = 1
and sb.vcode is not null
and sp.prodtype_id is not null) t
group by t.cate_1_id)
select decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid)) as category_2nd_code,
max(sp_cnt.cnt) as supplier_count,
max(sps_cnt_t.cnt) as supplier_t_count,
max(sps_cnt_a.cnt) as supplier_a_count,
decode(max(sp_cnt.cnt),
0,
'',
round((max(sps_cnt_t.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as t_rate,
decode(max(sp_cnt.cnt),
0,
'',
(round((max(sps_cnt_a.cnt) / max(sp_cnt.cnt)) * 100, 2)) || '%') as a_rate,
max(v_cnt.cnt) as vcode_count,
decode(max(sp_cnt.cnt),
0,
'',
(round((max(v_cnt.cnt) / max(sp_cnt.cnt)) * 100, 2)) || '%') as vcode_rate,
max(sps_cnt_l.cnt) as supplier_l_count,
decode(max(sp_cnt.cnt),
0,
'',
round((max(sps_cnt_l.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as l_rate,
max(sps_cnt_b.cnt) as supplier_b_count,
decode(max(sp_cnt.cnt),
0,
'',
round((max(sps_cnt_b.cnt) / max(sp_cnt.cnt)) * 100, 2) || '%') as b_rate,
get_examiner_by_prodtype(ac.categoryid, 11419) as examiner,
max(ac.name),
getassetcategory2(ac.categoryid) as category_name,
ac.categoryid,
max(ac.parentcategoryid) as parentcategoryid,
sp_cnt.cate_1_id
from ASSETCATEGORY ac
left join T_RSP_SUPPLIER_PRODTYPE rsp
on ac.categoryid = rsp.prodtype_id
left join T_RSP_SUPPLIER_BASIS sb
on rsp.supplier_id = sb.supplier_id
left join user_ u
on sb.user_id = u.userid
left join supplier_cnt sp_cnt
on sp_cnt.cate_1_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid))
left join supplier_status_cnt sps_cnt_t
on (sps_cnt_t.prodtype_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid)) and
sps_cnt_t.manage_status = 'T')
left join supplier_status_cnt sps_cnt_a
on (sps_cnt_a.prodtype_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid)) and
sps_cnt_a.manage_status = 'A')
left join supplier_status_cnt sps_cnt_l
on (sps_cnt_l.prodtype_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid)) and
sps_cnt_l.manage_status = 'L')
left join supplier_status_cnt sps_cnt_b
on (sps_cnt_b.prodtype_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid)) and
sps_cnt_b.manage_status = 'B')
left join vcode_cnt v_cnt
on v_cnt.prodtype_id =
decode(get_latest_parent_bysubid(ac.categoryid),
null,
ac.categoryid,
get_latest_parent_bysubid(ac.categoryid))
where ac.categoryid not in
(select a.parentcategoryid from ASSETCATEGORY a)
group by sp_cnt.cate_1_id,
ac.categoryid) t
group by t.cate_1_id
[/code]
其中get_latest_parent_bysubid(ac.categoryid) 是根据子节点获得所有一级节点。同时，也有数据是本身就是一级节点且无子节点。
