Re: Freezing database statistics

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Tue, 31 Oct 2006 05:13:51 GMT
Message-Id: <pan.2006.10.31.05.13.50.969344@verizon.net>

On Mon, 30 Oct 2006 20:25:53 -0800, dbaplusplus wrote:

> I am not sure whether I understand you. How can using or not using
> statistics can give wrong result. Result may be slower but eventually
> all the orders in table whose order_date > sysdate - 1 will be shown.
> Please elaborate.

The trick is really simple: if you have histogram on the column order_date and do not update statistics, then sysdate-1 will become larger then the maximum value in the index, as far as CBO is aware, because CBO gets its maximum value from the histogram. CBO just might return an empty set if you have inaccurate statistics. This is a wonderful case that I haven't really thought of. Brilliant, Jonathan! I'll have to test it, it is not hard to construct a test case now that I know the trick. Of course, this fouls up any hope of plan stability without outlines.

-- 
http://www.mladen-gogala.com

Received on Mon Oct 30 2006 - 23:13:51 CST