Re: Wrong index used

Hello Mark
First thanks for answering
>>
>>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
>>
>>I have seen behavior like this before when range scans were involved so try
>this. Since index A is on a single column and as long as it under a couple
>hundred bytes in length so that you do not get the sort key error, drop and
>recreate that one index. Then check your explain plans. What I have seen
>with both the rule and CBO when it has two indexes that can be used for
 range
>scans and Oracle apparently figures the costs/rule weight as being equal, is

How can this two indexes be equil if the first allows make an index range scan, and another requires full index scan

>that it chooses the last index created, and by dropping and recreateing the
>index the plan changes.
>
>I would think it is worth a try.
>
>What is your database block size anyway? If you are using 2K or 4K then
 since
>you are running into sort key errors then you may want to consider
 rebuilding
>the database with the next larger blocksize when you upgrade. The maximum
>sort key size is dependent on the Oracle block size so this might be
>necessary in your situation going down the road.
this is not possible.

• 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