大多數(shù)主流關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，例如 IBM DB2、Oracle 和 Microsoft® SQL Server，都依賴于基于成本的優(yōu)化器設(shè)計(jì)，來(lái)在數(shù)據(jù)庫(kù)服務(wù)器環(huán)境中的一組經(jīng)常變化的條件（包括變化的查詢特征和變化的數(shù)據(jù)）的影響下，從很多可能的計(jì)劃中選擇一個(gè)最佳 SQL 執(zhí)行計(jì)劃。具體而言，DB2 SQL 優(yōu)化決定受系統(tǒng)配置（I/O 存儲(chǔ)特征、CPU 并行性和速度、緩沖池和排序堆設(shè)置、通信帶寬）、模式（索引、約束）、DB2 注冊(cè)表變量、DB2 優(yōu)化級(jí)別和統(tǒng)計(jì)信息（關(guān)于表、列和索引的統(tǒng)計(jì)信息）的影響。這么多復(fù)雜的因素，再加上數(shù)據(jù)本身的動(dòng)態(tài)性，使得最佳計(jì)劃的評(píng)估對(duì)于任何數(shù)據(jù)庫(kù)系統(tǒng)而言通常都是一個(gè)復(fù)雜的過(guò)程。

考慮到生成最佳 SQL 執(zhí)行計(jì)劃是一項(xiàng)不簡(jiǎn)單的任務(wù)，DB2 對(duì)其已臻成熟的成本模型繼續(xù)進(jìn)行了改進(jìn)，并加入了新的功能，以提供更好的信息來(lái)幫助成本模型做出決定。統(tǒng)計(jì)視圖是一種強(qiáng)大的、新型的統(tǒng)計(jì)，它可以表示復(fù)雜謂詞或表之間的關(guān)系。REOPT 綁定選項(xiàng)將查詢優(yōu)化推遲到 OPEN 時(shí)有可用輸入變量的時(shí)候。然后，優(yōu)化器可以將輸入變量的值與編目統(tǒng)計(jì)進(jìn)行比較，并為謂詞計(jì)算出一個(gè)更好的選擇估計(jì)。統(tǒng)計(jì)視圖和 REOPT 都使優(yōu)化器可以計(jì)算出更精確的基數(shù)估計(jì)，而后選擇一個(gè)最佳查詢執(zhí)行計(jì)劃。對(duì)于優(yōu)化器不能選擇最佳查詢執(zhí)行計(jì)劃的例外情況，DB2 已經(jīng)增加了諸如 SELECTIVITY 子句和優(yōu)化指南之類的特性。

在本文的討論中，我們來(lái)看看優(yōu)化指南和統(tǒng)計(jì)視圖這兩個(gè)最新的增強(qiáng)。通過(guò)本文，您可以了解這些增強(qiáng)的作用是什么，以及在某些情況下，在非數(shù)據(jù)分區(qū)（non-DPF）和數(shù)據(jù)分區(qū)（DPF）環(huán)境中，如何在應(yīng)用程序內(nèi)充分利用它們。

DB2優(yōu)化概要文件和嵌入式指南

Version 8 FP9, DB2 for Linux, UNIX, and Windows 中包括優(yōu)化概要文件功能，該功能將一個(gè)指南傳遞給優(yōu)化器，用于指導(dǎo)優(yōu)化器為 SQL 查詢生成所需的執(zhí)行計(jì)劃，以覆蓋默認(rèn)的成本模型。

很多人都曾在應(yīng)用程序中碰到這樣的情況：大多數(shù)查詢工作負(fù)載都經(jīng)過(guò)了適當(dāng)?shù)恼{(diào)優(yōu)，并取得了較好的性能，但是，隨著用戶期望的增長(zhǎng)，加上系統(tǒng)的復(fù)雜性和多樣性，仍然有少數(shù) SQL 語(yǔ)句無(wú)法通過(guò)調(diào)優(yōu)取得預(yù)期的性能。雖然人們已經(jīng)盡了最大的努力力圖通過(guò)改變數(shù)據(jù)庫(kù)（例如使用索引建議器或者其他方法來(lái)改進(jìn)索引、更新統(tǒng)計(jì)信息、改善數(shù)據(jù)群集及更改參數(shù)）來(lái)調(diào)優(yōu) SQL 語(yǔ)句，但是問(wèn)題仍然存在。有時(shí)候，我們希望更直接地影響優(yōu)化器，同時(shí)盡量避免更改應(yīng)用程序。

這時(shí)候可以考慮使用優(yōu)化指南。然而需要注意的是，先進(jìn)的優(yōu)化器在生成一個(gè)特定的訪問(wèn)計(jì)劃時(shí)，必然有其原因，所以在應(yīng)用指南之前，務(wù)必理解是什么原因?qū)е虏樵兊男阅艿拖隆?yōu)化指南使用起來(lái)并不難，但更具有挑戰(zhàn)性的任務(wù)是根據(jù)給定的數(shù)據(jù)庫(kù)環(huán)境判斷 SQL 語(yǔ)句的問(wèn)題出在哪里，并選擇適當(dāng)?shù)闹改霞右詰?yīng)用。

