Franz J Fortuny wrote:

>
> However, it does handle the in predicate PERFECTLY in the first case
> (where the list is provided):
>
> in (xxx,xxx,xxx,xxx,xxx,xxx)

It's a completely different type of query with another execution
algorithm. IN (list) is transformed into a set of constant-driven OR'ed
equalities, while IN (subquery) is transformed into a correlated EXISTS.

> So, the problem might not be the IN PREDICATE, but the handling of the
> values generated by a subquery to be handled by the IN PREDICATE. This
> is where the BUG is located.

Again, this is not a bug. IN (subquery) is always executed as an
outer->inner semi-join, hence it can use indices on the inner table but
the outer one is scanned sequentially. This is just how the thing is
designed.

> Even if the TOR_EQUIV table is the first one and the others are joined
> to it: it will still NOT USE the SUBARTIS index.

What is the reported plan? Do you have an index for tor_equiv.idsub1?


Dmitry