Oracle優(yōu)化器模式與Oracle索引優(yōu)化規(guī)則課件

Oracle優(yōu)化器介紹.Oracle索引介紹.SQL優(yōu)化規(guī)則介紹.索引優(yōu)化規(guī)則介紹.Oracle優(yōu)化器介紹.1Oracle優(yōu)化器模式:Oracle在執(zhí)行一個SQL之前,首先要分析一下語句的執(zhí)行計劃,然后再按執(zhí)行計劃去執(zhí)行。分析語句的執(zhí)行計劃的工作是由優(yōu)化器(Optimizer)來完成的.Oracle優(yōu)化器（Optimizer）是Oracle在執(zhí)行SQL之前分析語句的工具.Oracle的優(yōu)化器有兩種優(yōu)化方式：基于規(guī)則的優(yōu)化方式(Rule-BasedOptimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-BasedOptimization,簡稱為CBO).RBO方式︰優(yōu)化器在分析SQL語句時,所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個where子句中的一列有索引時去走索引.CBO方式︰CBO是看語句的代價,這里的代價主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時,主要參照的是表及索引的統(tǒng)計信息。統(tǒng)計信息給出表的大小、有少行、每行的長度等信息。這些統(tǒng)計信息起初在庫內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的.在Oracle8及以后的版本,Oracle列推薦用CBO的方式。Oracle優(yōu)化器模式:2優(yōu)化器的優(yōu)化模式(OptermizerMode):包括Rule,Choose,Firstrows,Allrows這四種方式.Rule:不用多說,即走基于規(guī)則的方式。rboChoose:指的是當(dāng)一個表或或索引有統(tǒng)計信息,則走CBO的方式,如果表或索引沒統(tǒng)計信息,表又不是特別的小,而且相應(yīng)的列有索引時,那么就走索引,走RBO的方式。FirstRows:它與Choose方式是類似的,所不同的是當(dāng)一個表有統(tǒng)計信息時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應(yīng)時間。對于排序分頁頁顯示這種查詢尤其適用.AllRows:也就是我們所說的Cost的方式,當(dāng)一個表有統(tǒng)計信息時,它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統(tǒng)計信息則走基于規(guī)則的方式.查看缺省的Oracle優(yōu)化器:SQL>showparametersoptimizer_mode;可以在init文件中對整個instance的所有會話設(shè)置.也可以單獨對某個會話設(shè)置：SQL>ALTERSESSIONSEToptimizer_mode

=FIRST_ROWS;

