Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: to_number question

Re[2]: to_number question

From: Jonathan Gennick <>
Date: Fri, 16 Jul 2004 15:54:09 -0400
Message-ID: <>

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. ( 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:

>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     otherwise.

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 seeing.

Best regards,

Jonathan Gennick --- Brighten the corner where you are * 906.387.1698 *

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

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jul 16 2004 - 14:54:38 CDT

Original text of this message