Hi Robert,
There is a lot of factors that slows the query execution, here is the description of some of them:
And of course, the most important factor in dw applications, summary and join aggregates. Thankfully, as previously pointed out, the aggregate aware function can be used to let bo navigate the various levels of aggregation. Aggregation is beneficial in that many of the summarising functions and complex joins can be performed, say on a daily basis, at load time - therefore these operations are not required to be performed at execution time. For example, take a 2 million row fact table with the grain at the order line item level. If you aggregate this to say the day level, i.e. remove order number and order line item number (and of course summarising the measures), this will probably reduce to around the 3 to 400,000 mark. Approx 80% reduction in the number of rows.
If you aggregate further to say weekly level, this can be reduce to less than 100,000 rows. Further aggreations produce less beneficial returns, however, it all depends upon the specific requirements in terms of execution time, reporting frequency, storage restrictions etc etc.
The overhead in producing these aggregates in not restrictive. You could probably aggregate a 2 million row fact table to daily level in Oracle (using Parallel CTAS) in about 2-5 minutes, dependant upon server configuration.
Of course, the first place to start is to make sure that the databse is tuned optimally. i.e. sort area size, star transformation, db block size, multi bock read count, hash area size, indexing, physical file layout, disk configuration (i.e. striping, mirroring, cluster size etc), network bandwidth etc etc.
A book in itself - in fact, it may be worth investing in a db tuning manual, particularly the ones focused upon dw apps.
Regards
Brian Patterson
Listserv Archives (BOB member since 2002-06-25)