Do you have the right indexes created? 2009/3/18 oracle <[email protected]>
> > 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 -~----------~----~----~----~------~----~------~--~---
