Oracle complex query with multiple joins on same table

Question

I am dealing with a monster query ( ~800 lines ) on oracle 11, and its taking expensive resources.

The main problem here is a table mouvement with about ~18 million lines, on which I have like 30 left joins on this table.

LEFT JOIN mouvement mracct_ad1 
    ON mracct_ad1.code_portefeuille = t.code_portefeuille
    AND mracct_ad1.statut_ligne = 'PROPRE'
    AND substr(mracct_ad1.code_valeur,1,4) = 'MRAC'
    AND mracct_ad1.code_transaction = t.code_transaction
LEFT JOIN mouvement mracct_zias 
    ON mracct_zias.code_portefeuille = t.code_portefeuille
    AND mracct_zias.statut_ligne = 'PROPRE'
    AND substr(mracct_zias.code_valeur,1,4) = 'PRAC'
    AND mracct_zias.code_transaction = t.code_transaction
LEFT JOIN mouvement mracct_zixs 
    ON mracct_zias.code_portefeuille = t.code_portefeuille
    AND mracct_zias.statut_ligne = 'XROPRE'
    AND substr(mracct_zias.code_valeur,1,4) = 'MRAT'
    AND mracct_zias.code_transaction = t.code_transaction

is there some way so I can get rid of the left joins, (union join or example) to make the query faster and consumes less? execution plan or something?

Please explain what is even remotely different between those two joins other than the alias of the mouvement table. — dfundako, Aug 10 '18 at 16:50

The Impaler · Answer 1 · 2018-08-10T19:24:52.190

2

Just a note on performance. Usually you want to "rephrase" conditions like:

AND substr(mracct_ad1.code_valeur,1,4) = 'MRAC'

In simple words, expressions on the left side of the equality will prevent the best usage of indexes and may push the SQL optimizer toward a less than optimal plan. The database engine will end up doing more work than is really needed, and the query will be [much] slower. In extreme cases they can even decide to use a Full Table Scan. In this case you can rephrase it as:

AND mracct_ad1.code_valeur like 'MRAC%'

or:

AND mracct_ad1.code_valeur >= 'MRAC' AND mracct_ad1.code_valeur < 'MRAD'

edited Aug 10 '18 at 19:24

answered Aug 10 '18 at 18:01

The Impaler

45,731
9
39
76

That second one is risky, due to `NLS_SORT`, isn't it? – Matthew McPeak Aug 10 '18 at 19:01
@Matthew Yes, I agree with you. Even though the second one is still an option, I would lean towards avoiding it, since it can be error prone when using non-typical collations. – The Impaler Aug 10 '18 at 19:23
1

Although a good idea, it seems highly unlikely that replacing the `substr()` with `like` would have a noticeable affect on a query with 30 left joins. – Gordon Linoff Aug 11 '18 at 12:31

Gordon Linoff · Answer 2 · 2018-08-11T12:32:37.193

0

I am guessing so. Your code sample doesn't make much sense, but you can probably do conditional aggregation:

left join
(select m.code_portefeuille, m.code_transaction,
        max(case when m.statut_ligne = 'PROPRE' and m.code_valeur like 'MRAC%' then ? end) as ad1,
        max(case when m.statut_ligne = 'PROPRE' and m.code_valeur like 'MRAC%' then ? end) as zia,
        . . .  -- for all the rest of the joins as well
 from mouvement m
 group by m.code_portefeuille, m.code_transaction
) m
on m.code_portefeuille = t.code_portefeuille and m.code_transaction = t.code_transaction

You can probably replace all 30 joins with a single join to the aggregated table.

edited Aug 11 '18 at 12:32

answered Aug 10 '18 at 16:51

Gordon Linoff

1,242,037
58
646
786

well. its just the same join with different values. – mouad tk Aug 11 '18 at 12:19
1

@mouadtk . . . No, this replaces a bunch of different joins with a single join to an aggregated result. I made the answer a bit clearer to this point. – Gordon Linoff Aug 11 '18 at 12:30

Oracle complex query with multiple joins on same table

2 Answers2