I ran into a rather annoying bug with Oracle Warehouse Builder 10.2.0.3 and slowly changing dimensions, type 2 the other day.I ran into a rather annoying bug with Oracle Warehouse Builder and slowly changing dimensions, type 2 the other day.
A very simple mapping of the form "base table" -> "Dimension Table" with no transformations, no changes what so ever, took forever to run the second time around. When the dimension is empty (a ROLAP dimension) and is populated for the first time, it runs in seconds (Members of dimension in this case were 130.000). Then an update was run with around 30.000 new and changed members and this took around 12 hours to run. To put it mildly, this freaked me out. Looking at the generated code it was understandable - endless right outer joins, analytical functions and an impossible execution plan. I tried various ways of trying to influence the generated code but with no luck. I found several references to the same problem on metalink but no solutions.
I finally checked if there was a patch for this - and of course there was a patch - so, if you run into this it is patch 6510560. This is a generic patch, meaning that it applies to all platforms.