優(yōu)化概要文件的工作原理

首先選擇一組您想要影響其訪問(wèn)計(jì)劃的查詢。然后，將這些查詢和一些適當(dāng)?shù)闹改戏诺揭粋€(gè) XML 優(yōu)化概要文件中。為了通過(guò)驗(yàn)證，這個(gè)優(yōu)化概要文件必須遵從優(yōu)化指南 XML 模式，并由一些區(qū)段組成，如清單 1 所示。

清單1.XML 優(yōu)化概要文件

XML 優(yōu)化概要文件以 OPTPROFILE 區(qū)段開始，該區(qū)段表明版本屬性。這個(gè)全局區(qū)段將規(guī)則全局地應(yīng)用到所有 SQL 語(yǔ)句上。例如，可以指定使用哪個(gè) REOPT 選項(xiàng)，使用哪個(gè) MQT 表，或者使用什么樣的查詢優(yōu)化。statement profile 區(qū)段則表明將哪些特定的規(guī)則應(yīng)用于 STMTKEY 元素中的 SQL 語(yǔ)句上。

如果有問(wèn)題的 SQL 查詢不容易訪問(wèn)到，那么借助 XML 優(yōu)化概要文件可以帶來(lái)很大的方便。例如，SQL 查詢可能處在一個(gè)應(yīng)用程序中，而這個(gè)應(yīng)用程序是不能更改的。在這種情況下，可以使用概要文件，在查詢文本成功匹配之后，通過(guò)觸發(fā)與查詢相關(guān)聯(lián)的指南來(lái)影響查詢行為。該環(huán)境中的所有 SQL 語(yǔ)句將嘗試從活動(dòng)的優(yōu)化概要文件中查找匹配項(xiàng)，而這種匹配是高效率、低開銷的。

如何啟用優(yōu)化概要文件

一個(gè)數(shù)據(jù)庫(kù)中可以有很多個(gè)優(yōu)化概要文件，但是在實(shí)際情況中，更靈活的做法是創(chuàng)建一個(gè)主優(yōu)化概要文件，將所有規(guī)則（statement profile）組織在一起，然后只需激活此概要文件，根據(jù)應(yīng)用程序環(huán)境的不同，可以選擇以下幾種方法之一來(lái)激活概要文件。另外還需要將 DB2_OPTPROFILE 注冊(cè)表變量設(shè)置為 YES。

1.在CLP環(huán)境中：

使用 “SET CURRENT OPTIMIZATION PROFILE=KCHEN.PROF1” 語(yǔ)句在會(huì)話級(jí)將概要文件與所有 SQL 語(yǔ)句關(guān)聯(lián)，直到連接重置或者概要文件重置。這條語(yǔ)句還可以嵌入到應(yīng)用程序中。

2.對(duì)于 CLI 應(yīng)用程序或使用舊的 JDBC 驅(qū)動(dòng)程序的JDBC應(yīng)用程序：

在db2cli.ini配置文件中設(shè)置 CURRENTOPTIMIZATIONPROFILE 關(guān)鍵字來(lái)關(guān)聯(lián)概要文件。對(duì)于 SAMPLE 數(shù)據(jù)庫(kù)，這個(gè)關(guān)鍵字是在 data source 區(qū)段中設(shè)置的。

[SAMPLE]

CURRENTOPTIMIZATIONPROFILE=KCHEN.PROF1

