Re: Index help

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 26 Jul 2004 09:58:47 -0600
Message-id: <41052A37.3090509@sun.com>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 26 2004 - 10:56:18 CDT