Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: to_number question

Re: to_number question

From: Mladen Gogala <>
Date: Fri, 16 Jul 2004 11:23:25 -0400
Message-ID: <>

On 07/16/2004 10:39:22 AM, Stephen.Lee_at_DTAG.Com wrote:
> I think at the heart of the argument that the error should not occur (at
> least MY opinion) is that we CAN impose an order on processing so that (A or
> B) and C is not the same as A or (B and C), and the RDBMS is not allowed to
> throw away the parenthesis and do the processing in any way it chooses.
> So at least some folk around here don't like the fact that it is OK for the
> RDBMS to "throw away the parenthesis" in the case of a subquery when an
> optimizer that is too smart for its britches can form a logically equivalent
> query ... assuming no errors occur.

Stephen, relational databases are created as an implementation of Cantor's naive set theory (the word "naive" means that it doesn't concern itself with the axiom of choice, well ordering theorem, Zorn's lemma and alike). In the naive set set theory, union and intersect operations are commutative and therefore, they are commutative in the implementation as well. Unfortunately, RDBMS is no longer just a convenient tool to get a data subset without writing those pesky loops in a 3GL language,they contain tools to implement and enforce business rules, to automate processing and even to accommodate new and essentially non-relational data, like XML. I'm afraid that very soon, we will have to have new paradigm and that Codd-Date relational model will have to be extended. So far, the only theory that looks promising as heir to the set theory is the chaos theory, as it was described in the Jurassic Park. Until then, we will have to live with the limitations of the present relational model. The problem with the fine-grained control mechanisms that would allow us to select our own access path is that they add complexity to the database, and the name of the game is exactly the opposite. Microsoft's marketing pitch is:"we don't have half the features and portability that Oracle has, but our databases are so simple to create and manage that you don't need an expensive DBA to manage them". In essence, they are comparing a rubber dingy to a super-tanker and saying that it is much simpler to use the dingy, because one doesn't need an expensive captain. Oracle had to cave in and gradually simplify the steering wheel. Auto-pilot means that you no longer have total control over the optimizer. On the plus side, optimizer usually does an excellent job, so the total control is necessary if and only if you are trying to optimize a bad SQL trying to access an inefficient data model, a situation that is, unfortunately, not so rare. For that, there are numerous hacks, many were discussed on this very group. I don't necessarily share your enthusiasm for the total control and I don';t feel that the total control with parenthesis would be necessary to tune our queries properly.

Mladen Gogala
Oracle DBA
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jul 16 2004 - 10:21:06 CDT

Original text of this message