By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
Thanks for the explanation. I wonder I should have aksed this earlier.
Appreciate for all your kind feedback.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.