DB2 UDB 9.5 FP2a is not using the best index

[parabinda]

Hi,

We are using DB2 UDB 9.5 FP2a.
And the following SQL is not using the right index.

SELECT a1.col1, a0.col2
FROM TABLE1 a0 INNER JOIN TABLE2 a1 ON a0.KEY1
= a1.KEY1
WHERE ((a0.number_01 = '00001985014082 ') AND (a0.number_02 = '001'))

There are indexes on number_01 and number_02 columns.

We have done RUNSTATS on the tables.
The TABLE1 has - 38 million records
number_01 has - 35 million distinct values
number_02 has - 11 million distinct values

Still it is using index on "number_02"

Any help will be appreciated.

Thanks
Arabinda

[cburnett]

Could be that distribution stats are changing the optimizer's estimate.