優(yōu)化器的優(yōu)化模式(OptermizerMode):包括R3查看統(tǒng)計信息：1.selecttt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_lenfromdba_tablesttwherett.owner='SCOTT';2.selectttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factorfromdba_indexestttwherettt.owner='SCOTT';人工進行統(tǒng)計：對某一個用戶下的所有表和索引執(zhí)行統(tǒng)計分析:executedbms_stats.gather_schema_stats(ownname=>‘SCOTT',cascade=>true);對單個表執(zhí)行統(tǒng)計分析:EXECUTEdbms_stats.gather_table_stats(ownname=>‘SCOTT',tabname=>‘EMP',estimate_percent=>50,cascade=>true).查看統(tǒng)計信息：4從OracleDatabase10g開始，Oracle在建庫后就默認(rèn)創(chuàng)建了一個名為GATHER_STATS_JOB的定時任務(wù)，用于自動收集CBO的統(tǒng)計信息,調(diào)用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統(tǒng)計信息.默認(rèn)情況下在工作日晚上10：00-6：00和周末全天開啟.一起運行的還有另外一個Job:AUTO_SPACE_ADVISOR_JOB.可以通過以下查詢這個JOB的運行情況：

select*fromDba_Scheduler_JobswhereJOB_NAME='GATHER_STATS_JOB'.關(guān)閉自動統(tǒng)計功能：SQL>execBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');從OracleDatabase10g開始，Ora5Oracle索引介紹Oracle索引介紹6Oracle索引簡介索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數(shù)據(jù)(加快查詢)；索引由根節(jié)點、分支節(jié)點和葉子節(jié)點組成，上級索引塊包含下級索引塊的索引數(shù)據(jù)，葉節(jié)點包含索引數(shù)據(jù)和確定行實際位置的rowid。查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。通過每個行的ROWID，索引Oracle提供了訪問單行數(shù)據(jù)的能力。ROWID其實就是直接指向單獨行的線路圖。Oracle索引簡介索引是建立在表的一列或多個列上的輔助對象7索引分類邏輯上:單列索引,多列索引,唯一索引,非惟一索引。物理上:B*-Tree索引，反向索引，位圖索引。索引分類邏輯上:8單列索引和復(fù)合索引單列索引是基于單個列所建立的索引。多列索引是基于兩列或多列所建立的索引。單列索引

Createindexemp_ind1onemp(ename);復(fù)合索引

Createindexemp_ind2onemp(ename,job);單列索引和復(fù)合索引單列索引是基于單個列所建立的索引。多列索引9惟一索引和非惟一索引惟一索引是索引列值不能重復(fù)的索引。非惟一索引是索引列值可以重復(fù)的索引。無論是惟一索引還是非惟一索引，索引列都允許NULL。惟一索引和非惟一索引惟一索引是索引列值不能重復(fù)的索引。10B*-tree索引B*Tree索引是最常見的索引結(jié)構(gòu)，默認(rèn)建立的索引就是這種類型的索引。B*Tree索引在檢索高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指該列有很多不同的值）時提供了最好的性能。當(dāng)取出的行數(shù)占總行數(shù)比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當(dāng)檢查的范圍超過表的10%時就不能提高取回數(shù)據(jù)的性能。B-Tree索引是基于二叉樹的，由分支塊（branchblock）和葉塊（leafblock）組成。在樹結(jié)構(gòu)中，位于最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應(yīng)的rowid。在葉節(jié)點的上面是分支塊，用來導(dǎo)航結(jié)構(gòu)，包含了索引列（關(guān)鍵字）范圍和另一索引塊的地址.創(chuàng)建索引：

1:createindexSTUDENT_IND_nameonSTUDENT(NAME)tablespaceUSERS;2:createindexSTUDENT_IND_nameonSTUDENT(NAME,AGE)tablespaceUSERS;B*-tree索引B*Tree索引是最常見的索引結(jié)構(gòu)，默認(rèn)建11B*-tree索引B*-tree索引12B*-tree索引假設(shè)我們要找索引中值為80的行，從索引樹的最上層入口開始，定位到大于等于50，然后往左找，找到第2個分支塊，定位為75－100，最后再定位到葉塊上，找到80所對應(yīng)的rowid，然后根據(jù)rowid去讀取數(shù)據(jù)塊獲取數(shù)據(jù)。如果查詢條件是范圍選擇的，比如wherecolumn>20andcolumn<80，那么會先定位到第一個包含20的葉塊，然后橫向查找其他的葉塊，直到找到包含80的塊為止，不用每次都從入口進去再重新定位。B*-tree索引假設(shè)我們要找索引中值為80的行，從索引樹的13反向索引反向索引是B*Tree索引的一個分支，它的設(shè)計是為了運用在某些特定的環(huán)境下的。Oracle推出它的主要目的就是為了降低在并行服務(wù)器（OracleParallelServer）環(huán)境下索引葉塊的爭用。當(dāng)B*Tree索引中有一列是由遞增的序列號產(chǎn)生的話，那么這些索引信息基本上分布在同一個葉塊，當(dāng)用戶修改或訪問相似的列時，索引塊很容易產(chǎn)生爭用。反向索引中的索引碼將會被分布到各個索引塊中，減少了爭用。反向索引反轉(zhuǎn)了索引碼中每列的字節(jié)。建立反向索引:createindexreversed_ind_emponemp(Order_ID)reverse;反向索引反向索引是B*Tree索引的一個分支，它的設(shè)計是為14反向索引如果有一個名稱為order_id的列值在某行上是12345，oracle就將它顛倒為54321。這樣連續(xù)的幾個數(shù)不會在同一個頁塊里。不過反向索引又一個缺點就是不能在所有使用常規(guī)索引的地方使用。在范圍搜索中其不能被使用，例如，wherecolumn>value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜索相鄰葉塊完成區(qū)域掃描。

注意：只有索引中的值是逆向的，表中的值保持不變。反向索引如果有一個名稱為order_id的列值在某行上是1215位圖索引位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數(shù)為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那么3/3000000約等于0，這種情況下最適合用位圖索引。

創(chuàng)建位圖索引:createbitmapindexinx_bitmap_emponemp(sex);位圖索引位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級鎖16位圖索引的格式行值

1

2

3

4

5

6

7

8

9

10

Male

1

0

0

0

0

0

0

0

1

1

Female

0

1

1

1

0

0

1

1

0

0

Null

0

0

0

0

1

1

0

0

0

0

位圖索引的格式行值

1

2

3

4

5

6

7

817位圖索引如果搜索where

gender=’Male’,要統(tǒng)計性別是”Male”的列行數(shù)的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where

gender=’Male’

or

gender=’Female’的列的行數(shù)的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會用內(nèi)部的轉(zhuǎn)換函數(shù)將位圖中的相關(guān)信息轉(zhuǎn)換成rowid來訪問數(shù)據(jù)塊。

位圖索引如果搜索where

gender=’Male’,要統(tǒng)18聚簇聚簇是根據(jù)碼值找到數(shù)據(jù)的物理存儲位置，從而達到快速檢索數(shù)據(jù)的目的。聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，葉節(jié)點就是數(shù)據(jù)節(jié)點。非聚簇索引的順序與數(shù)據(jù)物理排列順序無關(guān)，葉節(jié)點仍然是索引節(jié)點，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。一個表最多只能有一個聚簇索引。

聚簇聚簇是根據(jù)碼值找到數(shù)據(jù)的物理存儲位置，從而達到快速檢索數(shù)19使用Oracle聚簇索引在Oracle當(dāng)中，聚簇不是索引的組織形式，而是表的組織形式。多用于表之間的連接字段。例:dept(deptno,

dnma,e

loc)

和表

emp(empno,

ename,

job,

mgr,

...

deptno),

常在deptno上進行連接,可以針對deptno字段建立聚簇，然后建立基于該聚簇的索引，并讓兩個表都使用上該聚簇。使用Oracle聚簇索引在Oracle當(dāng)中，聚簇不是索20Oracle聚簇索引CREATECLUSTERpersonnel(department_numberNUMBER(2))SIZE512STORAGE(INITIAL100KNEXT50K);CREATETABLEemp(empnoNUMBERPRIMARYKEY,enameVARCHAR2(10)NOTNULLCHECK(ename=UPPER(ename)),jobVARCHAR2(9),mgrNUMBER,commNUMBER(9,0)DEFAULTNULL,deptnoNUMBER(2)NOTNULL)CLUSTERpersonnel(deptno);

CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(9),locVARCHAR2(9))CLUSTERpersonnel(deptno);

