84 High-Function Business Intelligence in e-business
We ran an EXPLAIN of Query 7 to confirm that it was being routed to AST6 as
shown in Example 2-47.
Example 2-47 EXPLAIN of Query 7
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-09-04-17.02.40.562000
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 7.478784e-007
Comm Speed: 0
Buffer Pool size: 5256
Sort Heap size: 20000
Database Heap size: 600
Lock List size: 50
Maximum Lock List: 22
Average Applications: 1
Locks Available: 1243
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
with dt as
(select cast(country_name as varchar(20)) as country_name, COUNT(*) as
count, year, cast(root_level as varchar(20)) as root_level
from fact1_subset, mesh, time, country
Chapter 2. DB2 UDB’s materialized views 85
where fact1_subset.mesh_id=mesh.mesh_id and
fact1_subset.time_id=time.time_id and
fact1_subset.country_id=country.country_id
group by rollup(year), rollup(root_level), country_name) select *
from dt
order by country_name, year, root_level
Optimized Statement:
-------------------
SELECT Q1.COUNTRY_NAME AS "COUNTRY_NAME", Q1.COUNT AS "COUNT", Q1.YEAR AS
"YEAR", Q1.ROOT_LEVEL AS "ROOT_LEVEL"
FROM DB2ADMIN.AST6 AS Q1
WHERE (Q1.GPMONTH = 1) AND (Q1.GPFLEVEL = 1)
ORDER BY Q1.COUNTRY_NAME, Q1.YEAR, Q1.ROOT_LEVEL
Query 8:
The materialized view for this query looks like AST7 in Example 2-48. Note the
following generalization of local predicates for this query:
? The predicates on columns MESH.ROOT_LEVEL and MESH.FIRST_LEVEL
are added to the GROUP BY list, and removed from the predicates. Note that
the MESH.FIRST_LEVEL is an IN predicate.
? The COUNT(*) function in the CASE expression in the select list is replaced
by a COUNT(*), and the FACT1_SUBSET.DOC_ID column is added to the
GROUP BY list. The CASE expression is removed from the select list
altogether.
An estimate of the size of the materialized view was well within the limit of an
order of magnitude as compared to the base table. After creating this
materialized view, and populating it, we ran an EXPLAIN of Query 6 to confirm
that it was being routed to AST7 as shown in Example 2-49.
Example 2-48 Materialized view AST7
CREATE SUMMARY TABLE AST7 AS
(
SELECT ROOT_LEVEL, MESH.FIRST_LEVEL, AUTHOR_NAME, DOC_ID, COUNT(*) AS C
FROM FACT1_SUBSET, MESH, AUTHOR
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID
GROUP BY FIRST_LEVEL, ROOT_LEVEL, AUTHOR_NAME, DOC_ID
)
DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1
86 High-Function Business Intelligence in e-business
Example 2-49 EXPLAIN of Query 8
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-08-29-13.27.27.781000
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 7.478784e-007
Comm Speed: 0
Buffer Pool size: 5256
Sort Heap size: 20000
Database Heap size: 600
Lock List size: 50
Maximum Lock List: 22
Average Applications: 1
Locks Available: 1243
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT AUTHOR.AUTHOR_NAME, doc_id,
case
when fact1_subset.doc_id=1000
THEN count(*)
ELSE NULL END
FROM FACT1_SUBSET, MESH, AUTHOR
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.