The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
8.6k114149237
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.7k568104
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
29.3k287438644

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
8.6k114149237
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: 3,702 times

last updated: 01 Feb '13, 14:50