Hi Guys,
I have inherited from a previous devloper the query below. T_MAXVALUES has 100 000 rows and T_STA_ERG about 1m.
The performance is so bad, it runs since 5h now and I got nothing so I can´t say much about test data or how it should look.
This runs on Oracle 11g and I can´t get my head around on all the JOIN clauses and the ultimate purpose but sense that there might be a better way
to achieve what is tried below. Any of you guru´s able to immediately spot the big inefficencies?
SELECT DISTINCT T1.PARAMETER, T1.ZERL_MATRIX, T1.MONTH, T1.ADDITIONAL_CRI,
DECODE(H2.MRL,NULL,(DECODE(H1.MRL,NULL,(DECODE(H3.MRL,NULL,(DECODE(H4.MRL,NULL,NULL,'R')),'R')),H1.MRL)),H2.MRL)
FROM T_STA_ERG T1
LEFT JOIN (SELECT PARAMETER,ZERL_MATRIX_2,MRL
FROM T_MAXVALUES WHERE ZERL_MATRIX_1 IS NULL) H1
ON T1.PARAMETER = H1.PARAMETER
AND substr(T1.ZERL_MATRIX,4,3) = H1.ZERL_MATRIX_2
LEFT JOIN (SELECT PARAMETER,ZERL_MATRIX_1, ZERL_MATRIX_2, MRL
FROM T_MAXVALUES
WHERE ZERL_MATRIX_1 IS NOT NULL) H2
ON T1.PARAMETER = H2.PARAMETER
AND substr(T1.ZERL_MATRIX,1,3) = H2.ZERL_MATRIX_1
AND substr(T1.ZERL_MATRIX,4,3) = H2.ZERL_MATRIX_2
LEFT JOIN (SELECT PARAMETER,ZERL_MATRIX_2, MRL
FROM T_MAXVALUES
WHERE ZERL_MATRIX_1 IS NULL) H3
ON T1.PARAMETER = H3.PARAMETER
LEFT JOIN T_MAXVALUES H4
ON substr(T1.ZERL_MATRIX,1,3) = H4.ZERL_MATRIX_1