74 High-Function Business Intelligence in e-business
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-08-26-16.47.17.125000
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 MESH.SECOND_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND MESH.ROOT_LEVEL='Anatomy' AND
MESH.FIRST_LEVEL='Body Regions'
GROUP BY MESH.SECOND_LEVEL
Optimized Statement:
-------------------
SELECT Q3.$C1 AS "SECOND_LEVEL", Q3.$C0 AS "COUNTS"
FROM
(SELECT COUNT(Q2.$C1), Q2.$C0
FROM
(SELECT Q1.SECOND_LEVEL, Q1.COUNTS
FROM DB2ADMIN.AST3 AS Q1
WHERE (Q1.FIRST_LEVEL = 'Body Regions') AND (Q1.ROOT_LEVEL =
Chapter 2. DB2 UDB’s materialized views 75
'Anatomy')) AS Q2
GROUP BY Q2.$C0) AS Q3
Query 2:
Note the following generalization of local predicates for this query:
? The predicate on column MESH.ROOT_LEVEL is added to the GROUP BY
list, and removed from the predicate. MESH.ROOT_LEVEL is not a nullable
column.
? The COUNT(DISTINCT FACT1_SUBSET.DOC_ID) select list item is replaced
by a column DOC_ID, and an addition of column DOC_ID to the GROUP BY
list.
We see that a materialized view from the above generalization is a coarser
granular version of AST3, since it only groups on MESH.ROOT_LEVEL. We can
therefore potentially reuse AST3.
We ran an EXPLAIN of Query 2 to confirm that it was being routed to AST3 as
shown in Example 2-40.
Example 2-40 EXPLAIN of Query 2
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-08-26-16.58.54.265000
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
76 High-Function Business Intelligence in e-business
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 MESH.FIRST_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND MESH.ROOT_LEVEL='Chemicals and
Drugs'
GROUP BY MESH.FIRST_LEVEL
Optimized Statement:
-------------------
SELECT Q3.$C1 AS "FIRST_LEVEL", Q3.$C0 AS "COUNTS"
FROM
(SELECT COUNT(Q2.$C1), Q2.$C0
FROM
(SELECT Q1.FIRST_LEVEL, Q1.COUNTS
FROM DB2ADMIN.AST3 AS Q1
WHERE (Q1.ROOT_LEVEL = 'Chemicals and Drugs')) AS Q2
GROUP BY Q2.$C0) AS Q3
Query 3:
Note the following generalization of local predicates for this query:
? The COUNT(DISTINCT FACT1_SUBSET.DOC_ID) select list item is replaced
by a column DOC_ID, and an addition of column DOC_ID to the GROUP BY
list.
? There is no local predicate.
We see that a materialized view from the above generalization is a coarser
granular version of AST3, since it only groups on MESH.ROOT_LEVEL. We can
therefore potentially reuse AST3.
We ran an EXPLAIN of Query 2 to confirm that it was being routed to AST3 as
shown in Example 2-41.
Chapter 2. DB2 UDB’s materialized views 77
Example 2-41 EXPLAIN of Query 3
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-08-26-17.07.12.593002
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 MESH.ROOT_LEVEL, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS
FROM FACT1_SUBSET, MESH
WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID
GROUP BY MESH.ROOT_LEVEL
Optimized Statement:
78 High-Function Business Intelligence in e-business
-------------------
SELECT Q3.$C1 AS "ROOT_LEVEL", Q3.$C0 AS "COUNTS"
FROM
(SELECT COUNT(Q2.$C1), Q2.$C0
FROM
(SELECT Q1.ROOT_LEVEL, Q1.COUNTS
FROM DB2ADMIN.AST3 AS Q1) AS Q2
GROUP BY Q2.$C0) AS Q3
Query 4:
The materialized view for this query looks like AST5 in Example 2-42. 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.
? The COUNT(DISTINCT FACT1_SUBSET.DOC_ID) select list item is replaced
by a column DOC_ID, and an addition of column DOC_ID to the GROUP BY
list.
Example 2-42 Materialized view AST5
CREATE SUMMARY TABLE AST5 AS
(
SELECT ROOT_LEVEL, MESH.FIRST_LEVEL, AUTHOR_NAME, DOC_ID
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
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. We ran an EXPLAIN of Query
4 to confirm that it was being routed to AST3 as shown in Example 2-43.
Example 2-43 EXPLAIN of Query 4
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2002-08-27-15.50.44.109000
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.