Re[2]: to_number question

  • From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
  • To: "Poras, Henry R." <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 16 Jul 2004 15:54:09 -0400

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@xxxxxxxxxxxxxxxx) 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

From a pragmatic standpoint, I simply must find a way to
adjust my model, because I'm clearly not going to convince
Oracle to change the way their database operates.

Perhaps the correct mental model is:

    The outer SELECT operates against the rows
    and columns returned by the subquery, except
    when the optimizer surprises you by doing

This is certainly not very satisfactory. At least, I'm not
at all happy with it. Better might be:

    The outer SELECT and subquery together describe the state of
    the data to be returned by the statement, but to get that
    state the database will perform various operations in some
    indeterminate order.

This I like better, but still leaves me vaguely unsatisfied.
For one thing, the "state of the data" cannot be properly
understood without also visualizing the results from the
subquery, so I'm left with a bit of a chicken/egg problem.
It'll probably take me awhile to come up with a mental model
that I like.

In the end though, I'm not trying to coerce a given
execution plan, but rather I'm trying to reconcile my mental
model for subquery execution with this behavior that we're

Best regards,

Jonathan Gennick --- Brighten the corner where you are * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit, 
or send email to Oracle-article-request@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: