execution plan and poor statistics

From: Ceeper <nosp_at_m.e>
Date: Mon, 11 Apr 2011 22:53:19 +0200
Message-ID: <invpnv$nea$1_at_online.de>

A table is filled with a lot of data (about 100.000 entries). Data is processed and rows are deleted until table is empty. Problem: table statistics are gathered periodically, often when this table is nearly empty. The effect: the optimizer chooses full table scan, even if the table is filled.

The database admin periodically updates all table statistics. The update starts at a fixed time.

questions:
1. The above example preferes indexed access. How can oracle 11g be advised to always use the index? The used sql is fix, the existing application should not be changed.

2. Assuming the above example uses different times for operations. We do not know, when table is full or empty. How can oracle be advised to use the index.

I think the origin of this problem is the cost based optimizer. The rule based optimizer would always use the indexed access path, wouldn't it?

markus Received on Mon Apr 11 2011 - 15:53:19 CDT