CODEWITHSUNDEEP
sqlperformanceoracle-database
Rudy
Rudy

Reputation: 7044

Oracle Performance with Index

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?

Upvotes: 0

Views: 314

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 2

Related Questions