Oracle性能调优 [英] Oracle performance Tuning
本文介绍了Oracle性能调优的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下面的oracle查询.运行需要很长时间.您能否建议对此查询进行一些性能调整.
I have below oracle query. It is taking very long time to run. Can you please suggest some performance tuning for this query.
select ss.registration_id as REGISTRATION_ID,
ss.batch_id as BATCH_ID
from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss,
(select a.exceptn_criteria_val,
a.exceptn_criteria_rtrn_val
from EXCEPTN_CRITERIA a,
EXCEPTN_EXPRESSION b
where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
and b.IS_CURRENT_INDCTR = 1
and a.IS_CURRENT_INDCTR = 1) sub_query
where ss.REVENUE_CD = sub_query.exceptn_criteria_val
and ss.batch_id = 821370
and exists (select 'x'
from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss2,
(select a.exceptn_criteria_val,
a.exceptn_criteria_rtrn_val
from EXCEPTN_CRITERIA a,
EXCEPTN_EXPRESSION b
where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
and b.IS_CURRENT_INDCTR = 1
and a.IS_CURRENT_INDCTR = 1) sub_query2
where ss2.REVENUE_CD = sub_query2.exceptn_criteria_val
and ss2.registration_id = ss.registration_id
and ss2.batch_id = ss.batch_id
and ss2.batch_id = 821370
and sub_query2.exceptn_criteria_rtrn_val <> sub_query.exceptn_criteria_rtrn_val)
Order By Ss.Registration_Id,
ss.batch_id;
推荐答案
如果我正确理解,则可以使用相关子查询来查找具有多个不同的 exceptn_criteria_rtrn_val 值的行.如果我对的话,最好使用解析函数:
If I understood correctly you use correlation subquery for finding rows with more than one distinct exceptn_criteria_rtrn_val value. If I'm right will be better use analytic function:
select vw.registration_id as REGISTRATION_ID, vw.batch_id as BATCH_ID
from
(select ss.registration_id as REGISTRATION_ID, ss.batch_id as BATCH_ID
,count(distinct sub_query.exceptn_criteria_rtrn_val) over(partition by ss.registration_id, ss.batch_id) as cnt
from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss,
(select a.exceptn_criteria_val, a.exceptn_criteria_rtrn_val
from EXCEPTN_CRITERIA a, EXCEPTN_EXPRESSION b
where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
and b.IS_CURRENT_INDCTR = 1
and a.IS_CURRENT_INDCTR = 1) sub_query
where ss.REVENUE_CD = sub_query.exceptn_criteria_val
and ss.batch_id = 821370) vw
where cnt > 1
Order By vw.Registration_Id, vw.batch_id;
这篇关于Oracle性能调优的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文