yatou_0209
望不到光的黑
2014-07-09 14:36
浏览 350
已采纳

oracle 根据树形结构查询一级,二级报表sql 优化问题

问题是:当出现数据量特别大的时候,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) 是根据子节点获得所有一级节点。同时,也有数据是本身就是一级节点且无子节点。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • long2007
    long2007 2014-07-16 14:43
    已采纳

    1、不建议用如此复杂的sql语句。因为这样不利于阅读及维护。建议拆分成多条语句或写一个存储过程;
    2、较靠前的查询条件应该会先执行,在较靠前的查询中过滤掉尽可能多的无用数据,这样后面的查询需要处理的数据量较小,自然就提升了速度;
    3、注意left join,right join和inner join的区别。

    时间和使用机器的关系无法给你仔细分析具体语句,大概提这三点意见供你参考吧。

    点赞 评论

相关推荐