1 2 Previous Next 15 Replies Latest reply on Oct 22, 2019 4:48 PM by Jonathan Lewis

    re-writing this query that uses Or

    user13328581

      Hello experts;

       

      Just curious, I have a very complicated query similar to the following sample code below and I was just wondering if there is an another way to re-write this query. Please see sample code below

       

      with t as 
      (
      select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
       union all
      select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
       union all
      select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
       union all
      select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
      )
      , t_others as
      (
       select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue' as color, 'Zone 7' as zoneid from dual
       union all
      select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red' as color, 'Zone 7' as zoneid from dual
       union all
      select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual
       union all
      select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual
      )
      ,t_further_info as
      (
        select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual
         union all 
        select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual
         union all
        select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual
         union all
        select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual
      )
      select * from t 
      inner join t_others pt
      on t.id = pt.id
      inner join t_further_info fi
      on t.id = fi.id
      where (t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD') 
             or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
             or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
             )
      
      
      
        • 1. Re: re-writing this query that uses Or
          mathguy

          Is LASTUPDATED guaranteed to be non-NULL in all three tables?  If it is, you can write the condition like this:

          GREATEST(t.lastupdated, pt.lastupdated, fi.lastupdated) >= [whatever]

           

          If the last updated column is nullable, you can do the same after wrapping the dates within NVL, but then the code becomes more complicated than what you already have.

          1 person found this helpful
          • 2. Re: re-writing this query that uses Or
            L. Fernigrini

            You can use GREATEST to find the lowest higher of three values, and then compare only once... no big change:

             

            select *

            from t 

            inner join t_others pt

            on t.id = pt.id

            inner join t_further_info fi

            on t.id = fi.id

            WHERE GREATEST(t.lastupdated, pt.lastupdated, fi.lastupdated) >= to_date('2019-10-21', 'YYYY-MM-DD')

            1 person found this helpful
            • 3. Re: re-writing this query that uses Or
              Frank Kulash

              Hi,

               

              If none of the lastupdated columns can be NULL, you can do this:

              WHERE (GREATEST (t.lastupdated, pt.lastupdated, fi.lastupdated)  >= TO_DATE ('10.21.2019', 'MM/DD/YYYY')

              It won't be any faster, but it might be easier to debug and maintain.

              1 person found this helpful
              • 4. Re: re-writing this query that uses Or
                Martin Preiss

                just for the sake of completeness: whenever I see an OR in a WHERE clause, I start to think about splitting the query in separate partial queries connected by UNION or UNION ALL. This makes the query not at all more readable - but it may lead to a better performance, if there are fitting indexes in place and the data distribution makes an index access desirable. With the OR in place the optimizer usually would not choose an index access.

                 

                It may of course also lead to unpleasant effects, if these prerequisites are not given (and we multiply the number of full table scans by maybe three).

                • 5. Re: re-writing this query that uses Or
                  mathguy

                  In general, your rewritten query will not be equivalent to the original one. And it is only in the very simplest cases that you can even state explicitly under what special set of circumstances the queries ARE equivalent.

                   

                  Consider the data:

                   

                  ID  VAL1  VAL2

                  ---  ----  ----

                  101     3     5

                  101     3     5

                   

                  and the query:

                   

                  SELECT * FROM [...]  WHERE VAL1 = 3 OR VAL2 = 5.

                   

                  If you rewrite the query with UNION ALL, the result will have four rows. If you rewrite it with UNION, the result will have just one row. Neither of which is the output of the original query.

                  • 6. Re: re-writing this query that uses Or
                    Paulzip

                    Here's another way that is functionally equivalent, but with better brevity (less repetitions of date).

                    [snip..]

                    where to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)

                    • 7. Re: re-writing this query that uses Or
                      mathguy

                      Paulzip wrote:

                       

                      Here's another way that is functionally equivalent, but with better brevity (less repetitions of date).

                      [snip..]

                      where to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)

                       

                       

                      This is actually  better than the GREATEST(...) approach, for three reasons:

                       

                      1. NULL needs no special handling in the ANY(...)  condition.
                      2. I believe the ANY condition is re-written with OR, which means that as soon as one date is >= the comparison date, no further checks are performed. By contrast, GREATEST(...) must consider all the dates, just to compute the greatest value - so there will always be the maximum number of comparisons.
                      3. The ANY approach does not involve function calls; the GREATEST approach has one function call per row.
                      • 8. Re: re-writing this query that uses Or
                        L. Fernigrini

                        Good option... I always though that ANY and ALL were valid just for subqueries, I do not know why I had that idea on my mind (maybe because on the 9i course I did 15 years ago it was used with subqueries)!

                        • 9. Re: re-writing this query that uses Or
                          Martin Preiss

                          good point. So I would would rephrase: if you don't care about duplicates or have disjunct sets, you could try to replace OR by UNION/UNION ALL under certain circumstances.

                          • 10. Re: re-writing this query that uses Or
                            Jonathan Lewis

                            Apart from PaulZip's example of making the code a little more elegant with the use of ANY() what reason do you have for wanting to write the code some other way? Is there an option for optimization that you can see but the optimizer is failing to see ?

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: re-writing this query that uses Or
                              Jonathan Lewis

                              @Martin,

                               

                              Your strategy is viable if you remember to include the extra predicates in later branches of the UNION ALL that are needed to exclude rows that have been selected in earlier branches.  Oracle has been doing this type of transformation since about 7.3 and making use internally of the lnnvl() function as an easy option for expressing the de-duplicating predicate(s) - and they documented the function some time in 10g - see (for example):   https://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/

                               

                              Inevitably there are limitations and bugs - I'll have to check if there are any left in the latest versions.

                              There's also the OR_EXPAND() hint - and the older use_concat() hint - which can be used to force Oracle to do the transformation without going to the trouble of doing it yourself. Of course you have to know the precise syntax needed to make the hint work correctly - and there are bugs: https://jonathanlewis.wordpress.com/2019/05/22/danger-hints/

                               

                              Regards

                              Jonathan Lewis

                              1 person found this helpful
                              • 12. Re: re-writing this query that uses Or
                                Martin Preiss

                                Jonathan,

                                 

                                thank you for the additional points: now that you mention it, I remember the OR_EXPAND article but obviously I had to read it again.

                                 

                                Regards

                                 

                                Martin

                                • 13. Re: re-writing this query that uses Or
                                  user13328581

                                  Hi Jonathan;

                                   

                                  I need other options so that i can studied the explain plan and trace to see which of these options will have a better performance.

                                   

                                   

                                  Thanks everyone for your contribution. I really appreciate it.

                                  • 14. Re: re-writing this query that uses Or
                                    Jonathan Lewis

                                    user13328581 wrote:

                                     

                                    I need other options so that i can studied the explain plan and trace to see which of these options will have a better performance.

                                     

                                     

                                     

                                    Good reason.

                                    There are basically two strategies (with minor variations) that Oracle can adopt and that you could emulate manually for a query like this were there's a compound predicate that references two or more tables but isn't a join predicate. Taking your three "tables"

                                     

                                    Option 1 - join all three tables ignoring the compound predicate, then apply the predicate to the end result.

                                    Option 2 - convert the query into three queries with a UNION ALL, where each of the queries applies just its own part of the compound predicate, and where the later parts of the union all use the lnnvl() function (or fake it) to avoid duplicating rows that have appeared in earlier parts of the union all.

                                     

                                    In this example you could, as a starting point, add the hint /*+ or_expand */ to the query and this may be sufficient to make Oracle adopt option 2 if it hasn't done so automatically.  (If it has then /*+ no_or_expand */ will disable it and take you to option 1.  (This is assuming you're running 12c or later).

                                     

                                    I've written several notes about this type of action - as done by Oracle and as faked, and alternatives for earlier versions of Oracle.  If you want to read more on the topic you could start with https://jonathanlewis.wordpress.com/2018/03/02/conditional-sql-5/  which contains several links to other items, or you could search my blog for "conditional sql" : https://jonathanlewis.wordpress.com/?s=conditional+sql  (and Condition SQL 5 will be the one that appears at the top there).

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next