0

Let say if I have table TABLE1 that consist of million of records. The table has COLUMN A, B and C. I have an index of A with B. C is not indexed at all.

After that I do a query with as per below

  1. I run query Select * from TABLE1 where A='something' and B='something'
  2. I run query Select * from TABLE1 where A='something' and B='something' and C='something'

I understand that both query will use the index that I have specified. Based on my understanding, the performance of both query should be the same. However, is there any possibility that a query has better performance / run faster than the other? Why?

Improve this question
1
  • 6
    This sounds like something you can test yourself. Commented Feb 2, 2014 at 16:50

1 Answer 1

Reset to default
2

The queries will not necessarily use the index. Oracle makes a decision to use an index for queries based on the "selectivity" of the index. So, if 90% of the rows have a = 'something' and b = 'something' being true, then a full table scan is faster than using the index.

In both cases, the selectivity of the index would be the same (assuming the comparison values are the same). So both should be using the same execution plan.

Even so, the second query would typically run a bit faster, because it would typically have a smaller result set. The size of the result set is another factor in query performance.

By the way, both could take advantage of an index on table1(A, B, C).

Also, on a "cold" database (one just started with no queries run), the second should run faster for the simple reason that some or all of the data will have already been loaded into page and index caches.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

you mean table1(A,B) since C is not indexed at all?
@Rudy . . . I said that if the index contained C as the third column, both queries could take advantage of the index.
If A, B, and C are the only columns in the table, you should consider converting the table to an IOT. Also, if you always reference A and B in your queries, you should look at which of those two columns is less selective, and make that the leading column, so that you can more effectively take advantage of compression.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.