Re: Index help
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jul 2004 09:58:47 -0600
Predicate 1 (A0.idA3A5 = :1) - skewed distribution w/bind variable - CBO does
not handle this well.
Predicate 2 (A0.scheduleTime <= TO_DATE(:2,:3)) - nonequality predicate which
may or may not be a good candidate for indexing. How
many of the dates are <= to scheduleTime? If scheduletime is stored as a date,
the number of distinct values for scheduletime is
probably pretty high. This is hard to tell w/out knowing more about the data. I
also do not know how well the CBO can handle this
type of situation. Anyone know?
Predicate 3 ((A0.codeC5 = :4) OR (A0.codeC5 = :5)) - There are 3 distinct
values for this column (no idea on distribution). Let's
assume even distribution. 2/3 of the rows will be examined if you use an index.
Probably more efficient to use a FTS.
It is too bad that Oracle can't have multiple execution plans for a given
statement that varies upon certain bind variable values
(Use planA is ida3a5 = 46847, otherwise use planB). I don't recommend this as
it is a support nightmare, but if the application has
some way of sending different statements based on certain conditions it migh
improve performance...until the data changes, developer
changes, etc.
If you partitioned on ida3a5, would the CBO be able to perform partition
pruning properly? I don't know, but the idea just popped
into my head.
Regards,
Daniel
WHERE () AND (A0.scheduleTime <=
> ) AND ((A0.codeC5 = :4) OR (A0.codeC5 = :5))
Powell, Mark D wrote:
> If we go back to the original post with the Query then there are 3 columns
> referenced in the WHERE clause. It may still be possible to improve the
> query by building an index on those other columns. So far all the
> discussion has been on IDA3A5 which because it is badly skewed is not a good
> overall index choice.
>
> HTH -- Mark D Powell --
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
