Group By Rollup and other exotica
One of the topics I have wanted to research, but have never managed to find time to, is a performance comparison of Oracle OLAP technology and the SQL grouping extensions such as GROUP BY ROLLUP, GROUP BY CUBE and GROUP BY GROUPING SETS. I suppose for that mater I should also include conventional aggregate materialized view summaries, with query rewrite selecting the most appropriate summary for a given query. From a BI design point of view it intrigues me to determine what would have the best performance in the "summary" maintenance batch and what gives the best performance for user queries. And for added fun I could explore partitioning in rolled-up structures; for example if I range partition on date I could hash sub-partition on GROUPING_ID and potentially get a decent amount of partition elimination.
Chatting to a fellow Miracle Database Forum, Edinburgh (MDF-E, other conference abbreviations are available) delegate, Simon Kelsey, we discussed how a GROUP BY GROUPING SET materialized view could be used with query rewrite and I said I would look out a reference example. So here it is. This example comes from the Hobbs, Hillson, Lawande & Smith book, Oracle 10g Data Warehousing (page 417) and comes with a nice execution plan that clearly shows rewrite occurring. In reality, I think the only way I would use group by extensions is in materialized view definitions. Query rewrite hides the complex SQL (or at least "the difficult for a drag-n-drop tool") syntax from the user.