經(jīng)過(guò)這樣設(shè)置后，應(yīng)用程序執(zhí)行中的 SQL 將嘗試與 KCHEN.PROF1 中的 SQL 語(yǔ)句進(jìn)行匹配，以查找指定的規(guī)則，這些規(guī)則將覆蓋執(zhí)行環(huán)境中常規(guī)的優(yōu)化。

3.對(duì)于使用JCC Universal Driver的JDBC應(yīng)用程序：

采用 JCC Universal Driver 的 JDBC 應(yīng)用程序并不使用 DB2 CLI 層。雖然可以將一個(gè)系統(tǒng)包和綁定文件與動(dòng)態(tài) SQL 執(zhí)行相關(guān)聯(lián)，但最好的做法是將 “SET CURRENT OPTIMIZATION PROFILE” 語(yǔ)句嵌入在 Java™ 應(yīng)用程序中，在會(huì)話級(jí)關(guān)聯(lián)概要文件。

4.對(duì)于 SQL PL 過(guò)程：

在創(chuàng)建 SQL PL 過(guò)程之前，使用 SET_ROUTINE_OPTS 過(guò)程調(diào)用將概要文件的名稱與 DB2 V8 FP13+ 或 DB2 V9 FP1+ 中特定的 SQL PL 相關(guān)聯(lián)。

CALL SYSPROC.SET_ROUTINE_OPTS('OPTPROFILE KCHEN.PROF1')

SQL PL 過(guò)程包含的 SQL 語(yǔ)句具有一些執(zhí)行屬性，例如隔離級(jí)別或優(yōu)化級(jí)別，這些屬性只能通過(guò) DB2_SQLROUTINE_PREOPTS 注冊(cè)表變量來(lái)覆蓋。也可以用 SYSPROC.SET_ROUTINE_OPTS 過(guò)程覆蓋該選項(xiàng)。要激活一個(gè)概要文件，可以使用該存儲(chǔ)過(guò)程來(lái)關(guān)聯(lián)指南。

5.對(duì)于 C/C++ 應(yīng)用程序中的嵌入式SQL：

對(duì)于嵌入式 C/C++ 應(yīng)用程序，使用 OPTPROFILE 綁定選項(xiàng)。 嵌入式 SQC 程序需要使用 PREP 命令來(lái)編譯，該命令將創(chuàng)建綁定文件。這個(gè)綁定文件需要通過(guò) OPTPROFILE 選項(xiàng)綁定到數(shù)據(jù)庫(kù)，例如：

bind prog1.bnd OPTPROFILE KCHEN.PROF1

6.對(duì)于含嵌入式靜態(tài) SQL 語(yǔ)句的 SQLJ 應(yīng)用程序：

在定制階段使用 BINDOPTIONS 參數(shù)關(guān)聯(lián)概要文件。這個(gè)靜態(tài) SQLJ 程序 prog1 被按如下所示進(jìn)行翻譯和編譯：

sqlj prog1.sqlj

db2sqljcustomize -url jdbc:db2://SERVER:PORT/SAMPLE -user USER -password PASSWORD

-bindoptions 'OPTPROFILE KCHEN.PROF1' -storebindoptions prog1_SJProfile0

所有使用舊的 JDBC 驅(qū)動(dòng)程序的 JDBC 程序，都將使用 db2cli.ini 中的設(shè)置。使用 Universal JDBC 驅(qū)動(dòng)程序的 JDBC 程序?qū)儆谏鲜龅牡?3 類情況。需要注意的是，由于 SQLJ 為 SELECT SQL 語(yǔ)句生成一個(gè)隱式的 “DECLARE CURSOR” 子句，因此，為了使指南得到應(yīng)用，優(yōu)化概要文件除了包括 SELECT 語(yǔ)句外，還需要包括 “DECLARE CURSOR” 子句。

當(dāng)應(yīng)用程序執(zhí)行時(shí)，將 SQL 與活動(dòng)的概要文件中的指南相比較。如果存在一個(gè)匹配的 STMTKEY ，指南就會(huì)開始起作用；反之，假如指南被認(rèn)為是不適用的或無(wú)效的，那么就會(huì)返回一個(gè) rc = 13 的 SQL0437W。DB2 Explain 工具對(duì)于幫助確定指南是否被選擇非常有用。Explain 的輸出會(huì)指明優(yōu)化概要文件的名稱和有效的指南。概要文件中的指南通常覆蓋用于應(yīng)用程序設(shè)置的常規(guī)優(yōu)化，從而使概要文件能夠更好地控制計(jì)劃評(píng)估。

