Materialized view with Parameter?

[VolkerBarth]

Is it possible to create a materialized view which uses a parameter during manual refresh? Something like

CREATE MATERIALIZED VIEW "DBA"."MyView" in system as 
select * from X where X.date > PARAM

So that e.g. each week the materialized view can be refreshed to reflect the values of last week or so...

[VolkerBarth]

I guess you do not want to use an ordinary view (e.g. something like

select * from x where datediff(dd, x.date, current date) >= 7

for performance reasons. May a global shared by all temporary table be of help? (I'm aware that temporary tables do not get used by view matching.)

[VolkerBarth]

(That would better asked as a separate question...): No, materialized views were introduced in v10.

[MCMartin]

The workaround I use is an extra table just with the value of the parameter and then in the materialized view:

select * from X where X.date > (select max(PARAM) from paramtable)

Not elegant but it works.

[VolkerBarth]

I'm assuming you are calling the MV explicitly in your queries (instead of relying on view matching)?

[MCMartin]

yes, I am not using the view for the optimizer