Learn by play

Today was a playday. I spent the day having a go with the new Enterprise Edition of Oracle’s BI suite. I found some of the tools very easy use and as a person with a database rather than a user-tool background I didn’t have that baggage of saying Discoverer is better for that, Business Objects is better at this and Cognos is easier for the other. There are a couple of things I might ask Mark Rittman about – I know he knows the answer as he demonstrated it working in oc4j (ports and urls for example) but pride may stop me talking to him!

I also encouraged one of my developers to play with the database and in so doing I found out something I did not know; which of course is a good thing, if I do not learn at least one new thing a day I am not doing life right.

One of our customers has a retail data warehouse with type one slowly changing dimensions. Once or twice a year they ask us to rebuild the summaries to reflect current reporting hierarchies. This normally requires several days of downtime as 4 odd terabytes of data are reaggregated. Of course, the customer does not like any downtime. So for the last week my developer has been looking at various techniques to minimise the time the rebuild will take. He has meticulously documented the timings of various techniques. Today he was looking in detail at what happens when a single partition of 500 million row table is rebuilt. He set all the local bitmap index partitions to unusable, he truncated the partition and then append inserted to reload the data. Then he made the discovery – the index partitions were usable after the insert. After further tests he found that the truncate partition re-enabled the bitmap indexes. So he then swapped the order of the truncate and the index disable steps and found that time to refresh a single partition halved (and that includes the rebuild of the index partitions).

And please do not ask why we rebuild our materialized views by hand or I might be forced to tell you.