4

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;
general46
  • 700
  • 5
  • 12
  • 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

1 Answers1

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.

Ankit Bajpai
  • 11,992
  • 4
  • 23
  • 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