Re: Upgrade horror stories/victories

This sounds like you did an import which did NOT do an ANALYZE of the table.

When a table is analyzed, each column
has a min and max value visible in user_tab_columns, and also a pair of rows in user_tab_histograms (faked in, even if you haven't generated a histogram).

Also, you can't generate a one-bucket histogram, so I suspect the command you ran was simply:

    analyze table XXX estimate/compute statistics.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

r.golds wrote in message
<81eh6t$j8$1_at_ssauraab-i-1.production.compuserve.com>...
>
>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.
>
>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.
>
Received on Tue Nov 23 1999 - 13:14:47 CST