56 High-Function Business Intelligence in e-business
Example 2-27 CASE expression — matching query
SELECT Period.Year,
SUM(Fact.Quantity) ,
SUM(
(CASE WHEN Fact.DeliveryCode = 'Y'
THEN (Fact.Amount)
WHEN Fact.DeliveryCode = ‘N’
THEN (Fact.Quantity * Product.Price)
ELSE 0
END)
) AS RevenueForecast
FROM
Fact, Product, Period
WHERE
Fact.prod_id = Product.id and
Fact.period_id = Period.id and
Period.Year = 2002
GROUP BY Period.year,Product.id,Fact.DeliveryCode
2.7.4 Matching inhibited
Query rewrite component currently does not consider materialized view
optimization in the following cases.
1. Query includes the following constructs:
A query that includes the following constructs will not be considered for
materialized view query rewrite. This is not a comprehensive list. Also, some
of these restrictions may be removed in future releases.
– A base table in the materialized view is itself a target of an UPDATE. For
example, when there is a trigger involved, and the query may select from
the same base table that it also updates.
– Recursion or other complex constructs.
– Physical property functions like NODENUMBER.
– Outer Join.
– UNION.
– XMLAGG.
– Window aggregation functions. These are aggregate functions specified
with the OVER clause.
Chapter 2. DB2 UDB’s materialized views 57
2. Materialized view missing columns that are in the query:
If the materialized view is missing columns that exist in the base tables, and
the query references those columns, then the materialized view will be
ignored for optimization.
Consider the materialized view shown in Example 2-28.
Example 2-28 Materialized view contains fewer columns than in query
CREATE SUMMARY TABLE custtrans AS
(
SELECT cust_id, COUNT(*) AS counttrans
FROM trans
GROUP BY cust_id
)
DATA INTITIALLY DEFERRED REFRESH DEFERRED
A user query that looks like the following will result in the materialized view
being ignored for optimization purposes. This is because the” trans_date”
column has not been defined in the materialized view shown in Example 2-29.
Example 2-29 Materialized view contains fewer columns than in query — no match
SELECT cust_id, COUNT(*)
FROM trans
WHERE trans_date > '2002-01-01'
GROUP BY cust_id
3. Materialized view contains more restrictive predicates than in the query:
A materialized view cannot be considered matching if it is missing rows
required to satisfy the user query.
Consider the materialized view shown in Example 2-30:
Example 2-30 Materialized view with more restrictive predicates
CREATE SUMMARY TABLE custtrans AS
(
SELECT cust_id, COUNT(*) AS counttrans
FROM trans
WHERE cust_id > 500
Note: The predicates involved in the materialized view must be a superset
of the of those involved in the query. In DB2 V7, predicate analysis to
detect this was limited, where only exact matches or simple equality
predicates and IN predicates were considered. In DB2 V8, the analysis has
been expanded to cover a broader range of predicates.
58 High-Function Business Intelligence in e-business
GROUP BY cust_id
)
DATA INTITIALLY DEFERRED REFRESH DEFERRED
A user query that looks like the following will result in the materialized view
being ignored for optimization purposes. That is, rows corresponding to
cust_id between 400 and 500 are missing in the materialized view shown in
Example 2-31.
Example 2-31 Materialized view with more restrictive predicates — no match
SELECT cust_id, COUNT(*)
FROM trans
WHERE cust_id >= 400
GROUP BY cust_id
4. Query with an expression not derivable from materialized view:
Even if the expression used in the materialized view is not identical to that
used in the query, it might be possible to derive the expression used in the
query from that in the materialized view. However, it is possible for some
“obvious” matching cases to be ignored by DB2 due to precision or other
issues. These restrictions will eventually be handled in future.
Consider the materialized view shown in Example 2-32:
Example 2-32 Query: expression not derivable from materialized view
CREATE summary table custtrans AS
(
SELECT cust_id, SUM(sale_price) AS total, COUNT(items) AS countitems
FROM trans
GROUP BY cust_id
)
DATA INTITIALLY DEFERRED REFRESH DEFERRED
A user query that looks like that shown in Example 2-33 will result in the
materialized view being ignored for optimization purposes, since the
expression cannot be derived by DB2. However, it is possible for some
“obvious” matching cases to be ignored by DB2, either due to precision
issues, truncation issues, or a possibly not implemented as yet. Some of
these restrictions may eventually be handled in the future.
Example 2-33 Query: expression not derivable from materialized view — no match
SELECT cust_id, SUM(sale_price * 0.15) / COUNT(items)
FROM trans
GROUP BY cust_id
Chapter 2. DB2 UDB’s materialized views 59
5. Friendly arithmetic:
The database configuration parameter:
DFT_SQLMATHWARN NO |YES
sets the default value that determines the handling of arithmetic errors and
retrieval conversion errors as errors (unfriendly) or warnings (friendly) during
SQL statement compilation. For static SQL statements, the value of this
parameter is associated with the package at BIND time. For dynamic SQL
statements, the value of this parameter is used when the statement is
prepared.
The default is NO (unfriendly).
The materialized view will
not be considered for query rewrite if the query
demands unfriendly arithmetic, and the materialized view supports friendly
arithmetic.
The materialized view will be considered for query rewrite when the query and
materialized view have identical arithmetic requirements, and also when the
query demands friendly arithmetic and the materialized view supports
unfriendly arithmetic.
6.
Isolation mismatch
The isolation level of the materialized view must be equivalent or higher than
that demanded of the user query.
For example, if the materialized view is defined with ISOLATION of CS, then a
query that requests:
– Either UR or CS can match with the materialized view
– RS or RR will not be considered for matching
Note: It is rare for this option to be changed after initial database creation,
since the ramifications may be significant. Please refer to the
DB2
Administration Guide
for more details.
Note: It is important to know the ISOLATION under which the materialized
view was created. The CLP command CHANGE ISOLATION TO... may be
used to set the ISOLATION level before creating the materialized view.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.