Analytical Function in MV - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Analytical Function in MV

  1. #11
    Join Date
    Jun 2003
    Location
    India
    Posts
    24
    hey i am sorry that was my mistake....

    But lot of times we noted date devey23uk is just posting without any useful information..i think he is very eager to increase the number of post in this forum.rite?

    Wow!
    Luv
    Kanna

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by kanna
    Hi Tomazz,

    Simple question.. Can we use lag function while creating materialized view with fast refresh option?
    for god sake RTFM, the link is even given geez

  3. #13
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx for ur help guys.
    Cheers!
    OraKid.

  4. #14
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Pando...

    Iam not able to use even the following statement in my SELECT statement on my MV create script...

    DECODE(SUM(NVL(A.CYCLE_OPENING_BAL, 0)), 0, 0, ((SUM(NVL(A.TOTAL_BAL, 0)) / SUM(NVL(A.CYCLE_OPENING_BAL, 0))) * 100)) CURRENT_DOM_ROLL_RATE,

    DECODE(SUM(NVL(A.CYCLE_OPENING_BAL, 0)), 0, 0, (((SUM(NVL(A.TOTAL_BAL, 0)) / SUM(NVL(A.CYCLE_OPENING_BAL, 0))) * 100) - MAX(NVL(E.ROLL_RATE_GOAL_PCT, 0)))) ROLL_RATE_VARIANCE,

    SUM(NVL(A.TOTAL_BAL, 0)) - (SUM(NVL(A.CYCLE_OPENING_BAL, 0)) * (MAX(NVL(E.ROLL_RATE_GOAL_PCT, 0)) / 100)) BETTER_WORSE_GOAL_AMOUNT

    I have to REFRESH my MV in FAST option only. Since my FACT table has 550M row. Partitioned by year month (24 months data in fact tables). Each partition has 20+M records.

    You might ask... you must have gone thru the dox. before u write the code. YES. But we face the problem only during our new requirement from our client...

    Thnx in Advance.

  5. #15
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Did you include ...

    COUNT(*)
    COUNT(A.CYCLE_OPENING_BAL)
    COUNT(A.TOTAL_BAL)
    COUNT(E.ROLL_RATE_GOAL_PCT)

    ... in your select list for the query?

    Also, I would not include these complex statements in your MV definition -- you shoul limit your self to simple aggregate functions, like SUM(A.CYCLE_OPENING_BAL), MAX(E.ROLL_RATE_GOAL_PCT) etc., then calculate CURRENT_DOM_ROLL_RATE, ROLL_RATE_VARIANCE, and BETTER_WORSE_GOAL_AMOUNT by querying the MV.

    I expect that what is killing your fast refresh ability is partly statements like SUM(NVL(A.TOTAL_BAL, 0)), which would in any case be better as the equivalent NVL(SUM(A.TOTAL_BAL),0) -- the NVL() can be applied to the SUM() "externally" to the MV.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #16
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Slimdave,

    Did you include ...

    COUNT(*)
    COUNT(A.CYCLE_OPENING_BAL)
    COUNT(A.TOTAL_BAL)
    COUNT(E.ROLL_RATE_GOAL_PCT)

    ... in your select list for the query?


    I have all these in my SELECT list.


    Also, I would not include these complex statements in your MV definition -- you shoul limit your self to simple aggregate functions, like SUM(A.CYCLE_OPENING_BAL), MAX(E.ROLL_RATE_GOAL_PCT) etc., then calculate CURRENT_DOM_ROLL_RATE, ROLL_RATE_VARIANCE, and BETTER_WORSE_GOAL_AMOUNT by querying the MV.


    Here is the problem...

    The value derived from this statement in Materialized View MV_A (DECODE(SUM(NVL(A.CYCLE_OPENING_BAL, 0)), 0, 0, (((SUM(NVL(A.TOTAL_BAL, 0)) / SUM(NVL(A.CYCLE_OPENING_BAL, 0))) * 100) - MAX(NVL(E.ROLL_RATE_GOAL_PCT, 0)))) ROLL_RATE_VARIANCE) is used when we create Materialized View MV_B.

    If not here in MV_A Materialized View created stmt... I will be using the same statement in Materialized View MV_B created stmt.

    Thnx in Advance... If I crack this... I'll hv a good weekend..
    Cheers!
    OraKid.

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why are you including it in any materialized view definition? Why not in the query you apply on top of the MV? You could just put a regular view over the top of the MV, and put your comples formula in there.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #18
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx Slimdave. Looks good.. I'll try n let u know.

    Will that(View) be faster... 'coz my FACT table will be having 15-20M records/month. Since I could not use "LAG" Analytical function Iam forced to use a view :(
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width