CODEWITHSUNDEEP
sqlindexingdb2
Amit Kumar Gupta
Amit Kumar Gupta

Reputation: 7413

impact of index in SQL query

Suppose there are two indexes on a table

  1. index1 on col1,col2
  2. index2 on col3

Please tell me, whether in below case index will help?

.. where col1,col4
.. where col3,col4
.. where col1,col3 
.. where col1,col2,col3,col4

*note,

  1. i wrote where clause only without specifying the conditions. Only used column are mentioned
  2. I am using DB2. So as per my knowledge column sequence would not matter.
  3. col4 is not having any index.

Upvotes: 2

Views: 189

Answers (2)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

The columns mentioned in where clause, if indexed, then those indexes will be used. For eg:- in your "Where col1, col4", col1 is indexed and hence it will be used. Similarly for other conditions. For columns which both indexes are available, then both of them might be used. For eg:- in your 3rd and 4th where clauses.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838216

  • where col1,col4 - can use prefix col1 from index 1.
  • where col3,col4 - can use index 2.
  • where col1,col3 - can use either index2 or prefix col1 from index 1.
  • where col1,col2,col3,col4 - can use either index

Upvotes: 2

Related Questions