Page 95

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Previous Chapter

Page 90

Next Chapter

Page 100

Chapter 2. DB2 UDB’s materialized views 79

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, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH, AUTHOR

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND MESH.ROOT_LEVEL='Anatomy'

AND MESH.FIRST_LEVEL='Animal Structures'

GROUP BY AUTHOR.AUTHOR_NAME

Optimized Statement:

-------------------

SELECT Q3.$C1 AS "AUTHOR_NAME", Q3.$C0 AS "COUNTS"

FROM

(SELECT COUNT(Q2.$C1), Q2.$C0

FROM

(SELECT Q1.AUTHOR_NAME, Q1.DOC_ID

FROM DB2ADMIN.AST5 AS Q1

WHERE (Q1.FIRST_LEVEL = 'Animal Structures') AND (Q1.ROOT_LEVEL =

'Anatomy')) AS Q2

80 High-Function Business Intelligence in e-business

GROUP BY Q2.$C0) AS Q3

Query 5:

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(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 AST5, since it only groups on MESH.ROOT_LEVEL and

MESH.FIRST_LEVEL. We can therefore potentially reuse AST5. We ran an

EXPLAIN of Query 4 to confirm that it was being routed to AST5 as shown in

Example 2-44.

Example 2-44 EXPLAIN of Query 5

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.0

SOURCE_NAME: SQLC2D01

SOURCE_SCHEMA: NULLID

EXPLAIN_TIME: 2002-08-27-15.58.30.937000

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

Chapter 2. DB2 UDB’s materialized views 81

---------------- STATEMENT 1 SECTION 203 ----------------

QUERYNO: 1

QUERYTAG:

Statement Type: Select

Updatable: No

Deletable: No

Query Degree: 1

Original Statement:

------------------

SELECT AUTHOR.AUTHOR_NAME, COUNT(DISTINCT FACT1_SUBSET.DOC_ID) COUNTS

FROM FACT1_SUBSET, MESH, AUTHOR

WHERE FACT1_SUBSET.MESH_ID=MESH.MESH_ID AND

FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND MESH.ROOT_LEVEL='Anatomy'

AND MESH.FIRST_LEVEL IN ('Body Regions','Cells')

GROUP BY AUTHOR.AUTHOR_NAME

Optimized Statement:

-------------------

SELECT Q3.$C1 AS "AUTHOR_NAME", Q3.$C0 AS "COUNTS"

FROM

(SELECT COUNT(Q2.$C1), Q2.$C0

FROM

(SELECT Q1.AUTHOR_NAME, Q1.DOC_ID

FROM DB2ADMIN.AST5 AS Q1

WHERE (Q1.ROOT_LEVEL = 'Anatomy') AND Q1.FIRST_LEVEL IN ('Body Regions',

'Cells')) AS Q2

GROUP BY Q2.$C0) AS Q3

Query 6:

The materialized view for this query looks like AST6 in Example 2-45. Note the

following generalization of local predicates for this query:

? The YEAR, MONTH, ROOT_LEVEL, FIRST_LEVEL, and COUNTRY_NAME

columns in the GROUP BY list are all nullable, and therefore require the

GROUPING function to be defined in the select list of the materialized view.

Note: As explained in topic 2 on page 46, the GROUPING function must

be defined for all nullable columns when super aggregates (ROLLUP,

CUBE and grouping sets) are involved.

82 High-Function Business Intelligence in e-business

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 AST6 as shown in Example 2-46.

Example 2-45 Materialized view AST6

CREATE SUMMARY TABLE AST6 AS

(

SELECT COUNTRY_NAME, COUNT(*) AS COUNT, YEAR, MONTH, ROOT_LEVEL,

FIRST_LEVEL, GROUPING(COUNTRY_NAME) AS GPCNAME, GROUPING(YEAR) AS GPYEAR,

GROUPING(MONTH) AS GPMONTH, GROUPING(ROOT_LEVEL) AS GPRLEVEL,

GROUPING(FIRST_LEVEL) AS GPFLEVEL

FROM FACT1_SUBSET, MESH, TIME, COUNTRY

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, MONTH), ROLLUP(ROOT_LEVEL, FIRST_LEVEL), COUNTRY_NAME

)

DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1

Example 2-46 EXPLAIN of Query 6

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.0

SOURCE_NAME: SQLC2D01

SOURCE_SCHEMA: NULLID

EXPLAIN_TIME: 2002-08-29-13.38.21.687000

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

Chapter 2. DB2 UDB’s materialized views 83

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, month, cast(root_level as varchar(20)) as root_level,

cast(first_level as varchar(20)) as first_level

from fact1_subset, mesh, time, country

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, month), rollup(root_level, first_level),

country_name) select *

from dt

order by country_name, year, month, root_level, first_level

Optimized Statement:

-------------------

SELECT Q1.COUNTRY_NAME AS "COUNTRY_NAME", Q1.COUNT AS "COUNT", Q1.YEAR AS

"YEAR", Q1.MONTH AS "MONTH", Q1.ROOT_LEVEL AS "ROOT_LEVEL",

Q1.FIRST_LEVEL AS "FIRST_LEVEL"

FROM DB2ADMIN.AST6 AS Q1

ORDER BY Q1.COUNTRY_NAME, Q1.YEAR, Q1.MONTH, Q1.ROOT_LEVEL, Q1.FIRST_LEVEL

Query 7:

Note the following generalization of local predicates for this query:

? The YEAR, ROOT_LEVEL, and COUNTRY_NAME columns in the GROUP

BY list are all nullable, and therefore require the GROUPING function to be

defined in the select list of the materialized view.

We see that a materialized view from the above generalization is a coarser

granular version of AST6, since it only groups on three of the five columns

defined in AST6. We can therefore potentially reuse AST6.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Page 95

Create new playlist

Sign In

Sign Up

Table of Contents for
Page 95