Re: CBO rows estimate way out in 2 table join

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Mon, 29 Oct 2007 12:27:33 -0700
Message-ID: <1193686053.119276.183320@o38g2000hse.googlegroups.com>

> Hi Stephen,
>
> Have you tried *not* using histograms? If you know you always want a
> certain access path because you know your data (business data as
> Jonathan refers to it in the book you reference), histograms (as
> calculated by oracle) could be "messing" you up.
>
> Can you post the execution plan?
>
> HTH,
>
> Steve

Yea, I started off with no column stats at all on the instance. Then I added "method_opt all columns size 1" to get column stats on. That still gave me no luck. I know the data is pretty skewed, so I tried histogram which got Oracle estimating about 12K rows instead of 7K, but its still some way from the 70M rows it should be guessing!

I will post the explain plan tomorrow (away from office now).

Should of mentioned its Oracle 9.2.0.7.

Thanks,

Stephen. Received on Mon Oct 29 2007 - 14:27:33 CDT

Re: CBO rows estimate way out in 2 table join - what to do?