Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.

Former Member
replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question