Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Query help to refactor

Ricky007Feb 6 2024

Hi experts,

I've outlined the query below, and I'm looking for suggestions to improve its performance or make it more efficient.

I know I have provided only minimal information to achieve, the problem i am facing is slowness while calling the package with udf, please suggest to refactor the query in best possible way.

Thanks in advance

select
count(1)
from
tran_view accounts
inner join tran_info on tran_info.bracket_id=accounts.account_id
inner join event_view etmd on etmd.event_type=tran_info.event_type
inner join tran_recipient_list on tran_recipient_list.tran_info_id=tran_info.tran_info_id
inner join registration recipientorg on recipientorg.registration_id=tran_recipient_list.recipient_org_id
inner join country on country.iso_country_code=tran_info.iso_country_code
inner join security_code on security_code.security_code=tran_info.security_code
left outer join
(
select 
tran_mt.broker_tran_id,
tran_mt.account_id,
bia_ctry.iso_country_code bia_iso_country_code,
bia_sec_code.security_code bia_security_code,
tran_mt.country_id,
tran_mt.security_code_id
FROM
mapping_view tran_mt
inner join country bia_ctry on tran_mt.country_id = bia_ctry.country_id
inner join security_code bia_sec_code on tran_mt.security_code_id=bia_sec_code.security_code_id
bia_map on (bia_map.broker_tran_id=recipientorg.registration_id
and bia_map.account_id=accounts.account_id

and pkg_tran.match_country(
nvl(
bia_map.bia_iso_country_code, '11'
),tran_info.iso_country_code
)=0
and pkg_tran.match_security
(
nvl(
bia_map.bia_security_code,'ALL'
),tran_info.security_code
)=0)
left outer join
confirmation_tb on confirmation_tb.account_id=accounts.account_id
and confirmation_tb.ref_id=tran_info.ref_id
where 
recipientorg.tran_id=p_on_behalf_of
and ev.actor_acro=decode(
p_actor_tran_id,NULL,"EV"."ACTOR_ACRO",p_actor_tran_id
)
and tran_info.security_code=decode(
p_security,null,"tran_info"."security_code",p_security
)
AND tran_recipient_list.is_discarded='C'
AND tran_recipient_list.recipient_org_id=recipientorg.registration_id
and etmd.parent_bo_type='AP098'
order by tran_info_id;
Comments
Post Details
Added on Feb 6 2024
7 comments
184 views