Query speed is slow when using decode in join condition

Question

I need to write a query which basically needs to (left) join rows from two tables. My initial query looks like this:

  select *
  from Table1 T1
  left join Table2 T2 on T1.f1 = T2.f1
                     and (decode(T2.f2,
                                 '1',
                                 'value1',
                                 '2',
                                 'value2',
                                 '3',
                                 'value3') = T1.f2 or T2.f2 = '4')

What I am tring to do is in Table1 and Table2 there is two fields (T1.f1,T2.f1) which can be used as join condition directly, but another two fields which need some transformation before being used in join condition since the values in these two columns do not have the same value set.

And now this query runs really unacceptably slow. I tried to removed the whole

and (decode(T2.f2,
                             '1',
                             'value1',
                             '2',
                             'value2',
                             '3',
                             'value3') = T1.f2 or T2.f2 = '4')

condition and it runs OK. Then I tried to remove the T2.f2 = '4' part, i.e., the conditions looks like this:

left join Table2 T2 on T1.f1 = T2.f1
                 and (decode(T2.f2,
                             '1',
                             'value1',
                             '2',
                             'value2',
                             '3',
                             'value3') = T1.f2 )

and it also runs OK. So how should I modify the query (of course having the same result as original) to make it run fast? What is holding the execution?

Can you post the explain plan for the two versions of the query. — MT0, Jul 20 '16 at 08:36

score 1 · Answer 1 · answered Jul 20 '16 at 08:33

1

If you use DECODE( ... ) (or any function) then Oracle cannot use the index on that column and will perform a table scan.

You can either create a function-based index or you can try rewriting the query without the function:

LEFT OUTER JOIN
Table2 T2
ON (   T1.f1 = T2.T1
   AND (   ( T1.f2 = 'value1' AND T2.f2 = '1' )
        OR ( T1.f2 = 'value2' AND T2.f2 = '2' )
        OR ( T1.f2 = 'value3' AND T2.f2 = '3' )
        OR (                      T2.f2 = '4' )
       )
   )

answered Jul 20 '16 at 08:33

MT0

143,790
11
59
117

When I have only `ON ( T1.f1 = T2.T1 AND ( T1.f2 = 'value1' AND T2.f2 = '1' ))` then it runs fast but even if I have `ON ( T1.f1 = T2.T1 AND ( ( T1.f2 = 'value1' AND T2.f2 = '1' ) OR ( T1.f2 = 'value2' AND T2.f2 = '2' )))` it becomes slow again – tete Jul 20 '16 at 09:14

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Take a look at this:

(Update: old post which doesn't refer to ORACLE)

Is having an 'OR' in an INNER JOIN condition a bad idea?

Alternatively, blending the T2.f2 = '4' condition with the decode condition might speed it up, although I haven't tested it. It would look like this:

left join Table2 T2 on T1.f1 = T2.f1
             and (decode(T2.f2,
                         '1',
                         'value1',
                         '2',
                         'value2',
                         '3',
                         'value3',
                         '4',
                         T1.f2) = T1.f2 )

I hope this helps.

edited May 23 '17 at 12:22

Community

1
1

answered Jul 20 '16 at 08:29

Russop

36
3

This is entirely different logic - this is `OR ( T1.f2 = '4' AND T2.f2 NOT IN ( 'value1', 'value2', 'value3' ) )` whereas the OP's solution will match for `T1.f2 = '4'` and `T2.f2` has any value. – MT0 Jul 20 '16 at 08:32
Also, the question you've linked is for SQL Server (not Oracle) and is from 5 years ago (and databases have moved on several versions since then). Do you have an up-to-date reference for the correct RDBMS? – MT0 Jul 20 '16 at 08:39
Apologies if I'm missing something obvious, but the OP's logic is to match for `T2.f2 = '4'` not `T1.f2 = '4'`. – Russop Jul 20 '16 at 08:50
Yes, I wrote the table aliases back-to-front; however, the same comment applies, just swap `T1` and `T2`. – MT0 Jul 20 '16 at 08:59
So writing them the right way around means that my condition is equivalent to ` OR ( T2.f2 = '4' AND T1.f2 NOT IN ( 'value1', 'value2', 'value3' ) )` which given the decode statement is equivalent to ` OR ( T2.f2 = '4' AND T2.f2 NOT IN ('1','2','3') )` which of course boils down to ` OR T2.f2 = '4'`. I don't think your comment applies - my logic is equivalent to yours and the OP's post. As for the post I linked, you're right, I will update my answer. – Russop Jul 20 '16 at 10:06

score 0 · Accepted Answer · answered Jul 20 '16 at 08:41

0

You can use with clause (also materialize hint)

WITH
  T2 AS
(
      select /*+ materialize */
  decode(table2.f2,
                         '1',
                         'value1',
                         '2',
                         'value2',
                         '3',
                         'value3',
                         '4',
                         T1.f2) as join_sttmnt
  , Table2.*
from table2
)
select *
  from Table1 T1
  left join T2 on T1.f1 = T2.f1
                     and (join_sttmnt = T1.f2 or T2.f2 = '4')

I did not try it but the logic is true.

answered Jul 20 '16 at 08:41

CompEng

7,161
16
68
122

Why down vote? This is so far the working solution for me. As I mentioned on other comments, other solutions are still slow in my test. I don't care too much about using temp table, since I am going to use this query in a job which runs once at the end of day. – tete Jul 21 '16 at 03:09
Obviously not you. Somebody down voted your answer. While for me it is a working solution. – tete Jul 21 '16 at 05:14

Query speed is slow when using decode in join condition

3 Answers3