Wrong index used

Hello Oracle Gurus!

I already asked this question
but haven't get useful answers, so I hope this time I will get more answers

We have a big table with several indexes in it. The table was rebuild recently (we made insert select into another table with indexes already created and then renamed it.)

after we rebuild the table we found that many queries start to select wrong indexes to search.

Say we have fields A B C and D
and have indexes on A and and B,A and table name is T

when we do 'select A from T where A=something' it make a full index scan on index (B,A), rather then index range scan on index on (A)
if we add a field that is not in the index at all to the select list like this select A,D from T where A=something
than we get correct behaviour
  table access by index rowid

        index range scan on index on (A)

we created an almost empty table with the same structure and indexes and it works OK

We have RULE based optimizer on 7.3.3.6 on unix DC/OSx Nile Pyramid

How can we fix this and what is the reason?

Sincerely yours, Konstantin Kivi <kivi_at_hotmail.ru> Received on Fri May 25 2001 - 04:45:49 CDT