sqloraclesql-optimization

why "OR" operator is slower than union in oracle


Does anyone know why an "OR" operator is slower than union in ORACLE.

I have query like this:

Select
   O.Order_number,
   DA. ID,
   DA.Country,
   Sum(amount) Amount
from
   Order O
   left join Delivery_Address DA on
      O.ID = DA.order_Id
   left join TBL_A on 
     TBL_A.DA_ID = DA.ID
enter code here
   < ... Left joining another 10 tables>
enter code here
   Left join Transaction Tr on
     TR.Order_id = Order.id
where
   DA.Country = 'USA'
OR
   Tr.transaction_Date between to_date('20200701','yyyymmdd') and sysdate

This takes 200 secs for the first 50 records.

   Select
      O.Order_number,
      DA.ID,
      DA.Country,
      Sum(amount) Amount
   from
      Order O
      left join Delivery_Address DA on
         O.ID = DA.order_Id
      left join TBL_A on 
         TBL_A.DA_ID = DA.ID
      enter code here
      < ... Left joining another 10 tables>
      enter code here
      Left join Transaction Tr on
         TR.Order_id = Order.id
   where
      DA.Country = 'USA'
union
    Select
       O.Order_number,
       DA. ID,
       DA.Country,
       Sum(amount) Amount
    from
       Order O
       left join Delivery_Address DA on
          O.ID = DA.order_Id
       left join TBL_A on 
         TBL_A.DA_ID = DA.ID
    enter code here
       < ... Left joining another 10 tables>
    enter code here
       Left join Transaction Tr on
         TR.Order_id = Order.id
    where
       Tr.transaction_Date between to_date('20200701','yyyymmdd') and sysdate

This second query takes 13 secs for the first 50 records.

The transaction_date from the Transaction table is indexed, but the Country column is not indexed.

Anyone have any idea?


Solution

  • The OR allows each subquery to be evaluated independently.

    You would have to look at the execution plans to see what is really happening. However, in the first subquery query, an index using da.country is probably using an index. And in the second, tr.transaction_date.