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@xxxxxxxxxxx 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@xxxxxxxxxxx and include the word "subscribe" in either the subject or body. Sunday, August 29, 2004, 12:43:59 PM, Wolfgang Breitling (breitliw@xxxxxxxxxxxxx) 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: 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@xxxxxxxxxxxxx WB> put 'unsubscribe' in the subject line. WB> -- WB> Archives are at //www.freelists.org/archives/oracle-l/ WB> FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------