HI Maximus,
Melanie is of course completely right: read the documentation and the wiki first!
Anyhow, here are just a few steps to demonstrate what kind of information is gathered when:
Let's create a test table:
create table test (kcol1 varchar(10) primary key, nkcol2 integer, nkcol3 varchar(20))
//
insert into test (kcol1) (select rowno from tables a, tables b, tables c where rowno <=10000)
//
update test set nkcol2=mod(kcol1, 100)
//
update test set nkcol3= kcol1
Ok, ncol1 is obviously the pimary key for this table, and has 10000 distinct values,
nkcol3 is a direct copy of it, so it also has 10000 distinct values in it, whereas nkcol2 has only 100 distinct values due to the mod( x, 100) function used in the update.
Let's check the stats right now:
sqlcli DB760=>
sqlcli DB760=> \mu
Multiline mode switched ON
sqlcli DB760=> select t.tablename, f.entrycount, treeindexsize+treeleavessize+lobsize SIZE_IN_PAGES
=> from files f, tables t
=> where
=> t.tableid = f.fileid
=> and f.type='TABLE'
=> and t.tablename='TEST'
=> and t.schemaname=user;
| TABLENAME | ENTRYCOUNT | SIZE_IN_PAGES |
| ---------- | ------------------ | ------------------- |
| TEST | 10000 | 368 |
1 row selected (122.726 msec)
So, the first thing I looked at here, are actually no statistics but current size values from the filedirectory.
I did this just to see what statistical values we could expect.
By the way: to run multiline statements like the above one in sqlcli you've to set \mu first to activate the multiline mode. To mark the end of a statement in multilinemode, you've to add a ';' to the statement, just as you do with Oracle's sqlplus.
sqlcli DB760=> select * from optimizerinformation where tablename='TEST' and schemaname=user;
| SCHEMANAME | TABLENAME | COLUMNNAME | INDEXNAME | DISTINCTVALUECOUNT | CALCULATEDSIZE |
| ---------- | ---------- | ---------- | ---------- | -------------------- | ---------------- |
| LARS | TEST | KCOL1 | ? | 0 | ? |
| LARS | TEST | ? | ? | 0 | 8 |
2 rows selected (24.137 msec)
As we see, even without having ever run a update statistics there are two entries in the table OPTIMIZERINFORMATION (if you check the documentation, you'll figure that OPTIMIZERSTATISTICS is nowadays deprecated).
We've one line for the table (you'll notice the NULL value ? in the field COLUMNNAME) and one for the primary key "KCOL1". Unfortunately the values we see for DISTINCTVALUECOUNT and CALCULATEDSIZE are pretty much nonsense, as we know from the first query.
ATTENTION: here you can see why it is such a big problem for the optimizer if the statistics are not fitting to reality. Although MaxDB uses statistics only for Join-Statements, this table would be estimated to be very small...
Let's gather some stats:
sqlcli DB760=> update statistics test;
0 rows affected (81.886 msec)
sqlcli DB760=> select * from optimizerinformation where tablename='TEST' and schemaname=user;
| SCHEMANAME | TABLENAME | COLUMNNAME | INDEXNAME | DISTINCTVALUECOUNT | CALCULATEDSIZE |
| ----------- | ---------- | ----------- | ---------- | ------------------------ | ------------------ |
| LARS | TEST | KCOL1 | ? | 10000 | ? |
| LARS | TEST | ? | ? | 10000 | 360 |
2 rows selected (24.013 msec)
This looks much better, now!
But what about the other columns? For all non-indexes and non-primary columns you've to specify if you want to gather statistics on them. Usually this is not necessary as such columns won't ever be used for a join transition:
sqlcli DB760=> update statistics column (*) for test;
0 rows affected (140.526 msec)
sqlcli DB760=> select * from optimizerinformation where tablename='TEST' and schemaname=user;
| SCHEMANAME | TABLENAME | COLUMNNAME | INDEXNAME | DISTINCTVALUECOUNT | CALCULATEDSIZE |
| ---------- | ---------- | ---------- | --------- | ------------------ | --------------- |
| LARS | TEST | KCOL1 | ? | 10000 | ? |
| LARS | TEST | NKCOL2 | ? | 100 | ? |
| LARS | TEST | NKCOL3 | ? | 10000 | ? |
| LARS | TEST | ? | ? | 10000 | 360 |
4 rows selected (24.795 msec)
As we see, the OPTIMIZERINFORMATION does indeed show the different results of a UPDATE STAT command - you just have to look right to it.
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.