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

ID do Projeto: #28396309