CREATEINDEXidx_personnelONCLUSTERpersonnel;Oracle聚簇索引CREATECLUSTER21Oracle聚簇索引這樣可以讓兩個表同時用上聚簇索引。保證兩個表的記錄按照depno值盡量存放到同一個物理塊當(dāng)中。Oracle聚簇索引這樣可以讓兩個表同時用上聚簇索引。保證22使用索引的一些規(guī)則1.索引對大表最有用，不要在小表上加索引.2.為每個表中的主碼指定一個唯一索引。3.索引對于那些頻繁出現(xiàn)在SQL命令中的where子句中的列最有用，不管這些列在選擇中用來限定行還是為了表連接。4.當(dāng)一個屬性中存在很多不同的值時，可以使用索引。Oracle建議當(dāng)一個屬性中有少于30個不同值時，索引不是很有用，當(dāng)屬性中有100或更多不同值時索引就很明顯地有用了。相似地，只有當(dāng)使用索引進行查詢的結(jié)果不超過文件中所有記錄總數(shù)的20%時，使用索引才有幫助。5.檢查你的DBMS對索引的限制，即便要在每個表允許的索引個數(shù)上。許多系統(tǒng)不超過16個索引而且限制每個索引鍵值的大小。對一個表創(chuàng)建的索引數(shù)一般不超過5個.使用索引的一些規(guī)則1.索引對大表最有用，不要在小表上加索引.237.對于包含空值的屬性建立索引時要小心，在很多DBMS里有空值的行不能在索引中作為參照。8.時常需要做刪除、更新、插入操作的表不要創(chuàng)建索引.9.將表和索引建立在不同的表空間內(nèi)(TABLESPACES).不要將不屬于ORACLE內(nèi)部系統(tǒng)的對象存放到SYSTEM表空間里.同時,確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤上.減少I/O競爭.7.對于包含空值的屬性建立索引時要小心，在很多DBMS里有24SQL優(yōu)化的一些規(guī)則:(有些規(guī)則只有在基于規(guī)則的優(yōu)化器里有效).SQL優(yōu)化的一些規(guī)則:251>選擇最有效率的表名順序：1.把記錄少的表放在from子句的最后面一個表.2.如果有3個以上的表連接查詢,那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表,交叉表是指那個被其他表所引用的表.原因：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理.在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個表時,會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行排序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.

1>選擇最有效率的表名順序：262>WHERE子句中的連接順序：

ORACLE采用自右向左的順序解析WHERE子句,,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.

3.>SELECT子句中避免使用‘*‘

ORACLE在解析的過程中,需要通過查詢數(shù)據(jù)字典將'*'依次轉(zhuǎn)換成所有的列名.4.>使用表的別名(Alias)當(dāng)在SQL語句中連接多個表時,請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤.

(Column歧義指的是由于SQL中不同的表具有相同的Column名,當(dāng)SQL語句中出現(xiàn)這個Column時,SQL解析器無法判斷這個Column的歸屬)

5.>減少訪問數(shù)據(jù)庫的次數(shù):當(dāng)執(zhí)行每條SQL語句時,ORACLE在內(nèi)部執(zhí)行了許多工作:解析SQL語句,估算索引的利用率,綁定變量,讀數(shù)據(jù)塊等等.由此可見,減少訪問數(shù)據(jù)庫的次數(shù),就能實際上減少ORACLE的工作量.2>WHERE子句中的連接順序：276.>(可能的話)用TRUNCATE替代DELETE.當(dāng)刪除表中的記錄時,在通常情況下,回滾段(rollbacksegments)用來存放可以被恢復(fù)的信息.如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到執(zhí)行刪除命令之前的狀況.而當(dāng)運用TRUNCATE時,回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短.

