Chapter 2. DB2 UDB’s materialized views 55
In the above scenario,
transitem
table is joined to the
pgroup
table on column
pgid. If
pgroup.pgid
is the primary key in the referential integrity relationship,
every value of t
ransitem.pgid
has one and only one value in
pgroup
.
Furthermore, if the
prodline
table has a referential integrity relationship with
the
pgroup
table, where
prodline.lineid
is the primary key, this join is also a
join that does not affect the number of rows in the output. The materialized
view can now be used for applying the query predicate, selecting the columns
required by the query, and consolidating the aggregation by further grouping
on only the columns required in the query.
5. CASE expressions in the query:
Typically, matching queries with complex expressions need to have these
complex expressions used in a similar way in the materialized view. There are
some common scenarios that DB2 will handle. For example, DB2 can match
some user queries with CASE expressions to a materialized view that
contains the elements of the CASE expression as part of the GROUP BY
clause, and the SELECT list of the materialized view. DB2 is able to match
some user queries with CASE expressions as follows.
Consider the materialized view shown in Example 2-26.
Example 2-26 CASE expression materialized view
CREATE TABLE S1 AS
(SELECT
Period.year,
Product.id,
Fact.DeliveryCode,
SUM(Fact.Quantity) AS Quantity,
SUM(Fact.Amount) AS Amount,
SUM(Fact.Quantity * Product.Price) AS QP_Amount
FROM
Fact, Product, Period
WHERE
Fact.prod_id = Product.id and
Fact.period_id = Period.id
GROUP BY
Period.year,
Product.id,
Fact.DeliveryCode)
DATA INITIALLY DEFERRED REFRESH DEFERRED
Note that this materialized view does not have a CASE expression defined.
The query in Example 2-27 will be considered matching for materialized view
optimization purposes. The information required by the user query could
easily be computed from the materialized view as long as
DeliveryCode is
part of the GROUP BY items in the materialized view.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.