kl800.com省心范文网

Chapter 5


Min Wang Tongji University mailto: min.wang@tongji.edu.cn

? ?

节约DASD 节约CPU

? ? ? ? ?

Materialized Query Tables Multi-row fetch and insert Clone Tables Index Compress Not Logged Table Spaces

固化查询表 Materialized Query Tables

?

固化查询表(MQT)用以保存从其他表中查询出的 数据
? 大量预处理(计算,联接)以节省查询开销 ? 源表可以是基表、视图、表达式等

?

查询请求发出后,由优化器决定是否采用MQT来改 写查询 也可以直接使用SQL从MQT中查询 使用MQT可以大大提高查询效率

?

?

?

与视图相比
? 保存在DASD中,包含真正数据 ? 往往并非直接查询MQT, 而是优化器选择MQT来改写查询

?

与INDEX相比
? ? ? ? 对用于应用透明 MQT可以和多个表关联 MQT并非实时更新 MQT往往包含了从基表而来的计算结果

Define a MQT

create table MQT1 as (select S.city_id, sum(F.sale_exp) as sm_se, sum(F.sale_act)as sm_sa from Store S, Fact F where S.storeid = F.storeid group by S.cityid ) data initially deferred refresh deferred maintained by system enable query optimization;

Specify a full select associated with base table(s) Specify the mechanisms that are used to refresh the MQT Enable/disable a MQT for automatic query rewrite

?

DATA INITIALLY DEFERRED

? MQT will not be populated at creation ? use REFRESH TABLE to populate MQT ? use INSERT statement to insert data into user-maintained MQT ? data in the MQT is not refreshed immediately when its base ? tables are updated

?

REFRESH DEFERRED

?

can be refreshed any time using the REFRESH TABLE statement

?
?

MAINTAINED BY SYSTEM / USER ENABLE / DISABLE QUERY OPTIMIZATION

?

使用ALTER TABLE SQL 语句将一个已经存在的基表改为 MQT
? ? ? ? specify a fullselect associated with a table specify one of the mechanisms to refresh the table enable/disable a MQT for automatic query rewrite switch between system-maintained and user-maintained types

?

例子:
ALTER TABLE TRANSCOUNT ADD MATERIALIZED QUERY AS SELECT ACCTID, LOCID, YEAR, COUNT(*) as cnt FROM TRANS GROUP BY ACCTID, LOCID, YEAR ) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER;

?

系统维护的 MQT ( 默认)

? use the new REFRESH TABLE statement
? delete all the rows in the MQT ? executes the fullselect associated with the MQT to recalculate the data ? inserts the calculated result into the MQT ? updates the catalog for the refresh timestamp and cardinality of the MQT

? can not be updated by load, insert, update and delete
?

用户维护的MQT

? can be populated by REFRESH TABLE statement ? can be updated by load, insert, update and delete

1. 基表数据改变

2. REFRESH TABLE 更新基表上建立的 MQT

3. 关于MQT的统 计信息在Catalog 中得到更新

?

特殊寄存器
? Subsystem wise parameters ? Can be referenced in SQL

?

动态SQL用到的特殊寄存器
? CURRENT REFRESH AGE ? CURRENT MAINTAINED TABLE TYPES

?

Initial values specified on installation panel DSNTIP4

?

CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

? The value identifies the types of MQT that can be considered in query rewrite
? Value 'SYSTEM' means all system-maintained, query optimization enabled MQTs (Default) ? Value 'USER' means all user-maintained, query optimization enabled MQTs ? Value 'ALL' means all query optimization enabled MQTs

?

CURRENT REFRESH AGE

? The refresh age of a MQT is the timestamp duration between the current timestamp and the timestamp of the last REFRESH TABLE statement for the MQT ? It control if a MQT can be considered in query rewrite
? Value '0' means no MQTs are considered in query rewrite (Default) ? Value 'ANY' means all MQTs are considered in rewrite

? ? ? ?

?

决定是否可用MQT改写查询 寻找是否有可用的MQT 使用现成的MQT改写查询 比较改写前后的查询开销,采用开销较小的查询路 径方案 如果采用的plan是改写后的Query
? PLAN_TABLE will show the MQT used and its access path ? TABLE_TYPE column show value 'M' for a MQT

