Some queries require the most recent data

Techniques for Physical Data Warehouse Design Materialized View
Indexes for Data Warehouses
Evaluation of Star Queries
Data Warehouse Partitioning

Indexing

- Used in Data Warehouse together with materialized views

Physical Data Warehouse Design Outline

SELECTstatement of SQL

If we consider relational views as stored queries then materialized views can be considered as stored results

In brute force method SQL is written to explicitly access the view

Transparent query rewrite method is applied when a query optimizer detects that a query can be computed against a materialized view instead of the source relational tables

Creating materialized view

Creating materialized view CREATE MATERIALIZED VIEW MV_ORDERS
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS( SELECT O_ORDERKEY, O_CUSTKEY, O_TOTALPRICE, O_ORDERDATE
FROM ORDERS
WHERE O_ORDERDATE > TO_DATE('31-DEC-1986','DD-MON-YYYY') );

Access to materialized view through query rewriting

Indirect access to materialized view through query rewriting

Indexes for Data Warehouses

B*-tree can be traversed either:

- vertically from root to leaf level of a tree

Bitmap Indexes

Indexes for Data Warehouses: Requirements

Symmetric partial match queries
- All dimensions of the cube should be symmetrically indexed, to be searched simultaneously
Indexing at multiple levels of aggregation
- Summary tables must be indexed in the same way as base nonaggregated tables
Eacient batch update
- The refreshing time of a data warehouse must be considered when designing the indexing schema
Sparse data
- Typically, only 20% of the cells in a data cube are nonempty
- The indexing schema must deal eaciently with sparse and nonsparse data

Star Queries

Queries over star schemas are called star queries

SELECT ProductName, CustomerName, SUM(SalesAmount)

FROM Sales S, Customer C, Product P

- (1) Evaluation of the join conditions

- (2) Evaluation of the selection conditions over the dimensions

Evaluation of Star Queries using Bitmap Indexes Evaluation of star query requires

- (1) Obtain the record numbers of the records that satisfy the condition

Discontinued = 'Yes'

Techniques for Physical Data Warehouse Design Materialized View
Indexes for Data Warehouses
Evaluation of Star Queries
Data Warehouse Partitioning

Vertical partitioning splits the attributes of a table into groups that can be independently stored

- E.g., most often used attributes are stored in one partition, less often used attributes in another one

Queries over Partitioned Databases

Partition pruning is the typical way of improving query performance using partitioning

- When the reference table is partitioned on its primary key

- Large join is broken down into smaller joins

Time dimension is a natural candidate for range partitioning

Example: A table with a datecolumn deUned as the partitioning key

Partitioning Strategies
List partitioning speciUes a list of values for the partitioning key