© 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

Get DB2 UDB's High-Function Business Intelligence in e-business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.