2

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: Query Performance Enhancing Techniques Prof. Navneet Goyal Computer Science Department BITS, Pilani Query Performance Query performance is critical as: DW is used by top managers Queries are complex Queries are adhoc Huge volumes of data 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 2 Query Performance Techniques Software: – – – – – Partitioning Aggregation Multidimensional Databases View Materialization Bitmap Indexing Hardware – Parallel Machines Prof. Navneet Goyal, BITS, Pilani 3 03/15/10 03/15/10 Partitioning Improves performance Ease of management Fact tables are partitioned wrt Fact dimensions dimensions Dimension tables may also be partitioned 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 4 Partitioning Horizontal Partitioning – Range – Hash – List List – Composite Vertical Partitioning – Normalization – Row Splitting 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 5 Partitioning: Thumb Rules At no point of time there should be more At than 500 tables in the data warehouse than Do not partition on a dimension grouping Do that is likely to change within the lifecycle of the data warehouse of For example product, & location 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 6 Partitioning: Advantages Faster Access to Data Easy support for Data Purging Parallel DML Backup of Large Tables 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 7 Partitioning Fact Tables Sales Fact Table 100 million records Sales 500 Million Records 100 million records 100 million records 100 million records 100 million records 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 8 Partitioning Fact Tables Wrt Time Dimension? – Equal Size – Unequal Size Wrt Product Dimension? Wrt Location Dimension? Wrt Size? 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 9 Partitioning Fact Tables wrt TIME Range Partitioning: Most commonly Range used used Non-overlapping Partitions Example: Partition By Month 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 10 Partitioning Fact Tables wrt TIME CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITION sales_mar2000 VALUES LES THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/20...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online