A Cure for Madness 2004-08-29 - By Jonathan Gennick
WB > And I am sure everyone would be very upset
WB > if they implemented the rule that a subquery must be evaluated before
WB > predicates and selection are applied.
Don 't forget that Chris is really arguing that the results
must be "as if " the view had been materialized. Optimization
is still ok, and I can think of at least two approaches by
which the subquery can be merged into the main query while
retaining that "as if " behavior.
WB > I have said it before and I still content that the original problem is not
WB > caused by the optimizer incorrectly reordering the sequence of operations
WB > but by the fact that the to_number function is not well defined over the
WB > domain (varchar2). If that was not the case we would not have this debate
WB > as the end result would be the same no matter the order of operations.
This is actually an interesting line of thought. I like very
much your my_to_number example. It dovetails with some
thinking I 've been doing lately about null values. Excellent
example...
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@(protected)
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request@(protected) and
include the word "subscribe " in either the subject or body.
Sunday, August 29, 2004, 12:43:59 PM, Wolfgang Breitling (breitliw@(protected)) wrote:
WB > It is probably heresy to contradict the great Chris Date, but I disagree
WB > with this part of his follow-up:
WB > "
WB > Now, the SQL standard makes it perfectly clear that the result of the query
WB > SELECT *
WB > FROM R1
WHERE NUM > > 0 ;
WB > is defined as follows:
WB > 1. Evaluate R1.
WB > 2. Restrict the result of the previous step to just those rows
satisfying NUM > > 0.
WB > 3. Project the result of the previous step over all of its columns
WB > (which is effectively a no op, of course).
WB > In other words, the inner subquery must be evaluated before the outer WHERE
WB > and SELECT clauses are executed (hence my unequivocal no to the question
WB > "Is this rewriting on the part of the optimizer valid? "). But there 's
WB > still a little more to be said. "
WB > Maybe that is the SQL standard, I don 't know, I don 't have the text of it,
WB > but it is certainly not part of the relational theory whose strength is the
WB > fact that the operations selection (where clause), projection (select
WB > clause) and join can be freely interchanged as long as the rules laid out
WB > by the theory are followed. And none of the relational databases implement
WB > Chris ' 1-2-3 order of operation. And I am sure everyone would be very upset
WB > if they implemented the rule that a subquery must be evaluated before
WB > predicates and selection are applied. Think of the emp table (EMPNO, ENAME,
WB > JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) and create a view that excludes the
WB > sensitive data: emp_vw (select EMPNO, ENAME, JOB, MGR, DEPTNO from emp).
WB > Now, according to Chris ' stand, a query like
WB > select * from emp_vw where empno = 7369 would not be allowed to apply the
WB > predicate to the underlying emp table but would need to evaluate the view
WB > first ( which consists solely of a select , i.e. a temporary rowsource
WB > consisting of the selected columns from the entire emp table has to be
WB > built ) before applying the restriction "where empno = 7369 ". Imagine the
WB > performance.
WB > I have said it before and I still content that the original problem is not
WB > caused by the optimizer incorrectly reordering the sequence of operations
WB > but by the fact that the to_number function is not well defined over the
WB > domain (varchar2). If that was not the case we would not have this debate
WB > as the end result would be the same no matter the order of operations. To
WB > prove it I created a function my_to_number which returns a NULL (i.e.
WB > undefined) if presented with something that does not represent a number:
WB > create or replace function my_to_number(input in varchar2)
WB > return number is
WB > l_num number := NULL;
WB > begin
WB > begin
WB > l_num := to_number(input);
WB > exception
WB > when others then null;
WB > end;
WB > return l_num;
WB > end;
SQL > > create table subtest (flag varchar2(1), num varchar2(5));
WB > Table created.
SQL > > insert into subtest values( 'N ', '123 ');
WB > 1 row created.
SQL > > insert into subtest values( 'X ', '123 ');
WB > 1 row created.
SQL > > insert into subtest values( 'Y ', 'pqr ');
WB > 1 row created.
SQL > > insert into subtest values( 'N ', '456 ');
WB > 1 row created.
SQL > > insert into subtest values( 'Z ', 'ijk ');
WB > 1 row created.
SQL > > commit;
WB > Commit complete.
SQL > > analyze table subtest compute statistics;
WB > Table analyzed.
WB > Executing the query as is produces the error:
SQL > > SELECT SELECT FLAG, NUM
WB > 2 FROM ( SELECT FLAG, TO_NUMBER ( NUM ) NUM
WB > 3 FROM SUBTEST
WB > 4 WHERE FLAG = 'N ' )
WB > 5 WHERE NUM > 0 ;
WB > ERROR:
WB > ORA-01722 (See ORA-01722.ora-code.com): invalid number
WB > Forcing the subquery to be evaluated first gives us the result
SQL > > SELECT SELECT FLAG, NUM
WB > 2 FROM ( SELECT rownum, FLAG, TO_NUMBER ( NUM ) NUM
WB > 3 FROM SUBTEST
WB > 4 WHERE FLAG = 'N ' )
WB > 5 WHERE NUM > 0 ;
WB > F NUM
WB > - -- ---- --
WB > N 123
WB > N 456
WB > 2 rows selected.
WB > Using the extended TO_NUMBER function also gives the same result, proving
WB > that the reordering does not affect the final resultset.
SQL > > SELECT SELECT FLAG, NUM
WB > 2 FROM ( SELECT FLAG, my_TO_NUMBER ( NUM ) NUM
WB > 3 FROM SUBTEST
WB > 4 WHERE FLAG = 'N ' )
WB > 5 WHERE NUM > 0 ;
WB > F NUM
WB > - -- ---- --
WB > N 123
WB > N 456
WB > 2 rows selected.
WB > At 03:19 PM 8/27/2004, you wrote:
> >Some of you might have read the article I recently posted on
> > "Subquery Madness! ":
> >
> >http://five.pairlist.net/pipermail/oracle-article/2004/000012.html
> >
> >Chris Date took an interest in the issue, and he wrote a
> >fascinating follow-up, "A Cure for Madness, " which just went
> >live today:
> >
> >http://www.dbdebunk.com/page/page/1351381.htm
> >
> >Please note that Chris is *not* saying that the subquery
> >results need to be materialized, but only that the final
> >results need to be "as if " they had been. I can think of at
> >least two ways to merge the subquery and yet maintain the
> >required "as if " behavior.
> >
> >Chris 's article is really clear and well-written. I wish I
> >could write as well as he does. I think you 'll find his
> >article interesting no matter which side of the issue you
> >personally fall on.
WB > Regards
WB > Wolfgang Breitling
WB > Centrex Consulting Corporation
WB > www.centrexcc.com
WB > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
WB > Please see the official ORACLE-L FAQ: http://www.orafaq.com
WB > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
WB > To unsubscribe send email to: oracle-l-request@(protected)
WB > put 'unsubscribe ' in the subject line.
WB > --
WB > Archives are at http://www.freelists.org/archives/oracle-l/
WB > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
WB > -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
