Navneet goyal bits pilani 11 partitioningfacttables

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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

Ask a homework question - tutors are online