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> · >>Some of you might have read the article I recently posted on

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.

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.

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

WB>    2  FROM ( SELECT FLAG, TO_NUMBER ( NUM ) NUM
WB>    3         FROM   SUBTEST
WB>    4         WHERE  FLAG = 'N' )
WB>    5  WHERE  NUM > 0 ;

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

----------------------------------------------------------------

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------