Unformatted text preview: 00','DD/MM/YYYY')), );
03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 11 Partitioning Fact Tables wrt TIME
create table sales ( id number(2), id number(2), ale_date date, nits number(3), mount number(5)),
03/15/10 03/15/10 artition by range (sale_date) Prof. Navneet Goyal, BITS, Pilani 12 Partitioning Fact Tables wrt TIME
Seeing the partitions select partition_name from user_tab_partitions where table_name='SALES'; Seeing the records in a partition select * from sales partition (sale_1999); select * from sales partition (sale_2000); Now check what you get when you run the following command: select * from sales; What does it give you? And what you can infer from it?
03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 13 Aggregation
The single most dramatic way to affect performance in a large data warehouse is to provide a proper set of aggregate (summary) records ... in some cases speeding queries by a factor of 100 or even 1,000. No other means exist to harvest such spectacular gains." Ralph kimball
03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 14 Aggregation Still aggregations is so underused. Why? We are still not comfortable with redundancy! Requires extra space Most of us are not sure of what aggregates to store A bizarre phenomena called
SPARSITY FAILURE SPARSITY
03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 15 Aggregates & Indexes
Analogous?? Indexes duplicate the information content of indexed columns We don’t disparage this duplication as redundancy because of the benefits Aggregates duplicate the information content of aggregated columns Traditional indexes very quickly retrieve a small no. of qualifying records in OLTP systems In DW, queries require millions of records to be summarized Bypassing indexes and performing table scans 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 16 Aggregates
Need new indexes that can quickly and “logically” get us to millions of records Logically because we need only the summarized result and not the individual records Aggregates as Summary Indexes! 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 17 Aggregates
Aggregates belong to the same DB as the low level atomic data that is indexed (unlike data marts) Queries should always target the atomic data Aggregate Navigation automatically rewrites queries to access the best presently available aggregate...
View Full Document
- Summer '10
- Computer Science, Dimension table, Fact table, Prof. Navneet Goyal