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

asked 29 Apr '11, 12:06

Martin's gravatar image

Martin
9.0k127165257
accept rate: 14%

edited 29 Apr '11, 12:07

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

(29 Apr '11, 18:50) Volker Barth

No, not at this time.

permanent link

answered 29 Apr '11, 13:34

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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;
permanent link

answered 02 May '11, 17:16

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 03 May '11, 03:31

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815

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.

permanent link

answered 02 May '11, 02:37

Martin's gravatar image

Martin
9.0k127165257
accept rate: 14%

edited 02 May '11, 02:38

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 29 Apr '11, 12:06

question was seen: 10,915 times

last updated: 01 Feb '13, 14:50