(TRUNCATE只在刪除全表里的記錄時適用,TRUNCATE是DDL不是DML)

7.>(可能的話)使用COMMIT

只要有可能,在程序中盡量多使用COMMIT,這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:

COMMIT所釋放的資源:

a.回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b.被程序語句獲得的鎖

c.redologbuffer中的空間

d.ORACLE為管理上述3種資源中的內(nèi)部花費6.>(可能的話)用TRUNCATE替代DELETE.288.>(可能的話)用Where子句替換HAVING子句盡量少使用HAVING子句,HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾.這個處理需要排序,總計等操作.如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.

9.>(某些情況下)可以用EXISTS替代IN.NOTEXISTS替代NOTIN性能比較:1.Select*fromt1wherexin(selectyfromt2)2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)當(dāng)t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當(dāng)子查詢記錄集很小時用in比較合適.8.>(可能的話)用Where子句替換HAVING子句29原因分析：

1.Select*fromT1wherexin(selectyfromT2)

執(zhí)行的過程相當(dāng)于:

select*

fromt1,(selectdistinctyfromt2)t3wheret1.x=t3.y;

2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)

執(zhí)行的過程相當(dāng)于:

forxin(select*fromt1)loop

if(exists(select1fromt2wheret2.y=t1.x)

then

OUTPUTTHERECORD

endif

endloop

這樣表

T1要被完全掃描一遍.所以可以得出結(jié)論:當(dāng)t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當(dāng)子查詢記錄集很小時用in比較合適.原因分析：3010.>用表連接替換EXISTS

改進第9打優(yōu)化規(guī)則的例子.11.>用EXISTS替換DISTINCTEXISTS使查詢更為迅速,因為RDBMS核心模塊在子查詢的條件一旦滿足后立刻返回結(jié)果.DISTINCT會先進行排序,然后會根據(jù)排序后的順序去除相同的行.12.>使用顯式的游標(biāo)(CURSOR)使用隱式的游標(biāo),將會執(zhí)行兩次操作.第一次檢索記錄,第二次檢查TOOMANYROWS這個exception.而顯式游標(biāo)不執(zhí)行第二次操作.10.>用表連接替換EXISTS

改進第9打優(yōu)化規(guī)則的例子.3111-例：1.(低效)SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO;2.高效:

SELECTDEPT_NO,DEPT_NAMEFROMDEPTD

WHEREEXISTS(SELECT*

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);

11-例：3213.用索引提高效率通常,通過索引查詢數(shù)據(jù)比全表掃描要快.當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時,ORACLE優(yōu)化器將使用索引.

除了那些LONG或LONGRAW數(shù)據(jù)類型,你可以索引幾乎所有的列.在大型表中使用索引特別有效.

雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.索引需要空間來存儲,也需要定期維護,每當(dāng)有記錄在表中增減或索引列被修改時,索引本身也會被修改.這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5次的磁盤I/O.因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.定期的重構(gòu)索引是有必要的.ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>

13.用索引提高效率33Oracle索引優(yōu)化規(guī)則Oracle索引優(yōu)化規(guī)則34索引優(yōu)化規(guī)則：1.like件中不要以通配符(WILDCARD)開始,否則索引將不被采用.

例:SELECTLODGINGFROMLODGINGWHEREMANAGERLIKE‘％HANMAN';2.避免在索引列上使用計算或改變索引列的類型或使用‘!=’及<>例:SELECT…FROMDEPTWHERESAL*12>25000;SELECT…FROMEMPWHEREEMP_TYPE=to_char(123);

select….WhereACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';select…whereempno!=8888;3.避免在索引列上使用NOT.4.用>=替代>.高效:SELECT*FROMEMPWHEREDEPTNO>=4

低效:SELECT*FROMEMPWHEREDEPTNO>3

兩者的區(qū)別在于,前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄.索引優(yōu)化規(guī)則：355.用UNION替換OR(適用于索引列)通常情況下,用UNION替換WHERE子句中的OR將會起到較好的效果.對索引列使用OR將造成全表掃描.注意,以上規(guī)則只針對多個索引列有效.如果有column沒有被索引,查詢效率可能會因為你沒有選擇OR而降低.

在下面的例子中,LOC_ID和REGION上都建有索引.

高效:

SELECTLOC_ID,LOC_DESC,REGION

FROMLOCATION

WHERELOC_ID=10

UNION

SELECTLOC_ID,LOC_DESC,REGION

FROMLOCATION

WHEREREGION=“MELBOURNE”低效:

SELECTLOC_ID,LOC_DESC,REGION

FROMLOCATION

WHERELOC_ID=10ORREGION=“MELBOURNE”

如果你堅持要用OR,那就需要返回記錄最少的索引列寫在最前面注意：WHEREKEY1=10（返回最少記錄）

ORKEY2=20（返回最多記錄）

ORACLE內(nèi)部將以上轉(zhuǎn)換為

WHEREKEY1=10AND（（NOTKEY1=10）ANDKEY2=20）5.用UNION替換OR(適用于索引列)36

6.避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引．對于單列索引，如果列包含空值，索引中將不存在此記錄.對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄.如果至少有一個列不為空，則記錄存在于索引中．

(建議：可以給null值的字段設(shè)置一個默認(rèn)值))

