Re[2]: to_number question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Fri, 16 Jul 2004 15:54:09 -0400
Message-ID: <841093030074.20040716155409@gennick.com>

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_at_dfci.harvard.edu) 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 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 http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 14:54:38 CDT