MAXDB : Data Distribution

[Former Member]

Hi,

What is the counterpart for Histograms (in Oracle) in MaxDB? i.e How can we get the data distribution in MAXDB?

[lbreddemann · Accepted solution]

> What is the counterpart for Histograms (in Oracle) in MaxDB?

There are no precomputed histograms in MaxDB.

For each single-table-query the optimizer gets a sample of the table data fitting to the predicates to estimate the cardinality of them.

> i.e How can we get the data distribution in MAXDB?

You would have to code your own SQL queries with aggregate functions for that.

KR Lars

[Former Member]

Ok. Thanks a lot!!. That was a really helpful answer.

I would like to know whether kernel trace provides the information about this optimization and sampling?? If not, how can this optimizer trace be accessed or found?

[lbreddemann]

> I would like to know whether kernel trace provides the information about this optimization and sampling?? If not, how can this optimizer trace be accessed or found?

Yes, activating the kernel trace for the optimizer would create further informational output.

Anyhow, this output is intended for the developer of the optimizer and difficult to understand.

It's nothing like the CBO trace of oracle.

Really - if you want to understand better what the optimizer "thought" about your data, then use EXPLAIN JOIN and EXPLAIN SEQUENCE. These commands produce output that deliver some useful information.

Apart from that it should be clear, that the inner workings of the optimizer are not documented and changed all the time to improve performance and stability. In 7.6 alone there had been so many changes and enhancements.

As the general design goal of MaxDB is ease of use it shouldn't be necessary to know what the optimizer exactly does.

If you find your statement is badly optimized - post it here and we have a look.

Maybe it's a bug that should be fixed.

regards,

Lars

[Former Member]

And from where can we get the statistics of the number of rows selected for sampling? Does the RowsRead column of the table DBADMIN.SYSMONITOR stands for the same? If not, what does it signify?

[lbreddemann]

Hi there,

there is no place where you can check on what the sampling did or found (ok - you may try and read the optimizer trace - good luck with that...).

Anyhow, what I can tell is that the sampling algorithm does not work row-wise but uses certain characteristics of the B*Trees to estimate data distribution.

For further information you may want to read the available documentation first, especially the Wiki content: [SQL Optimizer|https://wiki.sdn.sap.com/wiki/x/DSg]

As with other DBMS the optimizer is one of the most complex code structures in MaxDB and not easy to explain just in a forum question.

regards,

Lars

[Former Member]

For each single-table-query the optimizer gets a sample of 
the table data fitting to the predicates to estimate the cardinality of them.

Can you elaborate this point ? And I would even like to know the process in case of JOIN queries...