Quoting Jonathan Gennick <jonathan_at_gennick.com>:
>
> Stephen's original problem and Tanel's solution are
> fascinating. I can see where the optimizer might try and
> combine Stephen's main query and subquery into just one query.
> However, it seems to me that an optimization should *never*
> return different results from the original operation that is
> being optimized.
>
> This begs the question of how the optimizer should decide
> whether it's safe to merge a subquery and main query.
> Clearly, the optimizer seems to have made the wrong call
> in the case of the query we've been talking about.
>
As I tried to explain in my post, the optimizer deals with basic relational
operations - projection, filter, and join. If you visualize a table as a 2-
dimensional array, then projection limits the total set veryically, to certain
columns, and a filter limits the set horizontally, to a certain set of row, and
a join cobines two sets. Relational theory guarantees that these operations are
commutative, i.e. the order does not matter. The relational engine is
explicitly allowed to reorder them as needed. That's part of the power of
relational databases. If you now bring a function into play which is not
applicable to all columns of the original set, you bring in a violation of this
commutativity property and therefore the successful completion of the query
depends on the order of the operations and thus on luck - unless you somehow
make sure that the relational engine uses a certain order of processing.
Using functions on columns can always pose a problem when the function is used
in the predicates and is not uniformly and equally applicable to all columns in
the original set. You are lucky if you get an error as in this case. In other
cases Oracle may do some implicit conversions in order to apply the function
and the result may not be what you were expecting, but because you did not get
an error you may not notice and inadvertantly corrupt your database.
--
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
-----------------------------------------------------------------
Received on Thu Jul 15 2004 - 08:05:40 CDT
