Re: Partial Index Usage Performance Question

On Oct 7, 3:14 am, digory <dig..._at_gmx.net> wrote:
> I can't give you the whole definition because of business reasons. But
> there are a lot of other columns in T, including a CLOB. Moreover, c
> is a DATE column, and my condition for c is actually:
>
> SELECT * FROM T WHERE a = ? AND b = ? AND c > t0
>
> for some constant date t0, which is known to be older than any value
> of c in T.

>> I can't give you the whole definition because of business reasons <<

Nonsense. You can easily construct a table to support your specific question.

If the where clause of a query supplies columns that are indexed then it is up to the CBO to determine if the index is to be used. You can use the explain plan feature of Oracle to see the likely plans that will be used by the optimizer.

Generally speaking if leading columns of an index are supplied by the query where clause then the CBO can use the index to solve the query but if the index is used or not depends on the decisions made by the optimizer based on the object statistics.

Explain plan is described in the Performance and Tuning manual.

HTH -- Mark D Powell -- Received on Tue Oct 07 2008 - 08:16:00 CDT