Views & performance

[Gordowey]

Hi, simple question

I have 2 hugge tables Table A and Table B. I define a view over these
2 tables (select,..joint ,...)

My question is:
Does the DB2 runs the query every time that I access the View? or only
the first time that I create the view.

Is the answer is Yes,...then: Can I set to refresh the view only 1 a
day?

I am asking this, because both tables are really big (about
100.000.000 rows),..and I suppose that running the query to refresh
the view everytime I access it,...will slow down everything,....

thanks, Al

[Serge Rielau]

Re: Views & performance

Gordowey wrote:

Hi, simple question
>
I have 2 hugge tables Table A and Table B. I define a view over these
2 tables (select,..joint ,...)
>
My question is:
Does the DB2 runs the query every time that I access the View?

Yes, views are just "templates" of queries.

Take a look at "Materializ ed Query Tables" (MQT).
You define them with CREATE TABLE ... AS (SELECT...) ...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Hemant Shah]

Re: Views & performance

While stranded on information super highway Gordowey wrote:

Hi, simple question
>
I have 2 hugge tables Table A and Table B. I define a view over these
2 tables (select,..joint ,...)
>
My question is:
Does the DB2 runs the query every time that I access the View? or only
the first time that I create the view.
>
Is the answer is Yes,...then: Can I set to refresh the view only 1 a
day?
>
I am asking this, because both tables are really big (about
100.000.000 rows),..and I suppose that running the query to refresh
the view everytime I access it,...will slow down everything,....
>
thanks, Al

I think what you nees is a materialized query table (MQT).



--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Mark A]

Re: Views & performance

"Gordowey" <albertoiriarte @gmail.comwrote in message
news:2cc0ccbb-7db0-4ddb-9634-fb18dc1ece83@e2 5g2000prg.googl egroups.com...

Hi, simple question
>
I have 2 hugge tables Table A and Table B. I define a view over these
2 tables (select,..joint ,...)
>
My question is:
Does the DB2 runs the query every time that I access the View? or only
the first time that I create the view.
>
Is the answer is Yes,...then: Can I set to refresh the view only 1 a
day?
>
I am asking this, because both tables are really big (about
100.000.000 rows),..and I suppose that running the query to refresh
the view everytime I access it,...will slow down everything,....
>
thanks, Al

A view does not really exist with an data. When you have an SQL statement
that references a view, at execution time DB2 resolves the view to the
actual base tables and rewrites the SQL without the view, and then executes
it.