Re: Wrong index used

From: Lynux <lynux_at_netvigator.com>
Date: 25 May 2001 18:04:33 GMT
Message-ID: <9em6rh$4dg5@imsp212.netvigator.com>

I strongly agree with what Mark D Powell said although I don't know what does 'sort key errors' mean. Actually, we also have the similar problem in our database.

However, we have try to rebuild the index again and again but the problem still occurred. I might think that it would be due to the size of the indexes and the sqlload of data into the table with direct path.

Our database uses 16K block size and some of our indexes glow over 10Gb in size. Most of them are partitioned but some cannot because they are the unique key doesn't match with the partition key column.

We also looking for any means to solve this problem. Also, could Mark teaches us more about 'sort key errors' and where can we detect it or find more information on this?

xmark.powell_at_eds.com.x wrote:

: 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

Received on Fri May 25 2001 - 13:04:33 CDT