Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: how to stop oracle where clause evaluation upon match

    All,

    I curious about a certain behavior, and I was wondering if someone could answer my question, or point me to the right spot in the documentation.

    (As it relates to Oracle 9i)

    I'm generating a query dynamically, and as such the query can have N where clause comparisons (some of these comparison are function calls that return a string).

    So for instance, lets say i want to query a table called employees:

    SELECT * from employees WHERE function1()=val_criteria1 OR function2()=val_criteria2 OR function3()=val_criteria3


    What I'm concerned about is the execution time of these functions.

    Lets say function1 takes 50ms to execute,function2 takes 25 seconds to execute, and function3 takes 5 minutes to execute(i don't know, maybe its querying the biggest table EVER

    I'm curious as to how oracle will treat these. Will it cease execution of the query as soon as the first clause is evaluated true, or will it continue to execute the other two statements regardless?

    i.e. If the first comparison function1()=val_criteria1 evaluates to true, i'm hoping at least, the query should stop right there, and not evaluate the other 2 comparisons.

    Is this the case, or will oracle automatically eval the other 2 queries for ***** and giggles. If oracle does evaluate the entire clause, is there a command/keyword/etc to get it to stop after the first evaluataion returns true? (or in the case of an AND clause, get the query to return after the first non-true evaluation).

    Thanks for any help!

    --james

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It looks to me that it will "short circuit".
    Code:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    PL/SQL Release 9.2.0.6.0 - Production
    CORE    9.2.0.6.0       Production
    TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
    NLSRTL Version 9.2.0.6.0 - Production
    
    SQL>
    SQL> exec dbms_application_info.set_client_info( null );
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> create or replace function foo1 return number is
      2  begin
      3     dbms_application_info.set_client_info( 'foo1' );
      4     return 0;
      5  end;
      6  /
    
    Function created.
    
    SQL> create or replace function foo2 return number is
      2  begin
      3     dbms_application_info.set_client_info( 'foo2' );
      4     return 0;
      5  end;
      6  /
    
    Function created.
    
    SQL> create or replace function foo3 return number is
      2  begin
      3     dbms_application_info.set_client_info( 'foo3' );
      4     return 0;
      5  end;
      6  /
    
    Function created.
    
    SQL> select userenv( 'client_info' ) from dual;
    
    USERENV('CLIENT_INFO')
    ----------------------------------------------------------------
    
    
    SQL> select *
      2    from dual
      3   where ( foo1() = 0 or foo2() = 0 or foo3() = 0 )
      4  /
    
    D
    -
    X
    
    SQL> select userenv( 'client_info' ) from dual;
    
    USERENV('CLIENT_INFO')
    ----------------------------------------------------------------
    foo1
    
    SQL> select *
      2    from dual
      3   where ( foo1() = 1 or foo2() = 0 or foo3() = 0 )
      4  /
    
    D
    -
    X
    
    SQL> select userenv( 'client_info' ) from dual;
    
    USERENV('CLIENT_INFO')
    ----------------------------------------------------------------
    foo2
    
    SQL> select *
      2    from dual
      3   where ( foo1() = 1 or foo2() = 1 or foo3() = 0 )
      4  /
    
    D
    -
    X
    
    SQL> select userenv( 'client_info' ) from dual;
    
    USERENV('CLIENT_INFO')
    ----------------------------------------------------------------
    foo3
    
    SQL>

  3. #3
    Join Date
    Mar 2006
    Posts
    47

    wow

    Thanks, from your example it looks like thats exactly how it behaves.
    That's just what I was looking for!

    --james

  4. #4
    Join Date
    Mar 2010
    Posts
    1

    how to stop oracle where clause evaluation upon match

    Hi,
    I have a similar question as jholder and hopefully someone can answer it:

    Basically I have 2 tables I'm trying to link up based on the ID columns (taxpayer_id or fuzzy_id).

    If Table_2 contains only Taxpayer_id = 1, then the SQL would return "Moca Foods". If Table_2 contains only Taxpayer_id = 2, then the SQL would return "Moca Food". If Table_2 contains BOTH Taxpayer_id 1 & 2, I only want to return "Moca Foods", giving precedence to Taxpayer_id OVER Fuzzy_id. Do you know if it's possible to evaluate only the first part of the WHERE clause and if it doesn't exist or returns null, THEN evaulate the second part? Is this possible by just modifying the WHERE clause?

    Table_1
    Taxpayer_id Fuzzy_id
    1 2

    Table_2
    Taxpayer_id Name
    1 Moca Foods
    2 Moca Food

    SQL
    Select Table_2.Name
    From Table_1, Table_2
    Where Table_1.Taxpayer_id = Table_2.Taxpayer_id OR
    Table_1.Fuzzy_id = Table_2.Taxpayer_id

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by gjwl_8 View Post
    Hi,
    If Table_2 contains only Taxpayer_id = 1, then the SQL would return "Moca Foods". If Table_2 contains only Taxpayer_id = 2, then the SQL would return "Moca Food". If Table_2 contains BOTH Taxpayer_id 1 & 2, I only want to return "Moca Foods", giving precedence to Taxpayer_id OVER Fuzzy_id. Do you know if it's possible to evaluate only the first part of the WHERE clause and if it doesn't exist or returns null, THEN evaulate the second part? Is this possible by just modifying the WHERE clause?
    You are missing an important column - how are these related to each other in tab2?
    it would seem to me you need one more column on tab2 to bring everything together.
    especially thinking ahead towards 1000s or rows.

    you can get it to work easily enough but I worry about a few things depending on your data sets.

    PHP Code:
    create table tab1 (Taxpayer_id numberFuzzy_id number);
    create table tab2 (Taxpayer_id numbername varchar2(15));

    insert into tab1 values (1,2);
    insert into tab2 values (1'Mocha Foods');
    insert into tab2 values (2'Mocha Food');

    insert into tab1 values (3,4);
    insert into tab2 values (3'Mocha Foods');

    insert into tab1 values (5,6);
    insert into tab2 values (6'Mocha Food');

    duck@db1select
      2     max
    (
      
    3        case
      
    4        when t1.taxpayer_id is not null then t1.taxpayer_id
      5        when t1
    .fuzzy_id is not null then t1.fuzzy_id
      6        
    else 9999999 end tax_or_fuz_id,
      
    7     max (
      
    8        case
      
    9        when t1.taxpayer_id is not null then t2.name
     10        when t1
    .fuzzy_id is not null then t2.name
     11        
    else 'NO MATCH' end matching_name
     12  from
     13     tab1 t1
    tab2 t2
     14  where
     15     t1
    .taxpayer_id t2.taxpayer_id
     16  
    or t1.fuzzy_id t2.taxpayer_id
     17  group by t1
    .taxpayer_idt1.fuzzy_id
     18  order by 1
    ;

    TAX_OR_FUZ_ID MATCHING_NAME
    ------------- ---------------
                
    1 Mocha Foods
                3 Mocha Foods
                5 Mocha Food 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I think I'd write it more literally with a UNION, like

    Code:
    Select Table_2.Name
    From   Table_1, Table_2
    Where  Table_1.Taxpayer_id  = Table_2.Taxpayer_id 
    union 
    Select Table_2.Name
    From   Table_1, Table_2
    Where  Table_1.Fuzzy_id     = Table_2.Taxpayer_id AND
           Table_1.Taxpayer_id != Table_2.Taxpayer_id
    --=cf

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Where you run into trouble Chuck is with multiple sets of data.
    We need something to group by.

    Code:
    duck@db1> select * from tab1;
    
    TAXPAYER_ID   FUZZY_ID
    ----------- ----------
              1          2
              3          4
              5          6
    
    duck@db1> select * from tab2;
    
    TAXPAYER_ID NAME
    ----------- ------------------------
              1 Mocha Foods
              2 Mocha Food
              3 Mocha Foods
              6 Mocha Food
    
    duck@db1> Select Tab2.Name
      2  From   Tab1, Tab2
      3  Where  Tab1.Taxpayer_id  = Tab2.Taxpayer_id
      4  union
      5  Select Tab2.Name
      6  From   Tab1, Tab2
      7  Where  Tab1.Fuzzy_id     = Tab2.Taxpayer_id AND
      8         Tab1.Taxpayer_id != Tab2.Taxpayer_id;
    
    NAME
    ----------------------------------------
    Mocha Food
    Mocha Foods
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    I'm not sure I get what you're saying. I could've used UNION ALL to see all the matches

    Code:
    SELECT Tab2.Taxpayer_id,
      Tab2.Name
    FROM Tab1,
      Tab2
    WHERE Tab1.Taxpayer_id = Tab2.Taxpayer_id
    UNION ALL
    SELECT Tab2.Taxpayer_id,
      Tab2.Name
    FROM Tab1,
      Tab2
    WHERE Tab1.Fuzzy_id   = Tab2.Taxpayer_id
    AND Tab1.Taxpayer_id != Tab2.Taxpayer_id;
    
    TAXPAYER_ID            NAME                 
    ---------------------- -------------------- 
    1                      Mocha Foods          
    3                      Mocha Foods          
    2                      Mocha Foods          
    6                      Mocha Foods
    TAXPAYER_ID matches up for the 1 and the 3, FUZZY_ID matches up for the 2 and the 6. Why would you need to group on anything?

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    I suppose this is the other interpretation

    Code:
    SELECT Tab2.Taxpayer_id,
      Tab2.Name
    FROM Tab1,
      Tab2
    WHERE Tab1.Taxpayer_id = Tab2.Taxpayer_id
    UNION ALL
    SELECT Tab2.Taxpayer_id,
      Tab2.Name
    FROM Tab1,
      Tab2
    WHERE Tab1.Fuzzy_id   = Tab2.Taxpayer_id
    AND not exists (select 1 from Tab2 t where Tab1.Taxpayer_id = t.Taxpayer_id);
    
    TAXPAYER_ID            NAME                 
    ---------------------- -------------------- 
    1                      Mocha Foods          
    3                      Mocha Foods          
    6                      Mocha Foods
    Note that in your query, Duck, you're bringing back a record match for a TAXPAYER_ID of 5, when there's no matching record in Tab 2

    Code:
    select 
          max( 
             case 
             when t1.taxpayer_id is not null then t1.taxpayer_id 
             when t1.fuzzy_id is not null then t1.fuzzy_id 
             else 9999999 end ) tax_or_fuz_id, 
          max ( 
             case 
             when t1.taxpayer_id is not null then t2.name 
             when t1.fuzzy_id is not null then t2.name 
             else 'NO MATCH' end ) matching_name 
       from 
          tab1 t1, tab2 t2 
       where 
          t1.taxpayer_id = t2.taxpayer_id 
       or t1.fuzzy_id = t2.taxpayer_id 
       group by t1.taxpayer_id, t1.fuzzy_id 
       order by 1; 
    
    TAX_OR_FUZ_ID          MATCHING_NAME        
    ---------------------- -------------------- 
    1                      Mocha Foods          
    3                      Mocha Foods          
    5                      Mocha Foods

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    I see that now. Interesting.
    Also, the last entry (fuzz id = 6) should be "Mocha Food".

    I think I fixed it:
    Code:
    jmagnus@lmdev01> select
      2     max(
      3        case
      4        when t1.taxpayer_id = t2.taxpayer_id then t1.taxpayer_id
      5        when t1.fuzzy_id = t2.taxpayer_id then t1.fuzzy_id
      6        else 9999999 end ) tax_or_fuz_id,
      7     max (
      8        case
      9        when t1.taxpayer_id is not null then t2.name
     10        when t1.fuzzy_id is not null then t2.name
     11        else 'NO MATCH' end ) matching_name
     12  from
     13     tab1 t1, tab2 t2
     14  where
     15     t1.taxpayer_id = t2.taxpayer_id
     16  or t1.fuzzy_id = t2.taxpayer_id
     17  group by t1.taxpayer_id, t1.fuzzy_id
     18  order by 1;
    
    TAX_OR_FUZ_ID MATCHING_NAME
    ------------- ---------------
                2 Mocha Foods
                3 Mocha Foods
                6 Mocha Food
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •