Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome.

In short we have left-join query like:

select from a 
left join b on a.key1=b.key1 or a.key1=b.key2

This works VERY slowly, while at the same time both separately:

select from a 
left join b on a.key1=b.key1

select from a 
left join b on a.key1=b.key2

work very fast.

b.key1 has normal index

b.key2 has normal index

I can't understand the reason for such behavior? Am I missing something very basic in my join strategy or index usage?

Here we go with detailed plans:

WITHOUT OR(TOP_USTR_ADMIN_IP - index name for ustrip column):

SQL> explain plan for
SELECT * FROM top.macs_constraint mc 
LEFT JOIN top.top_ustr tu ON  tu.ustrip = mc.IP;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

   Plan hash value: 349751289

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    22 |  1296   (1)| 00:00:16 |
|   1 |  SORT AGGREGATE        |                   |     1 |    22 |            |              |
|*  2 |   HASH JOIN RIGHT OUTER|                   |   981K|    20M|  1296   (1)| 00:00:16 |
|*  3 |    INDEX FAST FULL SCAN| TOP_USTR_ADMIN_IP | 23187 |   181K|    62   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | MACS_CONSTRAINT   |   629K|  8601K|  1231   (1)| 00:00:15 |
--------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TU"."USTRIP"(+)="MC"."IP")
   3 - filter("TU"."USTRIP"(+) IS NOT NULL)

WITH OR:

```
e

OR is slower because you no longer can perform an index seek, but effectively forces the database engine to look through each leaf node in your index tree.
With a single parameter (no OR), the engine can seek down through the index to the relevant leaf nodes, but when you ask it an OR, it scans the entire range of leaf nodes.

If the performance is too slow to tolerate, do as the comments suggest and try a UNION (no duplicate values) or a UNION ALL (allow duplicate values), or even two separate queries on their own and then join the results together in the code layer receiving the results.

StackExchange Database Administrators Q#51914, answer score: 6