... our use of an index organized table in our ETL process and I promised to jot a quick note on it. So here goes. There are various combinations of database functionality that I do not like mixing in a data warehouse. For instance I impose rigorous RI checking in the

One of my pet hates is moving shed-loads of data from the disk to aggregate it away to resolve a user's query. Experience tells me that the very thought of doing this imposes a massive strain on a data warehouse. Ok, millions of people tell me that I

Elsewhere [http://www.dizwell.com/prod/node/322] a question popped up relating to binds on partition keys. David Aldridge ran some tests (also posted here [http://oraclesponge.wordpress.com/2006/10/28/partition-pruning-and-bind-variables/] ) that indicated that binds would use global table stats rather than partition stats. Of course this

Yesterday [https://www.rittmanmead.com/blog/2006/08/16/year-to-date-alternatives/], I wrote about the use of a table function as an alternative to a date transformation table in the calculation of YTD. Mischievously, I used filter conditions to restrict my selection to a single product and store over an April

David Aldridge [http://oraclesponge.wordpress.com/] challenged me to write about functions that return lists of dates [http://oraclesponge.wordpress.com/2006/08/15/year-to-date-query-improvement-through-transformation-tables/#comment-1499] . So here goes In blogs past I wrote about the joys of calculating year-to-date [https://www.rittmanmead.com/blog/2006/06/14/time-series-revisited/], YTD,

One of my customers runs a chain of supermarkets. The bottom three levels of their product hierarchy are: * Saleable unit: Product as sold - could be price marked, promotion pack, "buy on get one for free" (BOGOFF) or whatever was provided by their suppliers. Each has it own

Occasionally, people ask me why I prefer to use pre-built tables for materialized views rather than use the syntax that creates the view and allocates its storage. One reason I give is that it gives me the option of changing the MV query to improve the chances of rewrite. And

One of the paragraphs in Powell's DW tuning book [https://www.rittmanmead.com/blog/2006/08/06/sitting-in-the-garden-and-reading/] was something on the lines of (and the book is not with me right now) ...features such as bitmap indexes, bitmap join indexes, index organised tables and cluster tables were

The other day [https://www.rittmanmead.com/blog/2006/07/31/re-summarise-versus-fast-mview-refresh-part-1/] I talked about on commit refresh of m-views; of the other options for refresh, on demand is probably most useful for data warehouse users. Mviews can also be refreshed at fixed intervals, but this has the possible pitfall

A couple of days ago I wrote [https://www.rittmanmead.com/blog/2006/07/23/re-summarisation/] that we may need re-build summary tables when items change parents. More exactly, it is the tables at the parent level (and above) that need to be refreshed when the parents adopt new offspring

Summary tables are often very suitable candidates for partitioning. They are usually date organised and as we generally receive data for a limited range of dates we are able to apply the changes to a subset of partitions of a table. In particular we can get away from the use

If you have been reading my current series on data warehouse design you will have read (or perhaps soon will, as there is also a new piece in preparation!) that a good technique in the batch process is to disable bitmap indexes during an insert and then rebuild them after

Last time [https://www.rittmanmead.com/blog/2006/07/03/dw-design-3-%e2%80%93-raw-facts-and-reference/] I wrote a few general notes about the storage of fact and reference data. I now will look at partitioning and compression in slightly more detail Partitioning is a way to divide a large table into

In data warehouse there is often the need to summarise, aggregate, or roll-up (the three terms often being used interchangeably) fact data. This is either achieved at query time within the query or in advance by the use of pre-built summaries. As I blogged yesterday [https://www.rittmanmead.com/blog/

One of our customers sent in some SQL that took ages to run. On the face of it was not doing anything difficult, it just inserted the results of an aggregate query into another table. The select part of the query joined two large partitioned tables on the partition key

One of the Oracle 9.2 data warehouses my team manages uses nested materialized views for the aggregation strategy. Nesting of aggregates can reduce the workload on the batch as ‘expensive’ roll-ups only need happen once. The downside to nesting is that in order to make query re-write work for

If you read AskTom you will probably know that there is no such thing as a universal go-faster switch in Oracle. Everything is “maybe faster, maybe slower and your mileage may vary”. Over the past week or so we have been user-testing a revised parallelism setting on a 3 TB