CODEWITHSUNDEEP
sqlperformancedb2zero
user6804978
user6804978

Reputation: 37

DB2 query using zero value in IN CLAUSE is causing table scan, index on column is ignored

SELECT * FROM TABLE1 WHERE COL1 in( 597966104, 597966100);
SELECT * FROM TABLE1 WHERE COL1 in( 0, 597966100)

In the above 2 queries the first query uses index created on COL1 but the second query does not use index. The only difference in both queries is that zero (0) is used in the IN CLAUSE of the second query. Why is the zero causing the index to be ignored. This leading to table scan and slowing down the query performance. Is there any solution for this problem. Any help on this issue is welcome and appreciated. Database used is DB2

Upvotes: 1

Views: 368

Answers (1)

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

DB2 has a cost based optimizer. It tries to fugure out the best access plan and uses its statistics and configuration to determine it. In your case the number of rows with col1 = 0 could really matter. For example when col1=0 for 40% of your data it could be cheaper to do the table scan.

If you want to figure out more details explain the query and you will see how the data is accessed and how much rows the optimizer guesses for the result set.

Make sure you have the correct and up-to-date statistics by running runstats for the table(s) as this will be the most important source of information for the optimizer.

Upvotes: 1

Related Questions