|
Is it possible to create a materialized view which uses a parameter during manual refresh? Something like So that e.g. each week the materialized view can be refreshed to reflect the values of last week or so... |
|
No, not at this time. Am I right that a (CREATE) VARIABLE won't do, either, as it is connection-specific by default, and that would be non-deterministic, too? (I'm asking as using such a variable would be a common way to parametrize a normal view.)
(03 May '11, 03:29)
Volker Barth
|
|
If you maintain 'paramtable' with exactly one row representing the current parameter value, then your view can be declared immediate and also can be used for cost-based view matching by the SQL Anywhere Optimizer for queries which don't mention 'paramtable': to let the optimizer know that paramtable has exactly one row, add a primary key column. This is one solution. Other solutions exist as well. Example: create table paramtable (PK int not null primary key, A_PARAM int); insert into paramtable (PK,A_PARAM) values (1, NULL); update paramtable set A_PARAM=PARAM; -- This materialized view can be declared immediate -- and will be also used by the optimizer if the -- query references only the table 'X' CREATE MATERIALIZED VIEW "DBA"."MyView" in system as select * from X, paramtable where X.date > paramtable.A_PARAM and paramtable.PK = 1; |
|
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. I'm assuming you are calling the MV explicitly in your queries (instead of relying on view matching)?
(02 May '11, 03:17)
Volker Barth
Replies hidden
yes, I am not using the view for the optimizer
(02 May '11, 07:24)
Martin
|
I guess you do not want to use an ordinary view (e.g. something like
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.)