7. 如果索引是建立在多個列上,索引時段需要放在where條件的第一個條件(Oracle8i之前),Oracle8i之后允許跳躍式索引.8.(可能的話)用UNION-ALL替換UNION.

UNION-ALL就是做簡單的合并，不會進行排序，UNION先做簡單的合并，然后做進行排序，最后去除重復(fù)的記錄。6.避免在索引列上使用ISNULL和ISNOTNU379.避免使用耗費資源的操作帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟動SQL引擎.

執(zhí)行耗費資源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要執(zhí)行兩次排序.

例如,一個UNION查詢,其中每個查詢都帶有GROUPBY子句,GROUPBY會觸發(fā)嵌入排序(NESTEDSORT);這樣,每個查詢需要執(zhí)行一次排序,然后在執(zhí)行UNION時,又一個唯一排序(SORTUNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行.嵌入的排序的深度會大大影響查詢的效率.

通常,帶有UNION,MINUS,INTERSECT的SQL語句都可以用其他方式重寫.

9.避免使用耗費資源的操作38

theend!Oracle優(yōu)化器模式與Oracle索引優(yōu)化規(guī)則課件39Oracle優(yōu)化器介紹.Oracle索引介紹.SQL優(yōu)化規(guī)則介紹.索引優(yōu)化規(guī)則介紹.Oracle優(yōu)化器介紹.40Oracle優(yōu)化器模式:Oracle在執(zhí)行一個SQL之前,首先要分析一下語句的執(zhí)行計劃,然后再按執(zhí)行計劃去執(zhí)行。分析語句的執(zhí)行計劃的工作是由優(yōu)化器(Optimizer)來完成的.Oracle優(yōu)化器（Optimizer）是Oracle在執(zhí)行SQL之前分析語句的工具.Oracle的優(yōu)化器有兩種優(yōu)化方式：基于規(guī)則的優(yōu)化方式(Rule-BasedOptimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-BasedOptimization,簡稱為CBO).RBO方式︰優(yōu)化器在分析SQL語句時,所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個where子句中的一列有索引時去走索引.CBO方式︰CBO是看語句的代價,這里的代價主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時,主要參照的是表及索引的統(tǒng)計信息。統(tǒng)計信息給出表的大小、有少行、每行的長度等信息。這些統(tǒng)計信息起初在庫內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的.在Oracle8及以后的版本,Oracle列推薦用CBO的方式。Oracle優(yōu)化器模式:41優(yōu)化器的優(yōu)化模式(OptermizerMode):包括Rule,Choose,Firstrows,Allrows這四種方式.Rule:不用多說,即走基于規(guī)則的方式。rboChoose:指的是當(dāng)一個表或或索引有統(tǒng)計信息,則走CBO的方式,如果表或索引沒統(tǒng)計信息,表又不是特別的小,而且相應(yīng)的列有索引時,那么就走索引,走RBO的方式。FirstRows:它與Choose方式是類似的,所不同的是當(dāng)一個表有統(tǒng)計信息時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應(yīng)時間。對于排序分頁頁顯示這種查詢尤其適用.AllRows:也就是我們所說的Cost的方式,當(dāng)一個表有統(tǒng)計信息時,它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統(tǒng)計信息則走基于規(guī)則的方式.查看缺省的Oracle優(yōu)化器:SQL>showparametersoptimizer_mode;可以在init文件中對整個instance的所有會話設(shè)置.也可以單獨對某個會話設(shè)置：SQL>ALTERSESSIONSEToptimizer_mode

=FIRST_ROWS;

優(yōu)化器的優(yōu)化模式(OptermizerMode):包括R42查看統(tǒng)計信息：1.selecttt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_lenfromdba_tablesttwherett.owner='SCOTT';2.selectttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factorfromdba_indexestttwherettt.owner='SCOTT';人工進行統(tǒng)計：對某一個用戶下的所有表和索引執(zhí)行統(tǒng)計分析:executedbms_stats.gather_schema_stats(ownname=>‘SCOTT',cascade=>true);對單個表執(zhí)行統(tǒng)計分析:EXECUTEdbms_stats.gather_table_stats(ownname=>‘SCOTT',tabname=>‘EMP',estimate_percent=>50,cascade=>true).查看統(tǒng)計信息：43從OracleDatabase10g開始，Oracle在建庫后就默認(rèn)創(chuàng)建了一個名為GATHER_STATS_JOB的定時任務(wù)，用于自動收集CBO的統(tǒng)計信息,調(diào)用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統(tǒng)計信息.默認(rèn)情況下在工作日晚上10：00-6：00和周末全天開啟.一起運行的還有另外一個Job:AUTO_SPACE_ADVISOR_JOB.可以通過以下查詢這個JOB的運行情況：

select*fromDba_Scheduler_JobswhereJOB_NAME='GATHER_STATS_JOB'.關(guān)閉自動統(tǒng)計功能：SQL>execBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');從OracleDatabase10g開始，Ora44Oracle索引介紹Oracle索引介紹45Oracle索引簡介索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數(shù)據(jù)(加快查詢)；索引由根節(jié)點、分支節(jié)點和葉子節(jié)點組成，上級索引塊包含下級索引塊的索引數(shù)據(jù)，葉節(jié)點包含索引數(shù)據(jù)和確定行實際位置的rowid。查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。通過每個行的ROWID，索引Oracle提供了訪問單行數(shù)據(jù)的能力。ROWID其實就是直接指向單獨行的線路圖。Oracle索引簡介索引是建立在表的一列或多個列上的輔助對象46索引分類邏輯上:單列索引,多列索引,唯一索引,非惟一索引。物理上:B*-Tree索引，反向索引，位圖索引。索引分類邏輯上:47單列索引和復(fù)合索引單列索引是基于單個列所建立的索引。多列索引是基于兩列或多列所建立的索引。單列索引

Createindexemp_ind1onemp(ename);復(fù)合索引

Createindexemp_ind2onemp(ename,job);單列索引和復(fù)合索引單列索引是基于單個列所建立的索引。多列索引48惟一索引和非惟一索引惟一索引是索引列值不能重復(fù)的索引。非惟一索引是索引列值可以重復(fù)的索引。無論是惟一索引還是非惟一索引，索引列都允許NULL。惟一索引和非惟一索引惟一索引是索引列值不能重復(fù)的索引。49B*-tree索引B*Tree索引是最常見的索引結(jié)構(gòu)，默認(rèn)建立的索引就是這種類型的索引。B*Tree索引在檢索高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指該列有很多不同的值）時提供了最好的性能。當(dāng)取出的行數(shù)占總行數(shù)比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當(dāng)檢查的范圍超過表的10%時就不能提高取回數(shù)據(jù)的性能。B-Tree索引是基于二叉樹的，由分支塊（branchblock）和葉塊（leafblock）組成。在樹結(jié)構(gòu)中，位于最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應(yīng)的rowid。在葉節(jié)點的上面是分支塊，用來導(dǎo)航結(jié)構(gòu)，包含了索引列（關(guān)鍵字）范圍和另一索引塊的地址.創(chuàng)建索引：

1:createindexSTUDENT_IND_nameonSTUDENT(NAME)tablespaceUSERS;2:createindexSTUDENT_IND_nameonSTUDENT(NAME,AGE)tablespaceUSERS;B*-tree索引B*Tree索引是最常見的索引結(jié)構(gòu)，默認(rèn)建50B*-tree索引B*-tree索引51B*-tree索引假設(shè)我們要找索引中值為80的行，從索引樹的最上層入口開始，定位到大于等于50，然后往左找，找到第2個分支塊，定位為75－100，最后再定位到葉塊上，找到80所對應(yīng)的rowid，然后根據(jù)rowid去讀取數(shù)據(jù)塊獲取數(shù)據(jù)。如果查詢條件是范圍選擇的，比如wherecolumn>20andcolumn<80，那么會先定位到第一個包含20的葉塊，然后橫向查找其他的葉塊，直到找到包含80的塊為止，不用每次都從入口進去再重新定位。B*-tree索引假設(shè)我們要找索引中值為80的行，從索引樹的52反向索引反向索引是B*Tree索引的一個分支，它的設(shè)計是為了運用在某些特定的環(huán)境下的。Oracle推出它的主要目的就是為了降低在并行服務(wù)器（OracleParallelServer）環(huán)境下索引葉塊的爭用。當(dāng)B*Tree索引中有一列是由遞增的序列號產(chǎn)生的話，那么這些索引信息基本上分布在同一個葉塊，當(dāng)用戶修改或訪問相似的列時，索引塊很容易產(chǎn)生爭用。反向索引中的索引碼將會被分布到各個索引塊中，減少了爭用。反向索引反轉(zhuǎn)了索引碼中每列的字節(jié)。建立反向索引:createindexreversed_ind_emponemp(Order_ID)reverse;反向索引反向索引是B*Tree索引的一個分支，它的設(shè)計是為53反向索引如果有一個名稱為order_id的列值在某行上是12345，oracle就將它顛倒為54321。這樣連續(xù)的幾個數(shù)不會在同一個頁塊里。不過反向索引又一個缺點就是不能在所有使用常規(guī)索引的地方使用。在范圍搜索中其不能被使用，例如，wherecolumn>value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜索相鄰葉塊完成區(qū)域掃描。

注意：只有索引中的值是逆向的，表中的值保持不變。反向索引如果有一個名稱為order_id的列值在某行上是1254位圖索引位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數(shù)為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那么3/3000000約等于0，這種情況下最適合用位圖索引。

創(chuàng)建位圖索引:createbitmapindexinx_bitmap_emponemp(sex);位圖索引位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級鎖55位圖索引的格式行值

1

2

3

4

5

6

7

8

9

10

Male

1

0

0

0

0

0

0

0

1

1

Female

0

1

1

1

0

0

1

1

0

0

Null

0

0

0

0

1

1

0

0

0

0

位圖索引的格式行值

1

2

3

4

5

6

7

856位圖索引如果搜索where

gender=’Male’,要統(tǒng)計性別是”Male”的列行數(shù)的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where

gender=’Male’

or

gender=’Female’的列的行數(shù)的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會用內(nèi)部的轉(zhuǎn)換函數(shù)將位圖中的相關(guān)信息轉(zhuǎn)換成rowid來訪問數(shù)據(jù)塊。

位圖索引如果搜索where

gender=’Male’,要統(tǒng)57聚簇聚簇是根據(jù)碼值找到數(shù)據(jù)的物理存儲位置，從而達到快速檢索數(shù)據(jù)的目的。聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，葉節(jié)點就是數(shù)據(jù)節(jié)點。非聚簇索引的順序與數(shù)據(jù)物理排列順序無關(guān)，葉節(jié)點仍然是索引節(jié)點，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊。一個表最多只能有一個聚簇索引。

聚簇聚簇是根據(jù)碼值找到數(shù)據(jù)的物理存儲位置，從而達到快速檢索數(shù)58使用Oracle聚簇索引在Oracle當(dāng)中，聚簇不是索引的組織形式，而是表的組織形式。多用于表之間的連接字段。例:dept(deptno,

dnma,e

loc)

和表

emp(empno,

ename,

job,

mgr,

...

deptno),

常在deptno上進行連接,可以針對deptno字段建立聚簇，然后建立基于該聚簇的索引，并讓兩個表都使用上該聚簇。使用Oracle聚簇索引在Oracle當(dāng)中，聚簇不是索59Oracle聚簇索引CREATECLUSTERpersonnel(department_numberNUMBER(2))SIZE512STORAGE(INITIAL100KNEXT50K);CREATETABLEemp(empnoNUMBERPRIMARYKEY,enameVARCHAR2(10)NOTNULLCHECK(ename=UPPER(ename)),jobVARCHAR2(9),mgrNUMBER,commNUMBER(9,0)DEFAULTNULL,deptnoNUMBER(2)NOTNULL)CLUSTERpersonnel(deptno);

CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(9),locVARCHAR2(9))CLUSTERpersonnel(deptno);

CREATEINDEXidx_personnelONCLUSTERpersonnel;Oracle聚簇索引CREATECLUSTER60Oracle聚簇索引這樣可以讓兩個表同時用上聚簇索引。保證兩個表的記錄按照depno值盡量存放到同一個物理塊當(dāng)中。Oracle聚簇索引這樣可以讓兩個表同時用上聚簇索引。保證61使用索引的一些規(guī)則1.索引對大表最有用，不要在小表上加索引.2.為每個表中的主碼指定一個唯一索引。3.索引對于那些頻繁出現(xiàn)在SQL命令中的where子句中的列最有用，不管這些列在選擇中用來限定行還是為了表連接。4.當(dāng)一個屬性中存在很多不同的值時，可以使用索引。Oracle建議當(dāng)一個屬性中有少于30個不同值時，索引不是很有用，當(dāng)屬性中有100或更多不同值時索引就很明顯地有用了。相似地，只有當(dāng)使用索引進行查詢的結(jié)果不超過文件中所有記錄總數(shù)的20%時，使用索引才有幫助。5.檢查你的DBMS對索引的限制，即便要在每個表允許的索引個數(shù)上。許多系統(tǒng)不超過16個索引而且限制每個索引鍵值的大小。對一個表創(chuàng)建的索引數(shù)一般不超過5個.使用索引的一些規(guī)則1.索引對大表最有用，不要在小表上加索引.627.對于包含空值的屬性建立索引時要小心，在很多DBMS里有空值的行不能在索引中作為參照。8.時常需要做刪除、更新、插入操作的表不要創(chuàng)建索引.9.將表和索引建立在不同的表空間內(nèi)(TABLESPACES).不要將不屬于ORACLE內(nèi)部系統(tǒng)的對象存放到SYSTEM表空間里.同時,確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤上.減少I/O競爭.7.對于包含空值的屬性建立索引時要小心，在很多DBMS里有63SQL優(yōu)化的一些規(guī)則:(有些規(guī)則只有在基于規(guī)則的優(yōu)化器里有效).SQL優(yōu)化的一些規(guī)則:641>選擇最有效率的表名順序：1.把記錄少的表放在from子句的最后面一個表.2.如果有3個以上的表連接查詢,那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表,交叉表是指那個被其他表所引用的表.原因：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理.在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個表時,會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行排序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.

1>選擇最有效率的表名順序：652>WHERE子句中的連接順序：

ORACLE采用自右向左的順序解析WHERE子句,,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.

3.>SELECT子句中避免使用‘*‘

ORACLE在解析的過程中,需要通過查詢數(shù)據(jù)字典將'*'依次轉(zhuǎn)換成所有的列名.4.>使用表的別名(Alias)當(dāng)在SQL語句中連接多個表時,請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤.

(Column歧義指的是由于SQL中不同的表具有相同的Column名,當(dāng)SQL語句中出現(xiàn)這個Column時,SQL解析器無法判斷這個Column的歸屬)

5.>減少訪問數(shù)據(jù)庫的次數(shù):當(dāng)執(zhí)行每條SQL語句時,ORACLE在內(nèi)部執(zhí)行了許多工作:解析SQL語句,估算索引的利用率,綁定變量,讀數(shù)據(jù)塊等等.由此可見,減少訪問數(shù)據(jù)庫的次數(shù),就能實際上減少ORACLE的工作量.2>WHERE子句中的連接順序：666.>(可能的話)用TRUNCATE替代DELETE.當(dāng)刪除表中的記錄時,在通常情況下,回滾段(rollbacksegments)用來存放可以被恢復(fù)的信息.如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到執(zhí)行刪除命令之前的狀況.而當(dāng)運用TRUNCATE時,回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短.

(TRUNCATE只在刪除全表里的記錄時適用,TRUNCATE是DDL不是DML)

7.>(可能的話)使用COMMIT

只要有可能,在程序中盡量多使用COMMIT,這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:

COMMIT所釋放的資源:

a.回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b.被程序語句獲得的鎖

c.redologbuffer中的空間

d.ORACLE為管理上述3種資源中的內(nèi)部花費6.>(可能的話)用TRUNCATE替代DELETE.678.>(可能的話)用Where子句替換HAVING子句盡量少使用HAVING子句,HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾.這個處理需要排序,總計等操作.如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.

9.>(某些情況下)可以用EXISTS替代IN.NOTEXISTS替代NOTIN性能比較:1.Select*fromt1wherexin(selectyfromt2)2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)當(dāng)t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當(dāng)子查詢記錄集很小時用in比較合適.8.>(可能的話)用Where子句替換HAVING子句68原因分析：

