DB2 UDB's High-Function Business Intelligence in e-business

© Copyright IBM Corp. 2002 xiii

Examples

2-1 Example of creating a deferred refresh materialized view . . . . . . . . . . . 19

2-2 Example of creating a refresh immediate materialized view . . . . . . . . . 19

2-3 LOADing from a cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

2-4 Creating a refresh deferred materialized view . . . . . . . . . . . . . . . . . . . . 27

2-5 Materialized view with REFRESH DEFERRED option . . . . . . . . . . . . . 30

2-6 Creating a refresh immediate materialized view . . . . . . . . . . . . . . . . . . 34

2-7 Superset predicates and perfect match materialized view 1 . . . . . . . . . 45

2-8 Superset predicates and perfect match — matching query 1 . . . . . . . . 45

2-9 Superset predicates and perfect match materialized view 2 . . . . . . . . . 45

2-10 Superset predicates and perfect match — matching query 2 . . . . . . . . 46

2-11 Aggregation functions & grouping columns materialized view 1 . . . . . . 47

2-12 Aggregation functions & grouping columns — matching query 1 . . . . . 47

2-13 Aggregation functions & grouping columns materialized view 2 . . . . . . 47

2-14 Aggregation functions & grouping columns — matching query 2 . . . . . 48

2-15 Aggregation functions & grouping columns materialized view 3 . . . . . . 48

2-16 Aggregation functions & grouping columns — matching query 3 . . . . . 48

2-17 Internally rewritten query by DB2 using the materialized view. . . . . . . . 49

2-18 Nullable columns or expressions in GROUP BY . . . . . . . . . . . . . . . . . . 50

2-19 Nullable columns or expressions in GROUP BY — user query. . . . . . . 51

2-20 Rewritten query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

2-21 Extra tables in the query materialized view . . . . . . . . . . . . . . . . . . . . . . 52

2-22 Extra tables in the query — matching query . . . . . . . . . . . . . . . . . . . . . 52

2-23 Extra tables in the materialized view . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

2-24 Extra tables in the materialized view — matching query . . . . . . . . . . . . 53

2-25 Informational and system-maintained referential integrity constraints . . 54

2-26 CASE expression materialized view . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

2-27 CASE expression — matching query. . . . . . . . . . . . . . . . . . . . . . . . . . . 56

2-28 Materialized view contains fewer columns than in query . . . . . . . . . . . . 57

2-29 Materialized view contains fewer columns than in query — no match. . 57

2-30 Materialized view with more restrictive predicates. . . . . . . . . . . . . . . . . 57

2-31 Materialized view with more restrictive predicates — no match. . . . . . . 58

2-32 Query: expression not derivable from materialized view . . . . . . . . . . . . 58

2-33 Query: expression not derivable from materialized view — no match . . 58

2-34 Capturing snapshot data into a table . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

2-35 Query involving a simple predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

2-36 Generalize simple predicate to GROUP BY in a materialized view . . . . 65

2-37 “Problem” queries listed in priority order . . . . . . . . . . . . . . . . . . . . . . . . 71

2-38 Materialized view AST3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73