I have some views that I use. An example is below. is there anything that PL/SQL can do to make the performance better? The view below works fine for the way that I access it. But for another project, it now uses that same view and the way it accesses it, it is a little slower as it uses this view to create another view. Or is this more of a tuning thing?
Basically in my projects I am trying to use views vs. loading some data into my tables and then running a job to create or update other tables. Instead, the views replace the need for having the jobs. So I guess I am asking if pl/sql can replace the views or make them faster. select g.TERRITORY, coalesce(rev.TRANS_DT ,can.TRANS_DT ) TRANS_DT,g.FISCAL_YEAR,g.FISCAL_QTR, coalesce(rev.revenue,can.revenue)revenue, g.goal from aff_rev_goal g, (select c.TERRITORY, c.TRANS_DT ,c.FISCAL_YEAR,c.FISCAL_QTR, coalesce(c.revenue,0) + coalesce(d.revenue,0) + coalesce(e.revenue,0) revenue from (select a.TERRITORY,TRANS_DT,r.FISCAL_YEAR,r.FISCAL_QTR, sum(revenue) revenue from AFF_TERR_ALIGNMENT a, aar023.quarterly_revenue r where a.ORIGIN_loc_cd=r.ORIGIN_loc_cd and a.REGION_CD != 'CAN' and a.FISCAL_HALF = GET_HALF_BY_QTR(r.FISCAL_QTR) and a.FISCAL_YEAR = r.FISCAL_YEAR group by a.TERRITORY, TRANS_DT, r.FISCAL_YEAR ,r.FISCAL_QTR)c, (select a.TERRITORY, TRANS_DT,r.FISCAL_YEAR, r.FISCAL_QTR, sum (revenue) revenue from AFF_KIAC_ACCT_ALIGNMENT a, quarterly_revenue r where r.KIAC_ACCT_NBR = a.KIAC_ACCT_NBR and a.FISCAL_HALF = GET_HALF_BY_QTR (r.FISCAL_QTR) and a.FISCAL_YEAR = r.FISCAL_YEAR and a.ORIGIN_LOC_CD=r.ORIGIN_LOC_CD group by a.TERRITORY,TRANS_DT,r.FISCAL_YEAR ,r.FISCAL_QTR)d , (select r.TRANS_DT, r.TERRITORY, r.FISCAL_YEAR, r.FISCAL_QTR, revenue revenue from AFF_UNASGNED_QUARTERLY_REV r)e where c.FISCAL_YEAR = d.FISCAL_YEAR(+) and c.FISCAL_QTR = d.FISCAL_QTR(+) and c.TERRITORY = d.TERRITORY(+) and c.FISCAL_YEAR = e.FISCAL_YEAR(+) and c.FISCAL_QTR = e.FISCAL_QTR(+) and c.TERRITORY = e.TERRITORY(+) and c.TRANS_DT = d.TRANS_DT(+) and c.TRANS_DT = e.TRANS_DT(+) ) rev, (select '8-8-19-29-0-0-0' as territory,TRANS_DT,r.FISCAL_YEAR,r.FISCAL_QTR, sum(revenue) revenue from quarterly_revenue r where r.ORIGIN_loc_cd in (select a.ORIGIN_loc_cd from AFF_TERR_ALIGNMENT a where a.FISCAL_HALF = GET_HALF_BY_QTR (r.FISCAL_QTR) and a.FISCAL_YEAR = r.FISCAL_YEAR and a.REGION_CD='CAN') group by '0-0-0-0-0-0-0', TRANS_DT,r.FISCAL_YEAR ,r.FISCAL_QTR)can where g.FISCAL_YEAR = can.FISCAL_YEAR(+) and g.FISCAL_QTR = can.FISCAL_QTR(+) and g.TERRITORY = can.TERRITORY(+) and g.FISCAL_YEAR = rev.FISCAL_YEAR(+) and g.FISCAL_QTR = rev.FISCAL_QTR(+) and g.TERRITORY = rev.TERRITORY(+) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