select S.city_id, sum(F.sale_amt) from Fact F, Store S where F.store_id = S.store_id group by S.city_id

select S.city_id, sum(F.sale_amt) as sm_sale from Fact F, Store S where F.store_id = S.store_id group by S.city_id

(other MQT control statements)
select A.cityid, sm_sale from MQT1 A

? ? ? ? ?

最多可以达到100倍的查询效率 如果基表删除,那么MQT也连带删除 在segmented表空间中定义MQT, 以提高刷新效率 在MQT中不允许使用主键,unique index和triggers Application 需要在采用少量的,通用的MQT表,和 大量的,专门的MQT表中做选择。
? Trade off between query performance and MQT maintenance

克隆表 Clone Tables

?

可以快速更新表数据,而无需重命名/rebind
? A capability to support online load replace

?

使用CREATE TABLE语句来创建克隆表
? All indexes are also cloned ? Table and Index data are not copied ? Base and Clone tables share the same table space and index names ? Underlying data sets are differentiated by a dataset instance number

?

Must be a Universal Table Space, DB2 managed and the only table in the table space Application is unaware of the clone table, no application changes are necessary. Can not clone a table that has RI. Can not make schema changes to a table that has a clone. No clone tables on MQTs. Can not clone the DB2 Catalog tables.

?

?
?

? ?

?

Statements
ALTER TABLE DVDB2SP.USER_GROUP_CONNEC2 ADD CLONE USER_GROUP_CONNECT_CLN;

?

Table is created in the same table space as the base table.
Base table: DA296.DSNDBC.VRADB002.VRATS901.I0001.A001 DA296.DSNDBD.VRADB002.VRATS901.I0001.A001 Clone table: DA296.DSNDBC.VRADB002.VRATS901.I0002.A001 DA296.DSNDBD.VRADB002.VRATS901.I0002.A001

?

–DIS DB(VRADB002) SPACE(*)

?

You can not make schema changes:

ALTER TABLE DVDB2SP.USER_GROUP_CONNEC2 ADD TEST_COL CHAR(1);

DSNT408I SQLCODE = -148, ERROR: THE SOURCE TABLE DVDB2SP.USER_GROUP_CONNEC2 CANNOT BE ALTERED. REASON 11 DSNT418I SQLSTATE = 42809 SQLSTATE RETURN CODE
?

Additional columns added to DB2 Catalog tables:
SYSTABLESPACE Instance Clone SYSCOPY Instance

?

Clone table is identical to the base table.
? Created in the same tablespace. ? Created with the same indexes.

?

Clone table created as empty.
? Table must be populated.

?

Grant access on the clone table.
? Security separate from base table.

?

Utilities process against the clone table.

? Can not run RUNSTATS on a clone table. ? Consider running RUNSTATS after EXCHANGE DATA if there is significant differences in the data between the two tables.

索引压缩 Index Compress

?

V9以后,索引必须关联8K, 16K, or 32K 的缓冲池.
? Indexes in DB2 V8 are 4K BP. ? SQL code -676 if incorrect page size.

? ?

DSN1COMP utility 可用于压缩索引. ALTER INDEX COMPRESS YES.
? Index placed in RBDP (rebuild pending).

?

New column in SYSIBM.SYSINDEXES.
? COMPRESS … Y/N.

?

No compression dictionary for index compression.

?

?

将4K index 转换为 8K, 16K, 或 32K的page size Turn compression on 索引压缩,或者重新关联缓冲 池,都会将索引置为“Rebuild Pending (RBDP)” 状态 需要手动从RBDP返回

STOP INDEX

ALTER INDEX …BP8K

?

ALTER INDEX…COMPRESS YES

?

REBUILD INDEX

Index Compression Process

?

8 K Page Buffer Size yields a

? 51 % Reduction in Index Leaf Page Space The Resulting Index would have approximately ? 49 % of the original index's Leaf Page Space No Bufferpool Space would be unused

?

16 K Page Buffer Size yields a

? 71 % Reduction in Index Leaf Page Space The Resulting Index would have approximately ? 29 % of the original index's Leaf Page Space ? 13 % of Buffer Pool space would be unused to ensure keys fit into compressed buffers

Comes at a very trivial CPU consumption overhead at execution

