Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
If you rewrite with 2 subqueries and UNION ALL you get duplicates or not? –  ypercube Oct 21 '13 at 20:45
    
Yes, union all on two queries produces result with dups if I got your question –  smwoody Oct 21 '13 at 20:51
1  
So if you use UNION (without ALL) is it faster or slower? Different plan? –  ypercube Oct 21 '13 at 21:09
1  
How many rows are really in TOP_USTR? –  Phil Oct 21 '13 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 '13 at 6:45

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '13 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. –  Allan S. Hansen Oct 22 '13 at 7:00
    
Wow, impressive link! –  smwoody Oct 22 '13 at 7:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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