7

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:

explain plan for
SELECT count(*) FROM top.macs_constraint mc 
LEFT JOIN top.top_ustr tu ON (tu.ustrip = mc.IP or tu.skladIP = mc.IP);  

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2704565128

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |    14 |   322M  (1)|999:59:59 |
|   1 |  SORT AGGREGATE      |                 |     1 |    14 |            |            |
|   2 |   NESTED LOOPS OUTER |                 |  1887K|    25M|   322M  (1)|999:59:59 |
|   3 |    TABLE ACCESS FULL | MACS_CONSTRAINT |   629K|  8601K|  1231   (1)| 00:00:15 |
|   4 |    VIEW              |                 |     3 |       |   513   (1)| 00:00:07 |
|*  5 |     TABLE ACCESS FULL| TOP_USTR        |     3 |    72 |   513   (1)| 00:00:07 |

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

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

   5 - filter("TU"."SKLADIP" IS NOT NULL AND "TU"."SKLADIP"="MC"."IP" OR  
              "TU"."USTRIP" IS NOT NULL AND "TU"."USTRIP"="MC"."IP")

Why does using or in the predicate give nested loops and no index usage? Is it possible to force index usage?

UPDATED: missattached wrong plan for "without or".fixed

5
  • If you rewrite with 2 subqueries and UNION ALL you get duplicates or not? Oct 21, 2013 at 20:45
  • Yes, union all on two queries produces result with dups if I got your question
    – smwoody
    Oct 21, 2013 at 20:51
  • 1
    So if you use UNION (without ALL) is it faster or slower? Different plan? Oct 21, 2013 at 21:09
  • 1
    How many rows are really in TOP_USTR?
    – Philᵀᴹ
    Oct 21, 2013 at 23:05
  • 1 yes, using union is faster, because each separate join uses indexes. Question was why 'or'-predicate does not =) Hansen answer below states that it seems to be well known, but I'v never met such info.Well I am no way a DB guru... 2 top_ustr has about 130k mc has about 660k
    – smwoody
    Oct 22, 2013 at 6:45

1 Answer 1

6

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.

3
  • Is it whell known that using 'or' in join predecate leades to " look through each leaf node in your index tree"? Can I read in some specs ?
    – smwoody
    Oct 22, 2013 at 6:31
  • 1
    I'd say it's "well known" index behavior (anything is well known, if you know if of course :)) However - you could look at for example: blogs.oracle.com/optimizer/entry/or_expansion_transformation where they explain changing ORs into UNIONS for the same reasons and where they also mention to avoid table scans. Oct 22, 2013 at 7:00
  • Wow, impressive link!
    – smwoody
    Oct 22, 2013 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.