MVSFORUMS.com

[Dip]

We are planning to build a index on a date field (late say ABC_DT)in a table. AT the same time our anticipation is that most of the records (80%) will be having NULL populated in that Date field (i think NULL in a index field is possible).

In this scenario how the below queries will perform in a batch job?

Select FIELD1, FIELD2 from table where ABC_DT = 'XXXX-XX-XX-XX.XX.XX.XXXXXX' ;

Select FIELD1, FIELD2 from table where ABC_DT IS NOT NULL;

Will it go for a INDEX scan?

OR

DB2 will internally perform a table scan as most of the records are having NULL populated in the INDEX field?

[videlord]

based on the statistic information, if requested data are more than 25% of total records, DB2 optimizer will use table scan instead of index scan

in your case, all requested data are less than 20% , index will be used if the statistic information in catalog table is correct

[kolusu]

[Dip]

Sorry.. didnt mention it.. It is not a primary index..

[pharia01]

Videlord,