Verifying update statistics run on tables
Hi,
I executed the command UPDATE STATISTICS for some table columns supplying a 'sample definition'. It ran successfully and returned with an OK message. My question is -
Where should I look to find the details of the statistics that have been created as a result of this run? More specifically, I am looking for histograms for the columns; or any other kind of detailed information that MaxDB stores as part of table column statistics.
~maximus
P.S.- Some background:
System details - MaxDB version - 7.6.05.09, Platform - Linux, 64-bit
I am novice user of MaxDB. I have installed MaxDB, created a database, created tables and loaded them with data. Now I want to update the statistics for the columns of the tables.
I looked in the table OPTIMIZERSTATISTICS but it only gives the number of distinct values, but not the histogram. In fact, even the PAGECOUNT field in this table always has nothing but a '?' mark.
Hi Maximus,
Ok, I tried to reproduce your examples.
The first thing here is obvious: col2 is not indexed, so although conditions like 't2.c2<3' reduce the result set a lot, MaxDB cannot use this knowledge, as it can either use the primary key or an table scan for the data access.
So, for this unindexed col2 there won't be any evaluation as it cannot be used for data access at all.
Create an index on col2 and you'll see that the estimations will be much better.
Concerning the 3-way-join: I just can't reproduce this.
That's what I get:
explain join select * from t1, t1 t3, t2 where t1.c1=t3.c1 and t1.c1=t2.c1 and t1.c2=1 and t2.c2<3;
STRATEGY OWNER TABLENAME MAX ADDNL. ROWS MULTIPLIER REVERSE MULTIPLIER ADDITIONAL PAGES ADDITIONAL ROWS ACCUMULATED_COSTS
LARS T2 997473 1 1 8673.67826086956 997473 4281
JOIN VIA SINGLE KEY LARS T1 999141 1 1 12468.4125 997473 8794.45248868778
JOIN VIA SINGLE KEY T3 999141 1 1 13853.7916666667 997473 13307.9049773756
That's the correct join order.
regards,
Lars
-
Lars Breddemann replied