優(yōu)化指南的例子

優(yōu)化概要文件中的任何指南都必須遵從 DB2 提供的 XML 模式。如果沒有正確地指定指南，那么指南將無(wú)效，并且在大多數(shù)情況下，將返回 rc = 13 的 SQL0437W。優(yōu)化概要文件存儲(chǔ)在一個(gè)名為 SYSTOOLS.OPT_PROFILE 表中。如果從這個(gè)表中更新或刪除一個(gè)指南，那么需要通過(guò)發(fā)出 FLUSH OPTIMIZATION PROFILE CACHE 語(yǔ)句更新緩存，使之可以被使用。需要注意的是，SQL 語(yǔ)句測(cè)試匹配是大小寫敏感的，但在嘗試匹配之前，DB2 將去除冗余空格和控制字符。

下面的例子演示了優(yōu)化概要文件在 3 類情況下的使用，即常規(guī)優(yōu)化、查詢重寫和計(jì)劃優(yōu)化。

例子1: 總是使用索引 T1X （計(jì)劃優(yōu)化）

假設(shè)在表 T1 的 (c2, c1) 列上有一個(gè)索引 T1X。根據(jù)優(yōu)化器的成本計(jì)算，對(duì)于以下查詢，會(huì)導(dǎo)致一個(gè)表掃描。下面的代碼展示了如何強(qiáng)制使用一個(gè)索引。

例子2: 總是使用 REOPT（常規(guī)優(yōu)化）

可以使用 REOPT 指南，將查詢優(yōu)化推遲到運(yùn)行時(shí)輸入變量已知的時(shí)候。可能的選項(xiàng)有 ONCE、ALWAYS 或 NONE。

例子3:只使用DB2 V9 中的Optimization Level 0（常規(guī)優(yōu)化）

通常，對(duì)于一個(gè)應(yīng)用程序而言，優(yōu)化級(jí)別是固定的，但是如果要使一條特定的 SQL 語(yǔ)句在一個(gè)不同的優(yōu)化級(jí)別上執(zhí)行，那么可以創(chuàng)建以下優(yōu)化指南：

例子4:只使用 DB2 V9 中的Runtime degree ANY（常規(guī)優(yōu)化）

可以有很多方法來(lái)修改內(nèi)部分區(qū)的查詢的運(yùn)行時(shí)等級(jí)。下面的代碼展示了優(yōu)化指南如何為查詢指定運(yùn)行時(shí)等級(jí)以及如何影響查詢的執(zhí)行。

例子5: INLIST 改為嵌套循環(huán)連接（查詢重寫）

將值列表（inlist）改為使用 GENROW 函數(shù)非常有效，可以提高查詢的性能。在這個(gè)例子中，值列表被放在內(nèi)存中的一個(gè)表中。

P.P_SIZE, P.P_TYPE, S.S_NATION

FROM KCHEN.PARTS P, KCHEN.SUPPLIERS S, KCHEN.PARTSUPP PS

WHERE P_PARTKEY = PS.PS_PARTKEY AND

S.S_SUPPKEY = PS.PS_SUPPKEY AND

P.P_TYPE IN ('BRASS', 'BRONZE') AND

P.P_SIZE IN (31, 31, 33, 34) AND

S.S_NATION = 'PERU']]>

例子6: 子查詢改為連接（查詢重寫）

在這個(gè)例子中，在查詢重寫期間，通過(guò)使用帶 ENABLE 屬性的 SUBQ2JOIN，將一個(gè)子查詢轉(zhuǎn)換成一個(gè)連接，以便更好地對(duì)其進(jìn)行優(yōu)化。

FROM KCHEN.PARTSUPP PS, KCHEN.LINEITEM

WHERE PS.PS_PARTKEY = L_PARTKEY AND

PS.PS_PARTKEY = ANY (

SELECT P_PARTKEY FROM KCHEN.PARTS

WHERE P_BRAND <> 'Brand#45' AND

P_NAME = 'peach snow puff bisque misty' AND

P_TYPE <> 'TIN')

GROUP BY PS_PARTKEY]]>

例子7: 影響連接順序 3、4、1、2 （計(jì)劃優(yōu)化）

