Re: Wrong index used

In article <9e2m85$rpp$1_at_news.netmar.com>, <kivi_at_hotmail.ru> writes:
>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>
>

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 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.

And obviously there are no hints in the SQL which would have the effect on a 7.3+ database of converting to the query to running cost based so that analyzing the new table would not correct the problem.

• Mark D Powell --
  • 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