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