Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: A Cure for Madness

Re[2]: A Cure for Madness

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Sun, 29 Aug 2004 19:54:57 -0400
Message-ID: <130993944847.20040829195457@gennick.com>

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@gennick.com

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_at_gennick.com and include the word "subscribe" in either the subject or body.

Sunday, August 29, 2004, 12:43:59 PM, Wolfgang Breitling (breitliw_at_centrexcc.com) 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_at_freelists.org
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_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 Sun Aug 29 2004 - 19:27:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US