Concluído

Oracle Tuning

Need an urgent oracle tuning requirement.

Existing Query is running for more than 5 hours. Need it to tuned for faster results.

The column name

PRD_END_DT : DATE

ACC_LTD_AM : NUMBER

Rest of columns are VARCHAR

I need the result faster and without using re writing in procedure . Want an SQL query result only

select nvl(today.prd_end_dt,prio.prd_end_dt) as dt,nvl(today.rgst_rep_cd,prio.rgst_rep_cd) as rep_cd,

sum(nvl(today.acct_ltd_am,0))- sum(nvl(prio.acct_ltd_am,0)) as DTD,

sum(nvl(today.acct_ltd_am,0))- sum(nvl(prio_week.acct_ltd_am,0)) as WTD,

sum(nvl(today.acct_ltd_am,0))- sum(nvl(prio_month.acct_ltd_am,0))as MTD,

sum(nvl(today.acct_ltd_am,0))- sum(nvl(prio_year.acct_ltd_am,0)) as YTD

from bdss today

full outer join bdss prio

on today.prod_typ_cd=prio.prod_typ_cd

and today.class_cd=prio.class_cd

and today.fund_acct_in=prio.fund_acct_in

and today.rgst_rep_cd=prio.rgst_rep_cd

and prio.prd_end_dt=(select max(PRD_END_DT) from bdss where PRD_END_DT < today.PRD_END_DT and to_char(trunc(PRD_END_DT),'D') not in (1,7))

full outer join bdss prio_week

on today.prod_typ_cd=prio_week.prod_typ_cd

and today.class_cd=prio_week.class_cd

and today.fund_acct_in=prio_week.fund_acct_in

and today.rgst_rep_cd=prio_week.rgst_rep_cd

and prio_week.prd_end_dt=(select max(PRD_END_DT) from bdss where PRD_END_DT < trunc(today.PRD_END_DT,'iw') and to_char(trunc(PRD_END_DT),'D') not in (1,7))

full outer join bdss prio_month

on today.prod_typ_cd=prio_month.prod_typ_cd

and today.class_cd=prio_month.class_cd

and today.fund_acct_in=prio_month.fund_acct_in

and today.rgst_rep_cd=prio_month.rgst_rep_cd

and prio_month.prd_end_dt=(select max(PRD_END_DT) from bdss where PRD_END_DT < trunc(today.PRD_END_DT,'mm') and to_char(trunc(PRD_END_DT),'D') not in (1,7))

full outer join bdss prio_year

on today.prod_typ_cd=prio_year.prod_typ_cd

and today.class_cd=prio_year.class_cd

and today.fund_acct_in=prio_year.fund_acct_in

and today.rgst_rep_cd=prio_year.rgst_rep_cd

and prio_year.prd_end_dt=(select max(PRD_END_DT) from bdss where PRD_END_DT < trunc(today.PRD_END_DT,'yy') and to_char(trunc(PRD_END_DT),'D') not in (1,7))

group by nvl(today.prd_end_dt,prio.prd_end_dt),nvl(today.rgst_rep_cd,prio.rgst_rep_cd)

There is an unique index on the columns PRD_END_DT,rgst_rep_cd,prod_typ_cd,class_cd,fund_acct_in.

The table has approx 76000 records only

Habilidades: SQL, Programação de Banco de Dados, Oracle

Veja mais: mission marketing group pty ltd, easy group pty ltd, didja group pty ltd, vervale business group pty ltd, easy group pty ltd melbourne, vuchiri media group denmark ltd, abasen group pvt ltd, kodak philippines ltd sss number, kapa group pty ltd, kapa group pty ltd robina, hisen electronics pty ltd phone number, ioi marketing group pty ltd, serendib lanka motor company pvt ltd telephone number, surevin internet services ltd contact number, agile group holdings ltd, whoiscom pte ltd contact number, cherry group cis ltd, moriah group pte ltd, moriah group pte ltd singapore, burkat group pty ltd

Acerca do Empregador:
( 0 comentários ) Mumbai, India

ID do Projeto: #28396309

Concedido a:

IllNino

I'm an experienced SQL developer with 11 years of experience in complex queries, including performance tuning. I'll be happy to provide you with the required service

₹1500 INR em 2 dias
(0 Comentários)
0.0

3 freelancers estão ofertando em média ₹4167 nesse trabalho

francoraj

I have more than 14 years of experience in data modelling, SQL , PLSQL and Performance tuning. I can do this work. I need some more details which i can share in the chat

₹4000 INR in 2 dias
(18 Comentários)
4.0
kundansql

Hello there ! I can try to optimize the provided query locally on my Microsoft SQL Server if you want. But for that I need all the table used in this query with or without data; create table statement with be okay. I Mais

₹7000 INR in 2 dias
(2 Comentários)
2.3