RE: Re[2]: to_number question

  • From: "rob zijlstra" <rmsah@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 15 Jul 2004 15:37:12 +0200

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

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: donderdag 15 juli 2004 15:09
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Re[2]: to_number question

Quoting Jonathan Gennick <jonathan@xxxxxxxxxxx>:
> 
> 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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: