Optimisation issues.

Hi,

We have a TEST database which was a copy of a PROD database. In the TEST database there are 8 records in 39065 that are 'Active' records. In the PROD database there are 16 records in 39497 that are 'Active' records.

Both scripts to access this information are select * from table
where status = 'A';

PROD uses the Index and has a cost average of 3. TEST does a FULL table scan and therefore has a cost average of 29.

I have rebuilt the indexes in both systems, even dropped and created the index in both systems.
However, this hasn't fixed the problem.

If I do a
SQL > select status, count(status) from table

   2 > group by status;

Both systems use the same indexing.
In fact if I do
SQL > select status from table

   2 > where status = 'A';

Then the indexing is being used.

So why doesn't it use the indexing on the (select * from table where status = 'A';) in the TEST database.

Cheers
Craig.
Unix Systems/Oracle Database Administrator Australian Maritime Safety Authority Received on Wed Jul 16 2003 - 20:55:38 CDT