HowdoestheMETHOD_OPTparameterworkMethod_opt參數(shù)應(yīng)用

這篇文章將詳細(xì)介紹 method_opt 參數(shù)怎樣影響目標(biāo)列上的統(tǒng)計(jì)信息以及為目標(biāo)列收集什么類型的統(tǒng)計(jì)信息. Method_opt 可能是存儲(chǔ)過(guò)程 dbms_stats.gather_*_stats 中最令人費(fèi)解的參數(shù). 這個(gè)參數(shù)最常見(jiàn)的功能就是控制直方圖的收集方式, 但實(shí)際上它的功能遠(yuǎn)不及此.

這篇文章將詳細(xì)介紹 method_opt 參數(shù)怎樣影響目標(biāo)列上的統(tǒng)計(jì)信息以及為目標(biāo)列收集什么類型的統(tǒng)計(jì)信息.

Method_opt 可能是存儲(chǔ)過(guò)程 dbms_stats.gather_*_stats 中最令人費(fèi)解的參數(shù). 這個(gè)參數(shù)最常見(jiàn)的功能就是控制直方圖的收集方式, 但實(shí)際上它的功能遠(yuǎn)不及此. 它的實(shí)際功能如下所示：

Method_opt 參數(shù)用法分為兩個(gè)部分, 如下圖所示：

“For all [indexed | hidden] columns” 這一部分控制著哪些列將會(huì)收集列的基本統(tǒng)計(jì)信息(目標(biāo)列上的最小值, 最大值, 列上不同值的數(shù)量, 空值的數(shù)量等等). 系統(tǒng)默認(rèn)值為 FOR ALL COLUMNS, 它將收集表上所有列(包括隱藏列)的基本的統(tǒng)計(jì)信息. 此外, 它的其他可選值如下所示：

如果一個(gè)字段(列)并不包括在這部分收集基本統(tǒng)計(jì)信息的列的列表中, 收集完成后只會(huì)計(jì)算這個(gè)列的平均長(zhǎng)度. 而列的平均長(zhǎng)度通常會(huì)用于計(jì)算行的平均長(zhǎng)度.

"Size [size_clause]" 這一部分控制收集直方圖的方式, size 后面可以有以下選項(xiàng)：

如果 method_opt 的默認(rèn)參數(shù) FOR ALL COLUMNS SIZE AUTO 在你的數(shù)據(jù)環(huán)境不適用, 可能你遇到的情況屬于下面兩種情況：

下面假設(shè)我們只想在 sales 表上的 cust_id 列上創(chuàng)建直方圖. 記住, method_opt 參數(shù)不僅可以指定哪些列收集基本的統(tǒng)計(jì)信息, 還可以指定哪些列收集直方圖, 因此我們需要考慮把 method_opt 參數(shù)分為兩個(gè)部分.

下面圖中的第一部分指定了哪些列將收集基本的統(tǒng)計(jì)信息. 在這里我們想要收集表上所有列的基本統(tǒng)計(jì)信息, 因此我們使用 For all columns. 但是關(guān)于 size 部分, 我們應(yīng)該怎樣設(shè)置參數(shù)呢？ 因?yàn)槲覀冎幌朐谝粋€(gè)列上收集直方圖, 所以我們先指定 size 1 來(lái)阻止所有列收集直方圖（刪除所有列的直方圖）

下面圖中的第二部分指定了 cust_id 列需要收集直方圖, “For columns” 是 method_opt 語(yǔ)法中額外的部分, 它允許在參數(shù)設(shè)置部分為指定的列提供明確的操作. 在這里我們使用 FOR COLUMNS SIZE 254 CUST_ID 來(lái)指定 cust_id 列收集直方圖. 因此最終的 Method_opt 參數(shù)設(shè)置如下:

下面我們就使用 DBMS_STATS.GATHER_TABLE_STATS 來(lái)執(zhí)行收集統(tǒng)計(jì)信息, 雖然在系統(tǒng)中 sales 表中有很多列會(huì)用在 where 查詢語(yǔ)句中, 但在這里我們只是在 cust_id 列上建立直方圖.

BEGIN
dbms_stats.Gather_table_stats('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID');
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_distinct, histogram

FROM user_tab_col_statistics

WHERE table_name = 'SALES';

對(duì)于 method_opt 參數(shù)在很多種情況下需要用到更復(fù)雜的設(shè)置, 比如你不想收集指定列的任何統(tǒng)計(jì)信息. 目前并沒(méi)有方法告訴 oracle 怎么樣不去收集指定列的統(tǒng)計(jì)信息, 但是你可以反過(guò)來(lái)考慮, 你可以通過(guò) for columns 語(yǔ)法明確的列出需要收集統(tǒng)計(jì)信息的列. 下面我們演示不收集 sales 表中 prog_id 列的統(tǒng)計(jì)信息的方法：

BEGIN
dbms_stats.delete_column_stats('SH', 'SALES', 'PROD_ID');
END;
/

 PL/SQL procedure completed successfully. 

 BEGIN 
 dbms_stats.Gather_table_stats('SH', 'SALES', 

 method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); 
 END; 
 /

 PL/SQL procedure completed successfully. 

-- 即使我們沒(méi)有收集 prod_id 列的統(tǒng)計(jì)信息, oracle 仍然會(huì)準(zhǔn)確的計(jì)算每行的平均長(zhǎng)度.

SQL> SELECT num_rows, avg_row_len FROM user_tables WHERE table_name = 'SALES';

 SQL> SELECT column_name, num_distinct, histogram 
 FROM user_tab_col_statistics 
 WHERE table_name = 'SALES';

注意: for columns 語(yǔ)句只能用于 gather_table_stats 存儲(chǔ)過(guò)程.

最后, 在本文的開(kāi)頭我提到 method_opt 參數(shù)可以用于收集擴(kuò)展的統(tǒng)計(jì)信息. 擴(kuò)展的統(tǒng)計(jì)信息包含兩類列的統(tǒng)計(jì)信息: 列的組合以及列的表達(dá)式的統(tǒng)計(jì)信息. 在下面的例子中, oracle 將會(huì)收集 sales 表上 prod_id 和 cust_id 列的組合的統(tǒng)計(jì)信息以及所有基本列的統(tǒng)計(jì)信息, 同時(shí), oracle 還將會(huì)為列的組合的統(tǒng)計(jì)信息自動(dòng)生成名字.

 BEGIN
 dbms_stats.Gather_table_stats('SH', 'SALES',
 method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254(PROD_ID, CUST_ID)');
 END;
 /

 PL/SQL procedure successfully completed. 

SQL> SELECT column_name, num_distinct, histogram FROM user_tab_col_statistics WHERE table_name = 'SALES';

在收集統(tǒng)計(jì)信息的語(yǔ)句中, 我們不建議在 method_opt 中直接指定參數(shù), 而建議使用 dbms_stats.set_table_prefs 存儲(chǔ)過(guò)程

 BEGIN
 dbms_stats.Set_table_prefs('SH', 'SALES', 'METHOD_OPT', -
 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 PROD_ID');
 END;
 / 

總結(jié), GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, 和 GATHER_SCHEMA_STATS 存儲(chǔ)過(guò)程只接受 “ FOR ALL [INDEXED|HIDDEN] columns ” 語(yǔ)法, 不能指定具體的列名.

GATHER_TABLE_STATS procedure 存儲(chǔ)過(guò)程可以接受以 “for columns ” 格式的額外參數(shù), 使用這種語(yǔ)法可以控制以下內(nèi)容：

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com