Analytical Function in MV

[kanna]

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!

[pando]

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

[balajiyes]

Thnx for ur help guys.

[balajiyes]

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.

[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?

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.

[balajiyes]

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..

[slimdave]

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.

[balajiyes]

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 :(