跪求指点SELECT *
FROM (SELECT GROUPER.*,
ROW_NUMBER() OVER(PARTITION BY GROUPER.GENERAL_BIZ_NUM, GROUPER.comment_issuer_role_cd ORDER BY GROUPER.comment_issuing_time DESC) AS ROWNUMBER
FROM (select temp.general_biz_num,
temp.processed_by,
temp.party_name,
temp.customer_num,
temp.biz_type_cd,
temp.comment_issuing_time,
temp.rating_business_property_cd,
temp.apply_org_cd,
temp.apply_org_name org_name,
temp.comment_issuer_role_cd,
temp.project_status_cd,
(select ui.user_name
from user_info ui
where ui.user_num =
decode(mp.participant_user_num,
null,
(select appc.comment_issuer_user_num
from credit_rating_appr_comm appc
where appc.comment_issuer_role_cd(+) =
'R0020'
and appc.general_biz_num(+) =
temp.general_biz_num
and appc.comment_issuing_time =
(select max(appc2.comment_issuing_time)
from credit_rating_appr_comm appc2
where appc2.comment_issuer_role_cd(+) =
'R0020'
and appc2.general_biz_num(+) =
temp.general_biz_num)),
mp.participant_user_num)) user_name,
decode(mp.participant_user_num,
null,
(select appc.comment_issuer_user_num
from credit_rating_appr_comm appc
where appc.comment_issuer_role_cd(+) = 'R0020'
and appc.general_biz_num(+) =
temp.general_biz_num
and appc.comment_issuing_time =
(select max(appc2.comment_issuing_time)
from credit_rating_appr_comm appc2
where appc2.comment_issuer_role_cd(+) =
'R0020'
and appc2.general_biz_num(+) =
temp.general_biz_num)),
mp.participant_user_num) decode_participant_user_num,
temp.meeting_sequence_num,
temp.approve_type_cd,
mp.participant_user_num,
cra.approve_conclusion_cd,
cra.r3_cd r3_approve,
cra.approve_date
from (select
wb.biz_event_number general_biz_num,
wt.assigned_to processed_by,
wb.customer_name party_name,
wb.customer_number customer_num,
wb.biz_type_code biz_type_cd,
wt.end_time comment_issuing_time,
wb.App_Attribute_Code rating_business_property_cd,
wb.apply_org_name apply_org_name,
wb.apply_org_code apply_org_cd,
wt.rec_role_code comment_issuer_role_cd,
mcs.meeting_sequence_num meeting_sequence_num,
mcs.approve_type_cd approve_type_cd,
mpc.meeting_id,
tsr.project_status_cd,
wb.simplied_approval,
wb.single_approver
from wf_task_inst wt
left join wf_biz_info wb on wb.bp_inst_id =
wt.bp_inst_id
left join task_status_relation tsr on tsr.task_code =
wt.task_code
left join meeting_process_case mpc on mpc.general_biz_num =
wb.biz_event_number
left join meeting_participant mp1 on mp1.meeting_id =
mpc.meeting_id
left join meeting_co_signature mcs on mcs.meeting_id =
mpc.meeting_id
where
wt.assigned_to = '010000100'
and wb.biz_type_code in ('1', '4')
and (wt.task_action > '0' or wt.task_action < '0')
and (wt.task_action > '8' or wt.task_action < '8')
and (wt.task_action > '7' or wt.task_action < '7')
and wt.processed_time <=
to_date('2012-06-29 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2012-06-26 00:00:00',
'yyyy-MM-dd HH24:mi:ss')
union all
select wb.biz_event_number general_biz_num,
wt.assigned_to processed_by,
wb.customer_name party_name,
wb.customer_number customer_num,
wb.biz_type_code biz_type_cd,
wt.end_time comment_issuing_time,
wb.App_Attribute_Code rating_business_property_cd,
wb.apply_org_name apply_org_name,
wb.apply_org_code apply_org_cd,
wt.rec_role_code comment_issuer_role_cd,
mcs.meeting_sequence_num meeting_sequence_num,
mcs.approve_type_cd approve_type_cd,
mpc.meeting_id,
tsr.project_status_cd,
wb.simplied_approval,
wb.single_approver
from wf_task_inst wt
join wf_biz_info wb on wb.bp_inst_id = wt.bp_inst_id
left join task_status_relation tsr on tsr.task_code =
wt.task_code
left join meeting_process_case mpc on mpc.general_biz_num =
wb.biz_event_number
left join meeting_participant mp1 on mp1.meeting_id =
mpc.meeting_id
left join meeting_co_signature mcs on mcs.meeting_id =
mpc.meeting_id
where (mp1.participant_user_num = '010000100' and
wt.assigned_to = mp1.recorder_user_num and
wt.role_code = 'R0020')
and wb.biz_type_code in ('1', '4')
and wt.task_action != '0'
and wt.task_action != '8'
and wt.task_action != '7'
and wt.processed_time <=
to_date('2012-06-29 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2012-06-26 00:00:00',
'yyyy-MM-dd HH24:mi:ss')) temp,
(select appc0.comment_issuer_user_num,
appc0.comment_issuer_role_cd,
appc0.general_biz_num
from credit_rating_appr_comm appc0
where appc0.comment_issuer_role_cd = 'R0020') appc,
(select cra0.approve_conclusion_cd,
cra0.approve_date,
cra0.r3_cd,
cra0.app_num
from credit_rating_approve cra0
where cra0.approve_status_cd = '3') cra,
(select meeting_id, participant_user_num
from meeting_participant mp0
where mp0.participant_role_cd = 'R0020') mp
where mp.meeting_id(+) = temp.meeting_id
and cra.app_num(+) = temp.general_biz_num
and appc.general_biz_num(+) = temp.general_biz_num) GROUPER)
WHERE ROWNUMBER = 1
ORDER BY COMMENT_ISSUING_TIME DESC性能优化SQLselect
FROM (SELECT GROUPER.*,
ROW_NUMBER() OVER(PARTITION BY GROUPER.GENERAL_BIZ_NUM, GROUPER.comment_issuer_role_cd ORDER BY GROUPER.comment_issuing_time DESC) AS ROWNUMBER
FROM (select temp.general_biz_num,
temp.processed_by,
temp.party_name,
temp.customer_num,
temp.biz_type_cd,
temp.comment_issuing_time,
temp.rating_business_property_cd,
temp.apply_org_cd,
temp.apply_org_name org_name,
temp.comment_issuer_role_cd,
temp.project_status_cd,
(select ui.user_name
from user_info ui
where ui.user_num =
decode(mp.participant_user_num,
null,
(select appc.comment_issuer_user_num
from credit_rating_appr_comm appc
where appc.comment_issuer_role_cd(+) =
'R0020'
and appc.general_biz_num(+) =
temp.general_biz_num
and appc.comment_issuing_time =
(select max(appc2.comment_issuing_time)
from credit_rating_appr_comm appc2
where appc2.comment_issuer_role_cd(+) =
'R0020'
and appc2.general_biz_num(+) =
temp.general_biz_num)),
mp.participant_user_num)) user_name,
decode(mp.participant_user_num,
null,
(select appc.comment_issuer_user_num
from credit_rating_appr_comm appc
where appc.comment_issuer_role_cd(+) = 'R0020'
and appc.general_biz_num(+) =
temp.general_biz_num
and appc.comment_issuing_time =
(select max(appc2.comment_issuing_time)
from credit_rating_appr_comm appc2
where appc2.comment_issuer_role_cd(+) =
'R0020'
and appc2.general_biz_num(+) =
temp.general_biz_num)),
mp.participant_user_num) decode_participant_user_num,
temp.meeting_sequence_num,
temp.approve_type_cd,
mp.participant_user_num,
cra.approve_conclusion_cd,
cra.r3_cd r3_approve,
cra.approve_date
from (select
wb.biz_event_number general_biz_num,
wt.assigned_to processed_by,
wb.customer_name party_name,
wb.customer_number customer_num,
wb.biz_type_code biz_type_cd,
wt.end_time comment_issuing_time,
wb.App_Attribute_Code rating_business_property_cd,
wb.apply_org_name apply_org_name,
wb.apply_org_code apply_org_cd,
wt.rec_role_code comment_issuer_role_cd,
mcs.meeting_sequence_num meeting_sequence_num,
mcs.approve_type_cd approve_type_cd,
mpc.meeting_id,
tsr.project_status_cd,
wb.simplied_approval,
wb.single_approver
from wf_task_inst wt
left join wf_biz_info wb on wb.bp_inst_id =
wt.bp_inst_id
left join task_status_relation tsr on tsr.task_code =
wt.task_code
left join meeting_process_case mpc on mpc.general_biz_num =
wb.biz_event_number
left join meeting_participant mp1 on mp1.meeting_id =
mpc.meeting_id
left join meeting_co_signature mcs on mcs.meeting_id =
mpc.meeting_id
where
wt.assigned_to = '010000100'
and wb.biz_type_code in ('1', '4')
and (wt.task_action > '0' or wt.task_action < '0')
and (wt.task_action > '8' or wt.task_action < '8')
and (wt.task_action > '7' or wt.task_action < '7')
and wt.processed_time <=
to_date('2012-06-29 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2012-06-26 00:00:00',
'yyyy-MM-dd HH24:mi:ss')
union all
select wb.biz_event_number general_biz_num,
wt.assigned_to processed_by,
wb.customer_name party_name,
wb.customer_number customer_num,
wb.biz_type_code biz_type_cd,
wt.end_time comment_issuing_time,
wb.App_Attribute_Code rating_business_property_cd,
wb.apply_org_name apply_org_name,
wb.apply_org_code apply_org_cd,
wt.rec_role_code comment_issuer_role_cd,
mcs.meeting_sequence_num meeting_sequence_num,
mcs.approve_type_cd approve_type_cd,
mpc.meeting_id,
tsr.project_status_cd,
wb.simplied_approval,
wb.single_approver
from wf_task_inst wt
join wf_biz_info wb on wb.bp_inst_id = wt.bp_inst_id
left join task_status_relation tsr on tsr.task_code =
wt.task_code
left join meeting_process_case mpc on mpc.general_biz_num =
wb.biz_event_number
left join meeting_participant mp1 on mp1.meeting_id =
mpc.meeting_id
left join meeting_co_signature mcs on mcs.meeting_id =
mpc.meeting_id
where (mp1.participant_user_num = '010000100' and
wt.assigned_to = mp1.recorder_user_num and
wt.role_code = 'R0020')
and wb.biz_type_code in ('1', '4')
and wt.task_action != '0'
and wt.task_action != '8'
and wt.task_action != '7'
and wt.processed_time <=
to_date('2012-06-29 23:59:59',
'yyyy-MM-dd HH24:mi:ss')
and wt.processed_time >=
to_date('2012-06-26 00:00:00',
'yyyy-MM-dd HH24:mi:ss')) temp,
(select appc0.comment_issuer_user_num,
appc0.comment_issuer_role_cd,
appc0.general_biz_num
from credit_rating_appr_comm appc0
where appc0.comment_issuer_role_cd = 'R0020') appc,
(select cra0.approve_conclusion_cd,
cra0.approve_date,
cra0.r3_cd,
cra0.app_num
from credit_rating_approve cra0
where cra0.approve_status_cd = '3') cra,
(select meeting_id, participant_user_num
from meeting_participant mp0
where mp0.participant_role_cd = 'R0020') mp
where mp.meeting_id(+) = temp.meeting_id
and cra.app_num(+) = temp.general_biz_num
and appc.general_biz_num(+) = temp.general_biz_num) GROUPER)
WHERE ROWNUMBER = 1
ORDER BY COMMENT_ISSUING_TIME DESC性能优化SQLselect
解决方案 »
- oracle中有没有算分位数的函数？或者怎么能算出来分位数？
- 安装"Oracle Database Configuration Assistant" 失败的错误
- Sys_Connect_By_Path 在10G 第4版的问题
- CURSOR的问题
- oracle的clob类型的字段,存储的文本内容,怎样转换为字符创并去掉换行符?
- 怎样进入SQL Plus 要用户密码和用户名，教材里说是system 密码是manager怎么不行的。^_^
- 散分！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
- DB link 總是Not Active
- Oracle解析sql语句的过程？（问题解决好，还可以加分）
- 菜鸟请教:delete trigger 设计...
- AWK替换操作，请大神指点
- 遍历部门表问题
本人 优化过几千个SQL ，个人博客http://blog.csdn.net/robinson1988/无论是 MYSQL DB2 MS SQL ORACLE 关系型的 SQL 优化思路一样。
如果你要优化sql，首先要知道业务(input & output 分别是什么)，如果知道业务，那么就可以进行拆分，至少我是这么认为的，呵呵。修改这么大sql 的risk 可能要比拆分的risk 大的多。