通常，查詢的連接順序很大程度上決定了查詢的執(zhí)行性能，因?yàn)樵皆绲剡^(guò)濾行，效率越高。可以使用以下指南來(lái)影響連接順序。注意，當(dāng)出現(xiàn)多個(gè)表引用時(shí)，使用 TABLEID 屬性，而不是 TABID 屬性。

where t71.c1 = t72.c1 and

t72.c2 = t74.c2 and

t74.c1 = t73.c1 and

t73.c2 = t71.c2 and

t71.c3 = t74.c3 and

t72.c3 = t73.c3]]>

例子8: 客戶使用情況（計(jì)劃優(yōu)化）

在批處理運(yùn)行過(guò)程中，當(dāng)刷新一個(gè) MQT 時(shí)，客戶會(huì)遇到性能問(wèn)題。當(dāng)為 MQT 定義中涉及的表 tab2 填充數(shù)據(jù)時(shí)，就會(huì)觸發(fā)對(duì) MQT 的刷新。下面的例子代碼可以演示這個(gè)問(wèn)題。

create table tab1 (i int, b char(30))

create table tab2 (i int, b char(150))

create table mqt1 (cnt,val) as

(select count(*), tab2.b from tab2, tab1 where tab1.b=tab2.b group by tab2.b)

data initially deferred refresh immediate

create index i11 on tab1 (i asc, b asc)

create index i12 on tab1 (b asc, i asc)

create index i21 on tab2 (i asc, b asc)

create index i22 on tab2 (b asc, i asc)

insert into tab2 values(14,substr(char(current timestamp),1,5))

在這個(gè)場(chǎng)景中，經(jīng)過(guò)分析，可以確定使用索引 I11 來(lái)訪問(wèn)表 TAB1 是最優(yōu)的，但是優(yōu)化器的默認(rèn)行為不會(huì)這么做，即使在調(diào)優(yōu)之后也仍然不會(huì)這樣做。但是，可以通過(guò)創(chuàng)建下面的指南來(lái)影響優(yōu)化器，使之考慮 I11 索引，從而將 MQT mqt1 的刷新速度提高兩倍以上。

統(tǒng)計(jì)視圖

基本上，關(guān)系數(shù)據(jù)庫(kù)中的數(shù)據(jù)會(huì)因事務(wù)和批量更新而發(fā)生變化 —— 即使是數(shù)據(jù)集市或數(shù)據(jù)倉(cāng)庫(kù)中的內(nèi)容也會(huì)隨著時(shí)間而變化。SQL 工作負(fù)載常常是動(dòng)態(tài)的 SQL（而不是靜態(tài)的），所以任何基于成本的優(yōu)化器通常都必須對(duì)數(shù)據(jù)、數(shù)據(jù)選擇性和數(shù)據(jù)基數(shù)做出假設(shè)，但是很多情況下，數(shù)據(jù)的分布呈難以預(yù)測(cè)的不均勻性，數(shù)據(jù)域值本身的特性以及表和視圖的相互依賴關(guān)系會(huì)使優(yōu)化器很易出錯(cuò)。

由于查詢是動(dòng)態(tài)的，在編譯時(shí)并不知道其選擇標(biāo)準(zhǔn)，因此，即使有了關(guān)于數(shù)據(jù)的完整的分布統(tǒng)計(jì)，仍然可能生成錯(cuò)誤的計(jì)劃。如果優(yōu)化器能預(yù)知查詢結(jié)果（或部分查詢結(jié)果），那么該信息對(duì)于幫助確定更精確的訪問(wèn)計(jì)劃將非常有用。

基本上，可以有以下兩點(diǎn)假設(shè)：

◆均勻分布

◆域值

為了理解統(tǒng)計(jì)視圖，我們首先看看以上兩點(diǎn)假設(shè)，通常情況下這兩點(diǎn)假設(shè)可能是錯(cuò)誤的。因此，在進(jìn)行查詢計(jì)劃優(yōu)化時(shí)，就需要使用統(tǒng)計(jì)視圖。

均勻分布

考慮以下數(shù)據(jù)，

C1 1 2 3 3 3 3 7 7 9 10

runstats（無(wú)分布）將提供關(guān)于 C1 的以下信息：

CARD = 10,

COLCARD = 6,

LOW2KEY = 2,

HIGH2KEY = 9

那么：

◆C1=3 的行的數(shù)量將被估計(jì)為 10/6 = 1.67。

