I 've been on the fence about whether to post the note below.
I think I 'll go ahead and post it, because it sums up as
best I can why I 'm so flummoxed over this issue. It 'll give
you an idea of where my head is at, though you might feel
that I have a rather strange way of looking at things.
To get any resolution, I 'm just going to have to go off in a
corner and reflect for awhile.
Friday, July 16, 2004, 11:05:24 AM, Poras, Henry R. (Henry_Poras@(protected)) wrote:
PHR > If we can
PHR > decide that the optimizer needs to be aware of the parenthesis, we are now
PHR > saying that we want our syntax to influence the execution plan and hence
PHR > performance. Ugh!!
Neither Stephen, nor I are trying to impose execution plans
on the optimizer.
With SQL you have the conceptual execution of a query versus
the actual execution performed by the database. Having the
proper conceptual model in mind is critical, because that 's
what you use when you actually write a SQL statement.
My mental model for subqueries in the FROM clause has
always been:
The outer SELECT operates against the rows
and columns returned by the subquery
Subquery merging breaks this model. That in itself wouldn 't
normally bother me, because it 's an optimization performed
by the database to get at my data more quickly. It bothers
me in this case though, because the optimization causes
behavior that does not conform to the mental model I just
gave above. (cognitive dissonance?)
What can I do? I can do at least two things:
* Adjust my mental model of how SQL, and especially
subqueries, works
* Decide whether I agree with the behavior, and build an
argument for whatever side of that question I ultimately
decide to fall on
