RE: Index rebuilding

From: Karsten Weikop <oraclel_at_weikop.com>
Date: Fri, 12 Nov 2004 11:49:46 +0100
Message-ID: <004801c4c8a5$5ebc1500$5401a8c0@KW>

Hi Steve

Please read the execellent paper from Richard Foote (which can be downloaded from Miracle's site):

http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf

Conclusion form this paper: Never Rebuild, but find the course to the problem.

In order to help you, we need some tracefile (perhaps also a 10056 event) in order to help you).

/karsten

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steve Jelfs Sent: Friday, November 12, 2004 11:45 AM To: oracle-l_at_freelists.org
Subject: Index rebuilding

We have an index that we need to keep rebuilding in order for the CBO to

use it. What happens is that we build the index and queries use the index. Then, after a couple of days the CBO reverts to full table scans/. Re-analysing the table and index does not change anything but re-building the index does.
The table holds transient data which is only ever about five days old before it is "archived" off to another table. Wach row might get about 2/3 updates during it's short life.

Why might this be? We're using 8.1.7.4 on W2K if that helps!

Cheers

Steve

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 12 2004 - 04:47:02 CST