Re: to_number question

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 15 Jul 2004 05:53:04 +0300
Message-ID: <01f901c46a16$daf65ce0$63a423d5@porgand>

I added few hints into your query. This should force optimizer to evaluate the subquery first and feed its result to your additional where clause. See if it helps to work around this feature :)

select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */ distinct nbr_cc_fop_name, X, Y

from (
select nbr_cc_fop_name, to_number(trim(nbr_cc_no_high)) X ,to_number(trim(nbr_cc_no_low)) Y

from nbr_cc
where nbr_cc_fop_name in ('AX','MC','VI','DS') ) Z
where X - Y > 0;

Tanel.

> I'm thinking that this is probably the case. The table DOES have entries
> that will break to_number; but the output of the subquery does not. It
> looks like the WHERE clause might be operating on the table rather the
> result of the subquery. That was one reason I used the subquery: I know
the
> output from it will be only numeric. I didn't (and don't) want to have to
> worry about which part of the WHERE clause gets evaluated first: The
"where
> nbr_cc_fop_name in ('AX','MC','VI','DS') part"; or the to_number biz. I
> figured if I did the subquery thing, then all the to_number stuff would
HAVE
> to work. This sure does look like a "feature" to me (at a list price of
> $40,000 per CPU). Either that, or there are some fine points of SQL and
> subqueries I haven't understood yet. By the way, it never made any
> difference if I did the to_number functions inside the subquery or in the
> WHERE clause.

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 Wed Jul 14 2004 - 21:49:49 CDT