◆C1=4 和 C1=8 之間的值域被估計(jì)為 ((8-4)/(9-2)) * 10 = 5.71。

但是，如果將數(shù)據(jù)變化一下，以反映數(shù)據(jù)不均勻、大跨度的分布，如下所示：

C1 1 2 3 3 3 3 7 7 99 100

那么：

C1=3 的行的數(shù)量被估計(jì)為 10/6 = 1.67。

C1=4 與 C1=8 之間的值域被估計(jì)為 ((8-4)/(99-2)) * 10 = 0.41。

如果數(shù)據(jù)是完全均勻分布的，如下所示：

C1 1 2 3 4 5 6 7 8 9 10

那么：

C1=3 的行的數(shù)量將被估計(jì)為 10/10 = 1。

C1=4 與 C1=8 之間的值域被估計(jì)為 ((8-4)/(9-2)) * 10 = 5.71。

C1=3 與 C1=7 之間的值域被估計(jì)為 ((7-3)/(9-2)) * 10 = 5.71。

所以，當(dāng)數(shù)據(jù)均勻分布時(shí)，無(wú)論值和范圍如何，真實(shí)的結(jié)果與估計(jì)的結(jié)果都更加一致。

即使擁有頻率值和分位數(shù)值之類的分布統(tǒng)計(jì)信息（這些信息可以大大減少等于和范圍謂詞的估計(jì)錯(cuò)誤），也仍然會(huì)出現(xiàn)估計(jì)錯(cuò)誤無(wú)法接受的情況。

域值

a) 現(xiàn)在看看包含以下數(shù)據(jù)的兩個(gè)表的連接 T1.C1 = T2.C1，其中一組數(shù)據(jù)包含另一組數(shù)據(jù)：

T1.C1 1 2 3 4 5 6 7 8 9 10 T2.C1 1 2 3 4 5 6 7 8 9 10

謂詞的選擇性定義如下：

Selectivity = 1 / ( max ( C1 colcard , C2 colcard ) ) = 0.1

基數(shù)為 10 * 10 * 0.1 = 10。

b) 如果表連接 T1.C1 = T2.C1 中的數(shù)據(jù)在兩組數(shù)據(jù)相交處稍微有所不同，一個(gè)表中的數(shù)據(jù)沒有包含另一個(gè)表中的數(shù)據(jù)：

T1.C1 1 2 3 4 5 6 7 8 9 10 T2.C1 1 2 2 2 2 5 12 13 14 15

在這種情況下，T1.C1 的值，例如 7，不能與 T2.C1 連接，而 T2.C1 的值，例如 12，也不能與 T1.C1 連接，但是估計(jì)算法并不知道這一點(diǎn)，因而會(huì)做出不準(zhǔn)確的假設(shè)，認(rèn)為 T1 中的一個(gè)值很可能與 T2 中的任意值連接，反之亦然。

基數(shù)仍然是 10 * 10 * 0.1 = 10。

所以成本是一樣的，但是 a) 的實(shí)際行輸出結(jié)果為 10，b) 的實(shí)際行輸出結(jié)果為 6。

結(jié)果 1 2 2 2 2 5

顯然，這里存在不一致性，而且，對(duì)于更復(fù)雜的連接，這種錯(cuò)誤估計(jì)的問(wèn)題很可能變得更糟糕。而 V8 FP9 以上版本提供的 DB2 統(tǒng)計(jì)視圖特性，正是為彌補(bǔ)這一類由于數(shù)據(jù)分布和值導(dǎo)致的不一致性而設(shè)計(jì)的。

為了理解統(tǒng)計(jì)視圖的作用，我們來(lái)考慮一個(gè)更實(shí)際一點(diǎn)的連接場(chǎng)景：

T1.C1 T1.C2 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9 I 10 J T2.C1 1 2 2 2 2 5 5 13 14 15

在這種情況下，連接謂詞 T1.C1=T2.C1 和 T1.C2='A'（或 C2 的任何值）返回的基數(shù)估計(jì)將為 1。但是，如果本地謂詞為 T1.C2='B' 或 T1.C2='E'，那么這個(gè)估計(jì)就錯(cuò)得太厲害了。請(qǐng)看上面兩個(gè)表在 T1.C1 = T2.C1 上的連接所產(chǎn)生的如下結(jié)果。

