Re: Re[4]: to_number question

Quoting Jonathan Gennick <jonathan_at_gennick.com>:

> Thursday, July 15, 2004, 9:09:02 AM, Wolfgang Breitling
> (breitliw_at_centrexcc.com) wrote:
> WB> As I tried to explain in my post, the optimizer deals with basic
> relational
> WB> operations - projection, filter, and join. If you visualize a table as a
> 2-
> WB> dimensional array, then projection limits the total set veryically, to
> certain
> WB> columns, and a filter limits the set horizontally, to a certain set of
> row, and
> WB> a join cobines two sets. Relational theory guarantees that these
> operations are
> WB> commutative, i.e. the order does not matter. The relational engine is
> WB> explicitly allowed to reorder them as needed.
>
> I respectfully disagree in this case. We're talking about a
> query with a subquery. Here's Stephen's query again:
>
> select /*+ NO_MERGE(Z) NO_PUSH_PRED(Z) PUSH_SUBQ(Z) */
> distinct nbr_cc_fop_name into v_fop
> from (select nbr_cc_fop_name, nbr_cc_fop_high, nbr_cc_fop_low
> from nbr_cc
> where nbr_cc_fop_name in ('AX','MC','VI','DS')) Z
> where to_number(trim(substr(p_corigid,1,20))) between
> to_number(trim(nbr_cc_no_low))
> and to_number(trim(nbr_cc_no_high));
>
> The target "table" for the outer query is that set of rows
> returned from the inner query. Thus, the outer query *must*
> consider only those rows. It's a fault, in my current
> opinion, for the outer query to look at any row not returned
> by the inner query.
>

You may disagree, but those are the rules. The optimizer - and we are not talking about the cost based optimizer specifically here, ANY optimizer - breaks the ENTIRE sql down into this basic set of relational operators, no matter how you wrote it initially, and then is free to reorder them any which way because it relies on the commutativity. If you bring something into play that violates the commutativity, YOU are not playing by the rules, not the optimizer.

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