1.Select*fromT1wherexin(selectyfromT2)

執(zhí)行的過程相當(dāng)于:

select*

fromt1,(selectdistinctyfromt2)t3wheret1.x=t3.y;

2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)

執(zhí)行的過程相當(dāng)于:

forxin(select*fromt1)loop

if(exists(select1fromt2wheret2.y=t1.x)

then

OUTPUTTHERECORD

endif

endloop

這樣表

T1要被完全掃描一遍.所以可以得出結(jié)論:當(dāng)t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當(dāng)子查詢記錄集很小時用in比較合適.原因分析：6910.>用表連接替換EXISTS

改進第9打優(yōu)化規(guī)則的例子.11.>用EXISTS替換DISTINCTEXISTS使查詢更為迅速,因為RDBMS核心模塊在子查詢的條件一旦滿足后立刻返回結(jié)果.DISTINCT會先進行排序,然后會根據(jù)排序后的順序去除相同的行.12.>使用顯式的游標(biāo)(CURSOR)使用隱式的游標(biāo),將會執(zhí)行兩次操作.第一次檢索記錄,第二次檢查TOOMANYROWS這個exception.而顯式游標(biāo)不執(zhí)行第二次操作.10.>用表連接替換EXISTS

改進第9打優(yōu)化規(guī)則的例子.7011-例：1.(低效)SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO;2.高效:

SELECTDEPT_NO,DEPT_NAMEFROMDEPTD

WHEREEXISTS(SELECT*

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);

11-例：7113.用索引提高效率通常,通過索引查詢數(shù)據(jù)比全表掃描要快.當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時,ORACLE優(yōu)化器將使用索引.

除了那些LONG或LONGRAW數(shù)據(jù)類型,你可以索引幾乎所有的列.在大型表中使用索引特別有效.

雖然使