DB2 utility which will estimate the number of rows that will be return 1

[tbtcust]

Hi All

Is there a DB2 utility, which will estimate the number of rows a query will return?

We are developing a web application and would like to present this info to the end-user without actually running the query.

Environment: Linux, DB2 Version 8 and 9.

Thanks in advance for any help.

 

[MarcLodge]

tbt103,
I can't think of a utli that will display this information. Is it worthwhile considering a SELECT COUNT(*) based on the query?

Marc

 

[blom0344]

An explain plan that can use up-to-date statistics would give some clue , but unfortunately the data concerned is hard to capture.
If the query performs a database-level aggregation, then you are sure that it pretty much impossible to know the query output beforehand..

Ties Blom

 

[tbtcust]

MarcLodge and blom0344, thanks for your input. I was hoping there was something. Looks like I'm stuck with Select (*).

 

[sathyarams]

You have given very little info about your application ...

An option is to use MQTs ...

Use IMMEDIATE REFRESH or DEFERRED REFRESH depending on the accuracy of the count required and the performance of the application updating the base table

HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[tbtcust]

sathyarams, thanks for replying. The data is static transaction data loaded to the DB overnight. I'm not familiar MTQs.

From what I'm reading on the web, looks like I could use a subset of the transaction data in the MQT. I'm thinking just the keys. Then use that table for my select count(*). Am I on the right track?

 

[MarcLodge]

Sathyaram,
That is a very good post, for which you must catch a star. Am I right that, in this context tbt103 could set up the specific query he requires against each table, and the system would then maintain that data as it goes. All tbt103 would then have to do is to query the MQT for the count.

The only problem I see with this method is if the underlying query for the MQT contains variables which may change.

Thanks for the info though Sathyaram, it was something I've not come across before.

Marc

 

[sathyarams]

Marc, Thanks for the * ;-)


Here's a brief on MQTs ..

MQT is Materialized Query Table - the definition of which the optimizer is aware of .. Therefore, you can write a query against the base tables of the MQT, and if the optimizer thinks that the data is available in the MQT and its access is more efficient, data is read from the MQT . This is my preferred method - as the appl is not dependent on the MQT and therefore any delays or problems in building them does not have an impact on the output of the appl ..

Of course, there is nothing to stop you from querying the MQT directly ..

MQTs - like other tables can have indexes and needs RUNSTATS to help the optimizer ..

MQTs can be REFRESH IMMEDIATE or REFRESH DEFERRED.

MQTs can be MAINTAINED BY USER or MAINTAINED BY SYSTEM or MAINTAINED BY FEDERATED_TOOL



Eg:

If the base table tab1 is

col1
col2
col3
col4
col5
..
...
col20

, you can define a MQT like

create table mqt1


as
(select
col1 , col2, col3, col4
count(*) as row_ct
from eudw.dwa_mrch_sales
group by
col1,col2,col3,col4
)
data initially deferred
refresh immediate
[ Can be based on a more complex query as well ]

followed by

REFRESH TABLE MQT1

Becuase it is refresh immediate, any update to tab1 will be updated to mqt1 (and obviously there will be a perf degradation)


For this defintion, all the following requests are likely to use mqt1

select col1,col2,col3,col4,count(*) from tab1 group by col1,col2,col3,col4
select col1,count(*) from tab1 where col2=5 group by col1
select col1,count(*) from tab1 group by col1


select col1,col2,count(*) from tab1 group by col1,col2

etc ..

Even queries like

select distinct col1,col2 from tabl

might use mqt1


Of course, not all MQTs and queries against the base tables are as simple ... So, you may have to test your MQT defn and queries ..


MQT is one area where the optimizer is getting better with every new release of DB2 and so it is worth a try...


Hope this helps

Cheers
Sathyaram


For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[blom0344]

http://www.db2mag.com/showArticle.jhtml?articleID=12803220

for additional bed-time reading

Ties Blom

 

[tbtcust]

Thank you all for the MQT Edu.