Partial Index Usage Performance Question

External Since: Aug 07, 2008 Posts: 32

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

External Since: Oct 01, 2008 Posts: 75

On Oct 6, 10:14 am, digory <dig....RemoveThis@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