RE: Re[2]: to_number question

Wolfgang,
Thanks for this explanation! (Reminded me of the way Vis Basic works...) Rob Zijlstra

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: donderdag 15 juli 2004 15:09
To: oracle-l_at_freelists.org
Subject: Re: Re[2]: to_number question

Quoting Jonathan Gennick <jonathan_at_gennick.com>:
>
> 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_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
-----------------------------------------------------------------



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