The Cost calculation is changed

[ursamajor]

In SQLBase 11.5 the cost of a certain query is calculated of “715.636” and in SQLBase 12 “173.385.137”. This is in the same database / machine / storage.
Is there an explanation of this phenomena?

[Steve Leighton]

Never seen a cost expressed in this way. Always a straight integer value. e.g.

SQLBase cost.png

Anyway, at the risk of teaching what you already know, SQLBase is a cost based optimizer , so 'Update Statistics on Database' or 'Update statistics on table <table name>' will change the SQL cost dramatically, and so should be done regularly on high volatile tables. I believe UNOAD/LOAD does the same thing, which is what may have occurred when you loaded up SB 12.

p.s. How can it be 'the same database' , if one is v11 and one is v12 - completely different animals, esp. if you are using any of the 'RC' isolation levels.

[ursamajor]

I guess I have a little late reply.

The 11.5 database was unloaded and then loaded into sqlbase 12 version.
I added the thousandseparators for visibility purposes.

The isolationlevel also is RL on both databases.
Only that in the Sql.ini readonly parameter is different. 11.5 = 0 and 12=1.

I'll try to repeat the tests and keep your advice in mind and post my results here.

[TerryP]

Hi All,

When you do an unload/load or use the reorganize command, you (still) have to do an explicit "update statistics on database", or the table and index statistics for page counts are all zero, which significantly affects the optimizer.

I remember raising it with Gupta (or Unify/Centura) back with SQL Base 9.0.1, but it still happens in 11.6.2, 12.0.1, 12.1.2 and 12.2.0. If you don't believe me, use the following script to check it out using the island database:

connect island;
reorganize;
select * from systables where name not like 'SYS%';
select * from systables where name not like 'SYS%';
update statistics on database;
select * from systables where name not like 'SYS%';
select * from systables where name not like 'SYS%';
commit;

Cheers,