CODEWITHSUNDEEP
sqloracle-databaseoracle11gquery-performance
Ricketyship
Ricketyship

Reputation: 654

Indexed query along with non index conditions

The following is a query that I'm executing.

select col1, col2 from table1 where col1 in (select table2.col1 from table2) and col2 = 'ABC' ;

The query takes a lot of time to come out. If I remove the "in (select table2.col1 from table2)" condition, the query behaves the way an indexed query needs to.

My question is, In case we have an indexed column being used in the where clause and we include a condition for a non indexed column (specifically an in condition), is there a possibility of a performance hit? Explain plan on the query does not give any hint of non index fetch.

Also, does the order of the conditions matter? i.e In case I give the index clause before the non index clause, will oracle apply the non index clause only on the subset chosen?

Thanks in advance.

Upvotes: 0

Views: 390

Answers (1)

Ronnis
Ronnis

Reputation: 12833

The order of your predicates does not matter. The optimizer determines that. It's not as simple as "index is always better", so the optimizer tries to evaluate the "selectivity" of each predicate, and then determine the "best" order.

If one predicate is determined to result in a very small portion of the table, and an index exist, it is likely that indexed access will be used. In your case, I don't think an index will help you, unless the rows are physically sorted (on disk) by col2.

If you can share the execution plans, we could probably help you.

Upvotes: 1

Related Questions