Re: Re[6]: to_number question

From: <Jared.Still_at_radisys.com>
Date: Thu, 15 Jul 2004 11:25:51 -0700
Message-ID: <OF380FC46B.372FB6CB-ON88256ED2.00650648-88256ED2.00653BB1@radisys.com>

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

... which would seem to indicate that the operation in question should be performed with a minimum of two queries and a temporary table.

Quite an interesting thread, one that forces you to realize that the database
is not a 'black box': understanding is required.

Jared

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

Received on Thu Jul 15 2004 - 13:23:01 CDT