to_number question 2004-07-15 - By Wolfgang Breitling
I had a response crafted and just when I tried to send it my web mail client
that I 'm forced to use during the day lost connection - and my response in the
process. I 'll try again
Quoting Jonathan Gennick <jonathan@(protected) >:
>
> Then I would argue that it is not I who am bringing
> something into play that violates the rules, but that SQL
> itself is doing so. I fail to see how subqueries in the FROM
> clause can fail to violate the commutativity that you speak
> of.
>
The optimizer is not violating the rules of commutativity, it uses them. You
are violating them by using to expressions where the second expression relies
on the successful evaluation of the first.
If I boil down the query to it basic elements, then I think Stephen 's query is
asking for all rows in the table where cold x contains only numeric digits and,
when interpreted as a number satisfy a numeric expression e.g. is greater than
a constant. In an arbitrary pseudo code language:
is_greater(is_number(col),const) = true
but by the rules of commutativity, Oracle is allowed to execute
is_number(is_greater(col),const) = true
If you write the original query as
select x from t
where decode(translate(col, '0123456789a ', '0123456789),null, 'A ', 'N ') = N
and to_number(col) > const
you immediately recognize that you may be in trouble because you know that you
must not rely on the predicates being evaluated in the order they appear in the
where clause. So now put the first predicate into a inline view thinking that
this way you impose a order of processing. Well, you don 't. The optimizer is
allowed to transform your query "back " into its simpler form.
--
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
