Re: 7.3 Optimizer diffs from 7.2

Crazylongs wrote:
>
> We just upgraded to 7.3 from 7.2. Complex queries run the same or even faster
> than with 7.2. However, primary keys/indexes seem to be ignored, and do ing
> FT scans. We either have to force the rule based optimizer in init.ora (which
> makes complex queries run slow) or force it by deleting the statistics before
> loading (which does the primary key lookups) and then re-estimating after.
> What did Oracle do to the optimizer to have this effect, has anyone else seen
> it, and does anyone have any other suggestions?
>
> Note: The first thing we did was compute (estimate) statistics for every
> object (using 30% of rows), thinking they should be re-estimated for the new
> optimizer, but this had little or no effect.

One of the new thingies in 7.3 is HASH JOINS which basically introduce another "scanning" candidate as opposed to index access.

Any explain plan output will list if HASH JOINS are being used - they are good in some cases - not so great in others... (It can be disabled at SQL or instance level)

Have fun. Received on Tue Nov 18 1997 - 00:00:00 CST