This quest started with the usual question: why is this query so slow? To put it in the picture, it was a query loading one DWH table by reading one source table from a legacy system (already loaded to Oracle, so no heterogenous services were involved at this step), joining it several times to several tables. (It's the usual badly-designed legacy system: if flag1 is I, join table T1 by C1, if flag1 is N, join table T1 by C2... 20 times.) If I simplify the query, we are talking about something like: SELECT T1.m, case when T1.h = 'I' then T2_I.n when T1.h = 'G' then T2_G.n else null end FROM T1 LEFT OUTER JOIN T2 T2_I ON (T1.h = 'I' and T1.y = T2_I.c1) LEFT OUTER JOIN T2 T2_G ON (T1.h = 'G' and T1.z = T2_G.c2) We even know, that the query always return number of rows identical to number of rows in T2. However, ommiting the T1.h = 'I'/'G' conditions in join clause would duplicate the rows, so the conditions are necessary there. O...
I use this blog to publish the small bits I come accross when using the Oracle Database. It's the "every day I learn something new" style.