RE: Index rebuilding

From: <arul.kumar_at_bt.com>
Date: Fri, 12 Nov 2004 10:53:13 -0000
Message-ID: <83FC2D1BC95D884894735B11B562A41C029B534E@i2km06-ukbr.domain1.systemhost.net>

May be, with the stats up to date CBO "thinks" using a full table scan = is going to benefit you.
Are you sure those full table scans are REALLY not good?

And, Try INDEX optimizer hint / to keep the execution plan same, you may = try SQL OUTLINEs

Hope this helps

Arul.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Steve Jelfs Sent: 12 November 2004 10:45
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=20 index. Then, after a couple of days the CBO reverts to full table=20 scans/. Re-analysing the table and index does not change anything but=20 re-building the index does.
The table holds transient data which is only ever about five days old=20 before it is "archived" off to another table. Wach row might get about=20 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:48 CST