Some query tuning tips for DB2
Some tips to influence the DB2 optimizer:
1. To avoid using an index, add an OR 0=1 predicate. E.g, Select * From T1 where (C1=:V OR 0=1). The same trick can indirectly be used to modify the join order also (if indexes determine the initial join order)
2. Use OPTIMIZE for 1 ROWS to avoid sorts. There is also a CLI variable called OPTIMIZEFORNROWS that will cause this clause to be appended to all queries.
3. OPTIMIZE FOR 1 ROWS also turns off both list and sequential prefetching as we are telling DB2 that we are only interested in the first row
1. To avoid using an index, add an OR 0=1 predicate. E.g, Select * From T1 where (C1=:V OR 0=1). The same trick can indirectly be used to modify the join order also (if indexes determine the initial join order)
2. Use OPTIMIZE for 1 ROWS to avoid sorts. There is also a CLI variable called OPTIMIZEFORNROWS that will cause this clause to be appended to all queries.
3. OPTIMIZE FOR 1 ROWS also turns off both list and sequential prefetching as we are telling DB2 that we are only interested in the first row
posted by Ram at
10:25 AM
|
0 comments
