From oracle-l-bounce@freelists.org Thu Jul 15 08:05:40 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6FD5PV15821 for ; Thu, 15 Jul 2004 08:05:35 -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 i6FD5E615744 for ; Thu, 15 Jul 2004 08:05:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9BF7E72C507; Thu, 15 Jul 2004 07:45:27 -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 25752-89; Thu, 15 Jul 2004 07:45:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DFA8872C839; Thu, 15 Jul 2004 07:45:26 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Jul 2004 07:44:01 -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 86C3972C7C7 for ; Thu, 15 Jul 2004 07:44:00 -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 26383-30 for ; Thu, 15 Jul 2004 07:44:00 -0500 (EST) Received: from priv-edtnes43.telusplanet.net (outbound05.telus.net [199.185.220.224]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C8D4D72C31E for ; Thu, 15 Jul 2004 07:43:59 -0500 (EST) Received: from localhost ([199.185.220.240]) by priv-edtnes43.telusplanet.net (InterMail vM.6.00.05.02 201-2115-109-103-20031105) with ESMTP id <20040715130902.BTGD27265.priv-edtnes43.telusplanet.net@localhost> for ; Thu, 15 Jul 2004 07:09:02 -0600 Received: from 57.80.136.6 ( [57.80.136.6]) as user centrex@hosting.telus.net@192.168.200.1 by webmail.hosting.telus.net with HTTP; Thu, 15 Jul 2004 06:09:02 -0700 Message-ID: <1089896942.40f681ee5eb1f@webmail.hosting.telus.net> Date: Thu, 15 Jul 2004 06:09:02 -0700 From: Wolfgang Breitling To: oracle-l@freelists.org Subject: Re: Re[2]: to_number question References: <84981230715.20040715085050@gennick.com> In-Reply-To: <84981230715.20040715085050@gennick.com> MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 57.80.136.6 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5205 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Quoting Jonathan Gennick : > > Stephen's original problem and Tanel's solution are > fascinating. I can see where the optimizer might try and > combine Stephen's main query and subquery into just one query. > However, it seems to me that an optimization should *never* > return different results from the original operation that is > being optimized. > > This begs the question of how the optimizer should decide > whether it's safe to merge a subquery and main query. > Clearly, the optimizer seems to have made the wrong call > in the case of the query we've been talking about. > As I tried to explain in my post, the optimizer deals with basic relational operations - projection, filter, and join. If you visualize a table as a 2- dimensional array, then projection limits the total set veryically, to certain columns, and a filter limits the set horizontally, to a certain set of row, and a join cobines two sets. Relational theory guarantees that these operations are commutative, i.e. the order does not matter. The relational engine is explicitly allowed to reorder them as needed. That's part of the power of relational databases. If you now bring a function into play which is not applicable to all columns of the original set, you bring in a violation of this commutativity property and therefore the successful completion of the query depends on the order of the operations and thus on luck - unless you somehow make sure that the relational engine uses a certain order of processing. Using functions on columns can always pose a problem when the function is used in the predicates and is not uniformly and equally applicable to all columns in the original set. You are lucky if you get an error as in this case. In other cases Oracle may do some implicit conversions in order to apply the function and the result may not be what you were expecting, but because you did not get an error you may not notice and inadvertantly corrupt your database. -- Regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- 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 -----------------------------------------------------------------