Subject Re: Query plan insists on NOT USING an index
Author Franz J Fortuny
--- In, Alexandre Benson Smith
<iblist@...> wrote:
> Well, maybe I am wrong, but from what I read (or better, from what I
> understood) he always is comparing the "IN <constant>" vs "IN
> <subquery>" as he tells about plans and first/second query.


This comparison is only the last part of the whole problem.

In previous messages we have concluded that:

1) JOINS must be placed before LEFT OUTER JOINS

2) If a VIEW contains LEFT OUTER JOINS (my case), then anything YOU
JOIN to such view will NOT USE the correct plan.

The discussions about IN (list) and IN (query) was the consequence of
trying to find other ways for the proper indexes to be used. As a
matter of fact, if the query does NOT contain LEFT OUTER JOINS, the IN
(query) DOES USE the correct indexes!

A very good lesson today. Thanks to all who participated.

Franz J Fortuny