Subject Re: Optimiser not using expected index
Thanks Dmitry,

That explains its avoidance of this index. My issue is that a lot of queries are of similar form, so I was hoping to create some sort of index/indices to improve performance. I sometimes need equality to specific TABLE1ID records, but often it is a range (or an exists on another table). The number of records in TABLE2 for a given TABLE1ID will increase over time.

Is the best strategy then to create separate indices on TABLE2.VALIDFROM and TABLE2.VALIDTO and let the engine merge the indices? I had avoided this approach because of old (and possibly now out of date) advice against using indices on fields with lots of duplicates.

Thanks again