Re: Re[2]: to_number question

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 15 Jul 2004 06:09:02 -0700

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.


Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: