Re: Upgrade horror stories/victories

We've upgraded several 4g databases without difficulty. In every case we

a) exported the databases
b) uninstalled O734, or re-installed NT
c) installed O815
d) manually created database/tablespaces/users/etc/import

This aproach I felt was not only safer, but had the added advantage that our tables and indexes went back to single segments again.

Our only issue was with the O815i Optimizer. It makes different choices than the O734 version, and so our 'optimized' select statements needed to be tweaked again.

One of my favourite examples of this is

    select * from MyTable where MyPrinaryKey < 100;

In O734 this ran instantly using an index. In O815 this causes a full table scan.

We started thinking about it, and decided that O734 was making the mistake. The optimizer doesn't know if this column is all positive numbers or not. It's possible that all of them are negative, and hence a full table scan would be faster. Of course the optimizer could bother to check the root blocks of the indexes to get the min and max values -- but as I recall the rule is that the optimizer shouldn't have to read from disk to make up its mind. (But who really knows how the Optimzer works?)

So we ran some command, (sorry I can't remember the name), and it created a histogram on the index (one bucket) which obviously held the min and max values for the data, and suddenly O815 started using the index again.

Regards
Robb Received on Tue Nov 23 1999 - 11:02:28 CST