T1.C1 T1.C2 T2.C1 1 A 1 2 B 2 2 B 2 2 B 2 2 B 2 5 E 5 5 E 5

為了彌補(bǔ)這種估計(jì)錯(cuò)誤，可以創(chuàng)建和準(zhǔn)備一個(gè)統(tǒng)計(jì)視圖，并像下面這樣加以利用：

Create view SCHEMA.V1 as select * from T1, T2 where T1.C1 = T2.C1 Alter view SCHEMA.V1 enable query optimization Runstats on table SCHEMA.V1 with distribution

對(duì)于統(tǒng)計(jì)視圖，ENABLE QUERY OPTIMIZATION 子句將導(dǎo)致該視圖以及與之相關(guān)聯(lián)的統(tǒng)計(jì)信息被用于改進(jìn)查詢優(yōu)化。這里只需收集包含分布特征的數(shù)據(jù) runstats 信息。runstats 信息是統(tǒng)計(jì)視圖部署的關(guān)鍵，必須提供比基本表更完整的信息。有時(shí)候，列組或類似的統(tǒng)計(jì)選項(xiàng)會(huì)很有用。

現(xiàn)在，統(tǒng)計(jì)視圖將包含在整個(gè)結(jié)果集上收集到的關(guān)于連接之后的數(shù)據(jù)分布的統(tǒng)計(jì)信息，無(wú)論是在 non-DPF 還是 DPF 環(huán)境中，這個(gè)信息都是完整的，沒有推斷成分。有時(shí)候，runstats 可能要花更多的時(shí)間，這可能是由于視圖本身的規(guī)劃沒做好。在運(yùn)行 runstats 之后，以下附加信息會(huì)成為已知的信息：

結(jié)果的列的 COLCARD

結(jié)果的基數(shù)

值以及值的計(jì)數(shù)

然后，這些信息被包括進(jìn)來(lái)，用于幫助優(yōu)化器在為那些符合條件的查詢（這些查詢不需要直接引用視圖）的選擇性估計(jì)和基數(shù)估計(jì)計(jì)算成本時(shí)做決定。 這將導(dǎo)致更精確的成本計(jì)算和更優(yōu)的訪問(wèn)計(jì)劃。

下面使用以上討論的相同的示例數(shù)據(jù)，闡釋在使用和不使用查詢的統(tǒng)計(jì)視圖的情況下基數(shù)估計(jì)的差別。

select * from T1,T2 where T1.C2='B' and T1.C1=T2.C1

1) 不使用統(tǒng)計(jì)視圖 - 在這種情況下，對(duì)于數(shù)據(jù)的基數(shù)的估計(jì)明顯不準(zhǔn)確。根據(jù)估計(jì)，訪問(wèn)計(jì)劃中的散列連接將返回 1 行，而實(shí)際上是 4 行。

清單2. 不使用統(tǒng)計(jì)視圖情況下的訪問(wèn)計(jì)劃

Rows RETURN ( 1) Cost I/O | 1 HSJOIN ( 2) 15.1653 2/-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.583011 1| | 10 10 TABLE:TABLE: KCHEN.T2KCHEN.T1

2) 使用統(tǒng)計(jì)視圖 - 在這種情況下，估計(jì)到的基數(shù)為 4 行，這相對(duì)于第 1 種情況有很大的提高，并且這次該估計(jì)是完全準(zhǔn)確的。

注意，解釋輸出將包含以下診斷信息，以表明正在使用統(tǒng)計(jì)視圖。在這方面，db2exfmt 工具非常有助于確定是否正在使用統(tǒng)計(jì)視圖。

Diagnostic Details: EXP0147W. The following statistical statistical view may have been used by the optimizer to estimate cardinalities: 'KCHEN '.'V1'.

清單 3. 使用統(tǒng)計(jì)視圖情況下的訪問(wèn)計(jì)劃

Rows RETURN ( 1) Cost I/O | 4 HSJOIN ( 2) 15.1653 2 /-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.58301 1 1 | | 10 10 TABLE: TABLE: KCHEN.T2 KCHEN.T1

