在Oracle 數(shù)據(jù)庫(kù)中，我們經(jīng)常需要去手工分析表和索引，還有某個(gè)模式下所有的表和索引等等。oracle 提供了兩種分析方法，分別是dbms_stats 包和analyze 命令。這些是我們?cè)谌粘ｍ?xiàng)目管理中的真實(shí)使用的方法，供參考。
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序定制開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了石泉免費(fèi)建站歡迎大家使用！
在10g 中分析某一個(gè)用戶下所有的對(duì)象，如user_miki 用戶
Sql代碼 收藏代碼
sys.dbms_stats.gather_schema_stats(ownname = 'user_miki',estimate_percent = 30,method_opt = 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade = true,options = 'GATHER');
這是我在項(xiàng)目中分析一個(gè)用戶所有對(duì)象的分析方法，取樣比例為30% ，分析所有索引字段，生成柱形圖，并且也分析索引。
(miki西游 @mikixiyou 文檔，原文鏈接: )
在10g 中分析某一個(gè)用戶下某一個(gè)表及其索引，如user_miki 用戶下info 表和它的索引
Sql代碼 收藏代碼
execute dbms_stats.gather_table_stats(ownname = 'user_miki',tabname = 'info',estimate_percent = 30,method_opt = 'for all indexed columns size auto');
這是我在項(xiàng)目中單獨(dú)分析一個(gè)用戶中一張表的分析方法，取樣比例為30% ，分析所有索引字段，生成柱形圖，并且也分析索引。
我們?cè)趃oogle 時(shí)，還能看到使用analzye 命令分析表的方法。
Sql代碼 收藏代碼
analyze table table_name compute statistics;
這個(gè)會(huì)將索引和表一起分析；
Sql代碼 收藏代碼
analyze table table_name compute statistics for table;
這個(gè)僅分析表；
analyze table table_name compute statistics for all columns;
這個(gè)僅分析表，和上面所有的字段，生成柱形圖
analyze table table_name compute statistics for all indexed columns;
這個(gè)僅分析表，和上面所有建立索引的字段，生成柱形圖
在9i 中，我們常常使用analyze 方法，在10g 以后，就改為dbms_stats.gather_schema_stats 類似的包來(lái)分析數(shù)據(jù)庫(kù)對(duì)象了。
10g 的分析函數(shù)包比9i 的analyze 方法更靈活一些。
在10g 中，會(huì)有個(gè)系統(tǒng)的計(jì)劃任務(wù)，每天10 點(diǎn)和周末兩天都自動(dòng)分析新的數(shù)據(jù)庫(kù)對(duì)象和它認(rèn)為統(tǒng)計(jì)信息已經(jīng)過(guò)期的數(shù)據(jù)庫(kù)對(duì)象。這個(gè)分析結(jié)果有時(shí)候不靠譜。
因此，我都采用10g 中提供的dbms_stats.gather_schema_stats 方法作為補(bǔ)充，設(shè)置一個(gè)計(jì)劃任務(wù)定期去分析業(yè)務(wù)用戶下所有數(shù)據(jù)庫(kù)對(duì)象。
Sql代碼 收藏代碼
variable job number;
begin
sys.dbms_job.submit(job = :job,
what = 'sys.dbms_stats.gather_schema_stats(ownname = ''USR_MIKI'',estimate_percent = 30,method_opt = ''FOR ALL INDEXED COLUMNS SIZE AUTO'',cascade = true,options = ''GATHER'');',
next_date = to_date('16-12-2012 00:56:24', 'dd-mm-yyyy hh24:mi:ss'),
interval = 'sysdate+7');
commit;
end;
/
例如，設(shè)置為每周六運(yùn)行一次。
分析函數(shù)是Oracle專門用于解決復(fù)雜報(bào)表統(tǒng)計(jì)需求的功能強(qiáng)大的函數(shù)，它可以在數(shù)據(jù)中進(jìn)行分組然后基于組計(jì)算某種統(tǒng)計(jì)值，并且每一組的每一行都可以返回一個(gè)統(tǒng)計(jì)值。
說(shuō)白了,分析函數(shù)就是 over([query_partition_clase] order_by_clause)。比如說(shuō)，我采用sum求和，rank排序等等，根據(jù)什么來(lái)呢？over提供一個(gè)窗口,使用partition by進(jìn)行分組，在組內(nèi)使用order by進(jìn)行排序。over不能單獨(dú)使用，要和分析函數(shù)：rank(),dense_rank(),row_number()等一起使用
分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
用于合計(jì)的函數(shù):
用于排列的函數(shù):
其他:
而這樣使用就是分析函數(shù):
它們得出的結(jié)果是相同的，都是：
請(qǐng)注意，這里我們用到了distinct 關(guān)鍵字，如果不用distinct，第2個(gè)查詢將返回20行數(shù)據(jù)，即earnings表的每行記錄都將返回一行總收入，因?yàn)椴挥胐istinct的含義是：針對(duì)每個(gè)打工者計(jì)算他/她所在的月份和地區(qū)的總收入。
在這個(gè)例子中，聚合函數(shù)是更好的選擇，但在另外一些情形下，我們更應(yīng)該使用分析函數(shù)。下面通過(guò)幾個(gè)實(shí)例來(lái)介紹排序分析函數(shù)的用途。
問(wèn)題：統(tǒng)計(jì)每個(gè)月份,不同地區(qū)工資最高的前3名。
利用我們傳統(tǒng)的聚合函數(shù)max可以方便地取出工資最高的一個(gè)員工,但是取出多個(gè)就無(wú)能為力了,同樣，如果不分組我們可以通過(guò)排序取出工資最高的前3名，但無(wú)法實(shí)現(xiàn)對(duì)多個(gè)月份和地區(qū)的分組。而采用rank()分析函數(shù)，可以方便地實(shí)現(xiàn)我們的要求。
完整的語(yǔ)句如下:
結(jié)果為：
我們?cè)陂_窗函數(shù)over()中使用earnmonth(月份)和area(地區(qū))作為分組標(biāo)志,并按照personincome(收入)倒序排列。
注意:RANK()函數(shù)有3組,分別是rank(), dense_rank(), row_number(),它們的區(qū)別是：
RANK()如果出現(xiàn)兩個(gè)相同的數(shù)據(jù),那么后面的數(shù)據(jù)就會(huì)直接跳過(guò)這個(gè)排名,比如：當(dāng)?shù)?名和第3名的利潤(rùn)相同時(shí),rank的結(jié)果是1,2,2,4;而dense_rank()則不會(huì)跳過(guò)這個(gè)排名,結(jié)果是1,2,2,3;而row_number()哪怕是兩個(gè)數(shù)據(jù)完全相同,排名也會(huì)不一樣,結(jié)果是1,2,3,4.
完整的語(yǔ)句如下：
結(jié)果為：
完整的語(yǔ)句如下：
結(jié)果為：
結(jié)果為:
結(jié)果為:
在以上例子中,是用rollup()和cube()函數(shù)都會(huì)對(duì)結(jié)果集產(chǎn)生null，這時(shí)候可用grouping函數(shù)來(lái)確認(rèn)該記錄是由哪個(gè)字段得出來(lái)的.
grouping函數(shù)用法，帶一個(gè)參數(shù)，參數(shù)為字段名，如果當(dāng)前行是由rollup或者cube匯總得來(lái)的，結(jié)果就返回1，反之返回0.
完整語(yǔ)句如下:
結(jié)果為:
以上語(yǔ)句統(tǒng)計(jì)結(jié)果和如下語(yǔ)句使用group by的查詢結(jié)果一樣:
說(shuō)明：Lag和Lead函數(shù)可以在一次查詢中取出某個(gè)字段的前N行和后N行的數(shù)據(jù)（可以是其他字段的數(shù)據(jù)，比如根據(jù)字段甲查詢上一行或下兩行的字段乙），原來(lái)沒(méi)有分析函數(shù)的時(shí)候采用子查詢方法，但是比較麻煩:
語(yǔ)法如下：
其中：
統(tǒng)計(jì)每個(gè)打工者上個(gè)月和下個(gè)月有沒(méi)有賺錢(personincome大于0即為賺錢):
結(jié)果為:
從目前來(lái)看，分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner來(lái)進(jìn)行，
Oracle數(shù)據(jù)庫(kù)的所有更改都記錄在日志中，但是原始的日志信息我們根本無(wú)法看懂，
而LogMiner就是讓我們看懂日志信息的工具。從這一點(diǎn)上看，它和tkprof差不多，一個(gè)是用來(lái)分析日志信息，
一個(gè)則是格式化跟蹤文件。
通過(guò)對(duì)日志的分析我們可以實(shí)現(xiàn)下面的目的：
1、查明數(shù)據(jù)庫(kù)的邏輯更改；
2、偵察并更正用戶的誤操作；
3、執(zhí)行事后審計(jì)；
4、執(zhí)行變化分析。
重慶分公司
重慶分公司
