kivi_at_hotmail.ru wrote in message news:<9eai19$6nn$1_at_news.netmar.com>...
> 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.
>
The rule based optimizer makes choices based on values determined by
rules. Unique keys get one value, bounded range scans another, and
unbounded still another value. If two indexes both result in a
unbounded range scan then they two plans get the same value. Now some
adjustments have been made to the rule based optimizer to allow it to
perform a couple of tricks such as using indexes with the min and max
functions and to perform full index scans, but again these actions
take place based on certain rules and conditions happening and do not
consider such things as the size or shape the index is in. Back to
the two indexes receiving the same value, the optimizer has to have a
tie breaker. In the case of range scans it seemed to be the last
index built at least in 7.2 where we ran several tests and were able
to get the index to switch back and forth by dropping and recreating
the index we wanted to use.
Received on Tue May 29 2001 - 14:14:50 CDT
