Hi there Svein,

Thanks for your response.

(FYI the example I gave was a cut-down version of the real thing).

> but unless you have more complex cases where the 'in list' gets much
> bigger (there is a limit as to how many items there can be, at least

This was indeed part of the problem (1500 items is the max, I
believe). That and using the same index repeatedly for every element
in the IN clause!

Thanks for the suggestion for reorganising the output SQL, I'll be
taking some of it on board. Part of the reason for the layout is the
generation takes place centrally (and for different situations). i.e
the joins are generated in one place and the where clause for more
definite types of other criteria generated elsewhere.

> How many different values are there in CRITERIACOLUMN?
> If there are only a handful,
> NATURAL could be a better option than using an index.

Is this really true? Surely the selectivity of the index would
determine the wisdom of ignoring an index and performing a table scan
or not?

> HTH, Set

Yes, it did!