Re: Wrong index used

try
select /*+ index(T index_A) A from T where A=something

Konstantin Kivi <kivi_at_hotmail.ru> wrote in message news:4f2ccffc.0105250145.3c1e867f_at_posting.google.com...
> 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 - 09:45:10 CDT