通過(guò)利用包含關(guān)于連接的附加信息（包括通過(guò)在 runstats 期間執(zhí)行查詢而得到的 runstats 信息，但是沒有持久地物化詳細(xì)的實(shí)際結(jié)果集）的統(tǒng)計(jì)視圖，基數(shù)估計(jì)得到了改善。針對(duì)一個(gè)或多個(gè)查詢的統(tǒng)計(jì)視圖的使用是透明的，不必直接引用它。統(tǒng)計(jì)視圖有點(diǎn)類似于物化查詢表（Materialized Query Table，MQT），不同的是統(tǒng)計(jì)視圖不需要物化。實(shí)際上，DB2 中對(duì)統(tǒng)計(jì)視圖的支持與對(duì) MQT 的支持具有類似的局限性。當(dāng)前，統(tǒng)計(jì)視圖不支持 SUM、MAX 之類的聚合函數(shù)，也不支持 distinct 操作和 UNION、EXCEPT 或 INTERSECT 之類的集合操作。

在 DB2 V8 FP9 中，需要設(shè)置注冊(cè)表變量 DB2_STATVIEW，并且統(tǒng)計(jì)視圖中只能有 2 個(gè)表引用。此外，還需要手動(dòng)收集統(tǒng)計(jì)信息，因?yàn)?runstats 不能工作于統(tǒng)計(jì)視圖。在 DB2 V9，所有這些限制都已經(jīng)被去掉了。

通常，在涉及事實(shí)表和很多維表的星型連接場(chǎng)景中，統(tǒng)計(jì)視圖中將包括維表的列（本地謂詞列尤其重要），而事實(shí)表中的列則不需要包括進(jìn)來(lái)。這是因?yàn)楫?dāng)事實(shí)表與維表連接時(shí)，事實(shí)表中列的數(shù)據(jù)分布不會(huì)改變，因此優(yōu)化器只需根據(jù)事實(shí)表的列的分布統(tǒng)計(jì)，就可以得到準(zhǔn)確的選擇估計(jì)。一個(gè)例外是在 V8 中，由于 MQT 路由限制，任何查詢謂詞引用的事實(shí)表列都必須包括在統(tǒng)計(jì)視圖中。

給定一個(gè) 3 表連接，可以創(chuàng)建多個(gè)統(tǒng)計(jì)視圖，即 T1 和 T2、T1 和 T3 以及 T1、T2、T3 上的統(tǒng)計(jì)視圖，這些視圖將包含生成最佳訪問(wèn)計(jì)劃所需的所有統(tǒng)計(jì)信息，實(shí)際上前 2 個(gè)視圖就足夠了。

例子1:

Select * from T1, T2, T3 where T1.C1=T2.C1 and T1.C2=T3.C2 and T2.C1=2 and T3.C2 = 'B'Create view SCHEMA.V11 as select T2.* from T1, T2 where T1.C1 = T2.C1 Create view SCHEMA.V12 as select T3.* from T1, T3 where T1.C2 = T3.C2

其中，T1 是與維表 T2 和 T3 有 FK-PK 關(guān)系的事實(shí)表。

為改善估計(jì)，創(chuàng)建 V11 和 V12 這兩個(gè)統(tǒng)計(jì)視圖。

結(jié)束語(yǔ)

當(dāng)查詢性能中出現(xiàn)例外情況時(shí)，優(yōu)化指南和統(tǒng)計(jì)視圖特性對(duì)于彌補(bǔ)訪問(wèn)計(jì)劃估計(jì)的不準(zhǔn)確性非常有用。如果應(yīng)用了所有標(biāo)準(zhǔn)的調(diào)優(yōu)技術(shù)之后，仍然得不到期望的結(jié)果，那么可以考慮這兩個(gè)特性。但是使用這兩個(gè)特性時(shí)要謹(jǐn)慎，因?yàn)閷?duì)于優(yōu)化指南，需要額外的查詢匹配開銷，而對(duì)于統(tǒng)計(jì)視圖，又有編譯方面的開銷。此外，優(yōu)化指南和統(tǒng)計(jì)視圖的特定內(nèi)容也可能會(huì)隨著時(shí)間和數(shù)據(jù)庫(kù)狀態(tài)的改變而改變，例如，優(yōu)化指南可能會(huì)隨著數(shù)據(jù)量而變化，統(tǒng)計(jì)視圖中的統(tǒng)計(jì)信息也有可能過(guò)時(shí)。所以，需要定期地檢查它們的實(shí)現(xiàn)，以便從它們的使用當(dāng)中最大限度地獲益。