无日志表空间 Not logged Table Spaces

?

DB2 9 New Function Mode only/Default is logged/DBADM authority required Undo/Redo log records are not maintained Indexes inherit the logging attribute from the base table space Possible uses:
? D/W for loading summarized tables (MQTs). ? Loading data into a table and making changes to the data with SQL before using the table. ? Changing a lot of data in a controlled process such as for year-end processing.

? ?

?

?

使用之前,务必再三考虑! 无日志期间发生的任何错误都是无法恢复的. 日志的开销很小,一般而言,无需为了节省日志开 销而牺牲恢复性

?

?

?

创建
? CREATE TABLESPACE…NOT LOGGED ? ALTER TABLESPACE…NOT LOGGED

?

catalog 中log/unlog的标志位
SYSIBM.SYSCOPY ? ICTYPE = A ? STYPE = O, logging attribute altered to NOT LOGGED ? STYPE = L, logging attribute altered to LOGGED SYSIBM.SYSTABLESPACE ? LOG = YES, logging attribute is Logged ? LOG = NO, logging attribute is Not Logged

多行查询/插入 Multi-row Fetch and Insert

?

多行查询
? A single FETCH can retrieve multiple rows of a result set

?

多行插入
? A single SQL statement can insert one or more rows into a table or a view

?

Both can be implemented as either static or dynamic SQL
好处
? ? ? ? Performance is improved by eliminating multiple trips between application and database Reduced network traffic in distributed access Enhance usability and power of SQL

?

?

Multi-row

? Max 32767 rows on single fetch/insert statememt ? C, C++, COBOL, PL/I ? An array in which each element of the array corresponds to a value for a column (a HVA per column) ? Defined by statements of the host language

?

Host Variable Array (HVA)

?

Rowset

? A set of rows that is retrieved through a multi-row fetch
? Cursor which one or more rows can be returned for a single Fetch statement as a rowset ? Each row can be reference in subsequent positioned update and delete statements

?

Rowset Cursor

Up to 30% CPU time reduction due to less API overhead


赞助商链接

ACCA chapter5

ACCA chapter5_金融/投资_经管营销_专业资料。1. Explain why a country’s ...5. The nominal tariff rates on the 10 imports into Country A, as well ...

Chapter 5 Test(课堂用-答案版)

Chapter 5 Test(课堂用-答案版)_经济学_高等教育_教育专区。Chapter 5 Elasticity and its Application MULTIPLE CHOICE 1. When the price of an eBook is $...

国际金融英文版试题chapter5

国际金融英文版试题chapter5_经济学_高等教育_教育专区 暂无评价|0人阅读|0次下载|举报文档国际金融英文版试题chapter5_经济学_高等教育_教育专区。托马斯版 ...

ENetwork Chapter 5 - CCNA Exploration 网络基础知识 ...

ENetwork Chapter 5 - CCNA Exploration 网络基础知识 (版本 4.0)_IT/计算机_专业资料。CCNA Exploration 网络基础知识 (版本 4.0)本...

投资学 chapter 5

投资学 chapter 5_经济学_高等教育_教育专区 暂无评价|0人阅读|0次下载|举报文档 投资学 chapter 5_经济学_高等教育_教育专区。Sample question: You have the...

Chapter 5-Q&A

Operating Systems Chapter 5 – Concurrency: Mutual Exclusion and Synchronization Chapter 5 – Concurrency: Mutual Exclusion and Synchronization True / False ...

Chapter 5 summary

Chapter 5 summary_金融/投资_经管营销_专业资料。global market Chapter 5 summary 1.The FX market is the largest and most active financial market in the ...

chapter 5 短语结构树

chapter 5 短语结构树_计算机软件及应用_IT/计算机_专业资料 暂无评价|0人阅读|0次下载|举报文档 chapter 5 短语结构树_计算机软件及应用_IT/计算机_专业资料。...

词汇学Chapter5归纳整理

“the quality or an example of” rapid——the qualit or state of being rapid 5.Noun/adjective suffixes: -ese The suffix -ese has two meanings: a,...

Chapter 5 Semantics课后答案

Chapter 5 Semantics Preview: The Study of Meaning Semantics: The meaning of words: Lexical semantics The meaning of sentences: Propositional meaning, ...