CODEWITHSUNDEEP
sqloracle-databasequery-optimizationsql-tuning
mtk
mtk

Reputation: 24

Oracle complex query with multiple joins on same table

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?

Upvotes: 0

Views: 924

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

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'

Upvotes: 2

Related Questions