Wrong index used

Hello Oracle Gurus!

We faced a problem recently
after we rebuild the table and it's indexes (did 'insert select' with indexes created in the new table and then renamed it ). The reason for rebuild that we started to get 'sort key to long' while trying to rebuild indexes.

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

when we select 'A from tabl 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 e.i. D 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 opctimizer 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>

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net