How to avoid OR expansion?

Question

How to optimize the following query if the OR operator is used in a join condition to avoid the OR expansion in terms of SQL tuning?

SELECT t1.A, t2.B, t1.C, t1.D, t2.E 
   FROM t1 LEFT JOIN t2
   ON t1.A=t2.A OR t1.B=t2.C;

What is your problem with this query? See [here](https://stackoverflow.com/a/34975420/4808122) the minimum you should post — Marmite Bomber, Apr 22 '20 at 20:21
Hi, here, as far as I know, the use of the OR operator is not a good thing in terms of the performance. So I wanted to learn how to avoid OR expansion in terms of SQL tuning. — general46, Apr 22 '20 at 21:46
What version of Oracle do you use? This is relevant here - check `select * from V$VERSION` — Marmite Bomber, Apr 23 '20 at 04:47
You should also learn to distinct between *using OR in a predicate* and the *OR expansion*. The former is done by you while writing the query, the latter is done by the Oracle Optimizer by *transforming the query* with the intention to help you, in case the *OR predicate* may cause performance problems. — Marmite Bomber, Apr 23 '20 at 05:24

score 0 · Answer 1 · answered Mar 12 '20 at 06:51

0

Having OR condition will ignore using indexes. So once you have the following indexes -

t1 (A, B)
t2 (A, C)

You may try below query having UNION ALL clause -

SELECT t1.A, t2.B, t1.C, t1.D, t2.E 
FROM t1 LEFT JOIN t2 ON t1.A=t2.A
UNION ALL
SELECT t1.A, t2.B, t1.C, t1.D, t2.E
FROM t1 LEFT JOIN t2 ON t1.B=t2.C;

This query will use index and might perform faster.

answered Mar 12 '20 at 06:51

Ankit Bajpai

13,128
4
25
40

5

*Having OR condition will ignore using indexes* - do you have a source for that? – juergen d Mar 12 '20 at 07:00
Transforming `or` into `union` is 'OR expansion', which is exactly what the OP wanted to avoid. https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion – William Robertson Mar 12 '20 at 10:33
a very rough rule of thumb is to replace an Or with a Union to avoid table scanning. – Leketo Mar 12 '20 at 21:46
Well, whatever the reason, the OP wants to avoid it. – William Robertson Mar 13 '20 at 17:18
Your answert is **twice** wrong. First the proposed query returns *different* result (`UNION`should be used instead of `UNION ALL`). Secondly index is used to access *a small number of row based on access predicate*, here *all rows are joined*, so no index will be used in neither variant. (ignoring the option of the *fast full index scan*). – Marmite Bomber Apr 22 '20 at 20:02

score 0 · Answer 2 · answered May 22 '23 at 03:37

Use the NO_EXPAND hint to prevent OR-expansion:

SELECT /*+ NO_EXPAND */ t1.A, t2.B, t1.C, t1.D, t2.E 
   FROM t1 LEFT JOIN t2
   ON t1.A=t2.A OR t1.B=t2.C;

However, the chances are very small that the above hint will improve your query performance. There is no rule that says OR-expansion is a bad thing. Telling the Oracle optimizer precisely how to do its job is usually a bad idea. Ideally, you want to create useful information by gathering statistics, and let the optimizer do its job.

How to avoid OR expansion?

2 Answers2