Subscribe: Comments on: The order of conditions matters
http://awads.net/wp/2006/03/03/the-order-of-conditions-matters/feed/
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
cond  dbms output  dbms  function inside  function  inside function  inside  line  output put  output  pre  put line  put  sql 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Comments on: The order of conditions matters

Comments on: ★ The order of conditions matters



News, views, tips and tricks on Oracle and other fun stuff



Last Build Date: Tue, 19 Aug 2014 05:55:04 +0000

 



By: Steven Meyer

Wed, 30 Jul 2008 15:23:16 +0000

Quote iG "It is NOT ALWAYS guaranted..." then iG goes on to show an example in SQL. But remember that the order of evaluation in SQL is not as written.

SQL> CREATE OR REPLACE FUNCTION this_is_y(x NUMBER) RETURN VARCHAR2 IS
  2  BEGIN
  3    dbms_output.put_line('Inside function this_is_Y(' || x || ')');
  4    RETURN 'Y';
  5  END this_is_Y;
  6  /

Function created.

SQL> CREATE OR REPLACE FUNCTION this_is_n(x NUMBER) RETURN VARCHAR2 IS
  2  BEGIN
  3    dbms_output.put_line('Inside function this_is_N(' || x || ')');
  4    RETURN 'N';
  5  END this_is_n;
  6  /

Function created.
SQL> SELECT *
  2  FROM   (SELECT object_id
  3          FROM   user_objects
  4          WHERE  9 > rownum)
  5  WHERE  this_is_y(object_id) = 'Y'
  6  AND    this_is_n(object_id) = 'Y';

no rows selected

SQL>

And the output is:

Inside function this_is_N(174702)
Inside function this_is_N(592838)
Inside function this_is_N(592839)
Inside function this_is_N(592840)
Inside function this_is_N(592841)
Inside function this_is_N(592842)
Inside function this_is_N(592843)
Inside function this_is_N(592844)



By: iG

Sat, 11 Aug 2007 14:04:51 +0000

Its NOT ALWAYS guaranted the in

select from my_table where cond1 and cond2;

cond1 be first evaluated. For a query I was doing, a must use:

select /*+ no_cpu_costing */ from my_table where cond1 and cond2;

Look at

http://www.dba-oracle.com/oracle_tips_optimizer_cost_model.htm




By: Gaza

Thu, 26 Jul 2007 04:32:38 +0000

If (and only if) this behaviour is a standard in PLSQL, then one could argue that the more concise code that can be written using this IS more maintainable. More verbose code, especially if it requires the setting up and initialisation of more unnecessary variables, can reduce the maintainability of that code!




By: Eddie Awad

Fri, 03 Mar 2006 19:51:02 +0000

Good and valid points Rob. Thank you. Nevertheless, the short-circuit evaluation notion in PL/SQL is rather an interesting fact (as of 10gR2).




By: Robert Vollman

Fri, 03 Mar 2006 18:19:21 +0000

Interesting. But is this behaviour dependable? And, if so, will it remain like this in future versions? And, even if it is, is that necessarily the best coding practise? Especially since you can just do this:

IF (2=1) THEN IF (IS_GREATER_FUNC(1,2)) THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF;

That way, your intention (to only check the second case if the first case is true) is clear, and dependable. Sure, it's more lines of code, but easier to maintain.

Rob