From oracle-l-bounce@freelists.org Fri Jul 16 14:54:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6GJsDd11116 for ; Fri, 16 Jul 2004 14:54:23 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6GJs1611075 for ; Fri, 16 Jul 2004 14:54:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E549D72C0D0; Fri, 16 Jul 2004 14:30:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13923-50; Fri, 16 Jul 2004 14:30:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 37EFE72C27A; Fri, 16 Jul 2004 14:30:30 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 16 Jul 2004 14:29:02 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 482E072D737 for ; Fri, 16 Jul 2004 14:29:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13923-26 for ; Fri, 16 Jul 2004 14:29:02 -0500 (EST) Received: from relay.pair.com (relay.pair.com [209.68.1.20]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 7AF6972C4CD for ; Fri, 16 Jul 2004 14:29:00 -0500 (EST) Received: (qmail 98166 invoked from network); 16 Jul 2004 19:54:13 -0000 Received: from dynamic-173-129.oreilly.com (HELO 172.30.254.72) (209.58.173.129) by relay.pair.com with SMTP; 16 Jul 2004 19:54:13 -0000 X-pair-Authenticated: 209.197.173.129 Date: Fri, 16 Jul 2004 15:54:09 -0400 From: Jonathan Gennick X-Mailer: The Bat! (v1.62 Christmas Edition) Personal X-Priority: 3 (Normal) Message-ID: <841093030074.20040716155409@gennick.com> To: "Poras, Henry R." Subject: Re[2]: to_number question In-Reply-To: <0D3AAB332E29BA4F9D8BE559F244A914558BE7@phsexch27.mgh.harvard.edu> References: <0D3AAB332E29BA4F9D8BE559F244A914558BE7@phsexch27.mgh.harvard.edu> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5336 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@gennick.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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@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@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 -----------------------------------------------------------------