Re: Partial Index Usage Performance Question

On Oct 6, 10:14 am, digory <dig..._at_gmx.net> wrote:
> Hi
>
> I have a table T with columns A, B, C and an index I over all three
> columns. Further, I have a query which uses columns A and B, but not
> C, i.e.
>
> SELECT * FROM T WHERE a = ? AND b = ?
>
> I gather Oracle is able to use index I even though the last column is
> not used. My question is the following: If I write a query like
>
> SELECT * FROM T WHERE a = ? AND b = ? AND c = ?
>
> with a condition for C that, for the data in T, is known to be true
> always, will that query be generally slower or faster than the one
> above, or is there no difference in performance, or does it depend on
> the complexity of the index?
>
> Thanks for a little insight,
> Peter

That depends upon the structure of T. If there are more columns in T than you have indexed then, most likely, the queries will run in approximately the same time. If, however, there are only the three columns in T then the second query could run faster as it will scan the index for the results and not touch the table at all.

You haven't provided enough information to answer your question. Provide the DDL for table T, and some sample data, and you'll get a better response, and possibly even an actual answer.

David Fitzjarrell Received on Mon Oct 06 2008 - 11:27:17 CDT