Is there a way to estimate view table size in DB2?

[xeqister]

Greetings,
I would like to know whether there is a way to estimate/calculate
the view table size in DB2. As what I understand, view is just a
logical table and we cannot simply calculate the size based on the
physical table(s) that made up the view. Any ideas?

[Artur]

Data for views is generated dynamically based on the select definition,
so it's difficult to talk about "view table size". If you want to
calculate the size of returned row you can use:

db2 describe output select ...from view

and multiply by numbers of returned rows.
But, if your question was "How much intermediate data db2 has to
process to return the result set from a view" than the answer is "it
depends on the view definition". Some views require whole table scan,
for some db2 can rewrite the query to access base tables with indexes.

I would suggest to play with explaining sql queries (hint: compare
"Show SQL text" and "Show optimized SQL text).

-- Artur Wronski

[xeqister]

Hi Artur,
Thanks for the hints. I will try it out as what you suggested.
Anyway, do you have any idea how the view table is constructed? Is it
made up by pointers to the physical table or there are something else?
Besides what you have suggested, is there a way to check out view table
info from the DB2 SYS schema?
Thanks in advance.......
Artur wrote:

Data for views is generated dynamically based on the select definition,
so it's difficult to talk about "view table size". If you want to
calculate the size of returned row you can use:

db2 describe output select ...from view

and multiply by numbers of returned rows.
But, if your question was "How much intermediate data db2 has to
process to return the result set from a view" than the answer is "it
depends on the view definition". Some views require whole table scan,
for some db2 can rewrite the query to access base tables with indexes.

I would suggest to play with explaining sql queries (hint: compare
"Show SQL text" and "Show optimized SQL text).

-- Artur Wronski

[Knut Stolze]

xe******@gmail.com wrote:

Hi Artur,
Thanks for the hints. I will try it out as what you suggested.
Anyway, do you have any idea how the view table is constructed? Is it
made up by pointers to the physical table or there are something else?
The view definition is comparable to a query that is stored in the database.
After all, a query just returns a table, so you could run another query on
top of it. (Subqueries do exactly that.) And views represent tables -
same thing there.

That's just THE basic SQL concept: everything is in tables and all
operations take tables as input and return a tables as output.
Besides what you have suggested, is there a way to check out view table
info from the DB2 SYS schema?

Have a look an SYSCAT.VIEWS.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[xeqister]

Thanks for the explanation. I wonder I should have aksed this earlier.
Appreciate for all your kind feedback.