1、Oracle 優(yōu)化器介紹.Oracle索引介紹.SQL優(yōu)化規(guī)則介紹.索引優(yōu)化規(guī)則介紹.Oracle優(yōu)化器模式優(yōu)化器模式:Oracle在執(zhí)行一個(gè)SQL之前,首先要分析一下語(yǔ)句的執(zhí)行計(jì)劃,然后再按執(zhí)行計(jì)劃去執(zhí)行。分析語(yǔ)句的執(zhí)行計(jì)劃的工作是由優(yōu)化器(Optimizer) 來(lái)完成的 .Oracle優(yōu)化器（Optimizer）是Oracle在執(zhí)行SQL之前分析語(yǔ)句的工具.Oracle的優(yōu)化器有兩種優(yōu)化方式：基于規(guī)則的優(yōu)化方式(Rule-Based Optimization,簡(jiǎn)稱(chēng)為RBO)和基于代價(jià)的優(yōu)化方式(Cost-Based Optimization,簡(jiǎn)稱(chēng)為CBO). RBO方式優(yōu)化器在分析SQL語(yǔ)

2、句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見(jiàn)的,當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引. CBO方式CBO是看語(yǔ)句的代價(jià),這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小 、有少行、每行的長(zhǎng)度等信息。這些統(tǒng)計(jì)信息起初在庫(kù)內(nèi)是沒(méi)有的,是你在做analyze后才出現(xiàn)的.在Oracle8及以后的版本,Oracle列推薦用CBO的方式。優(yōu)化器的優(yōu)化模式(Optermizer Mode) :包括Rule,Choose,First rows,All rows這四種方式.Rule:不用多說(shuō),即走基于規(guī)則的方式。 rboCh

3、oose:指的是當(dāng)一個(gè)表或或索引有統(tǒng)計(jì)信息,則走CBO的方式,如果表或索引沒(méi)統(tǒng)計(jì)信息,表又不是特別的小,而且相應(yīng)的列有索引時(shí),那么就走索引,走RBO的方式。First Rows:它與Choose方式是類(lèi)似的,所不同的是當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí),它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應(yīng)時(shí)間。對(duì)于排序分頁(yè)頁(yè)顯示這種查詢尤其適用.All Rows:也就是我們所說(shuō)的Cost的方式,當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí),它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒(méi)有統(tǒng)計(jì)信息則走基于規(guī)則的方式 .查看缺省的Oracle優(yōu)化器:SQLshow parameters optimizer_mod

4、e;可以在init文件中對(duì)整個(gè)instance的所有會(huì)話設(shè)置.也可以單獨(dú)對(duì)某個(gè)會(huì)話設(shè)置： SQLALTER SESSION SET optimizer_mode= FIRST_ROWS; 查看統(tǒng)計(jì)信息： 1.select tt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_len from dba_tables tt where tt.owner=SCOTT;2.select ttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key

5、,ttt.clustering_factor from dba_indexes ttt where ttt.owner=SCOTT;人工進(jìn)行統(tǒng)計(jì)：對(duì)某一個(gè)用戶下的所有表和索引執(zhí)行統(tǒng)計(jì)分析:execute dbms_stats.gather_schema_stats(ownname =SCOTT,cascade=true); 對(duì)單個(gè)表執(zhí)行統(tǒng)計(jì)分析:EXECUTE dbms_stats.gather_table_stats (ownname=SCOTT, tabname=EMP,estimate_percent=50,cascade=true). 從Oracle Database 10g開(kāi)始，Or

6、acle在建庫(kù)后就默認(rèn)創(chuàng)建了一個(gè)名為GATHER_STATS_JOB的定時(shí)任務(wù)，用于自動(dòng)收集CBO的統(tǒng)計(jì)信息,調(diào)用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統(tǒng)計(jì)信息.默認(rèn)情況下在工作日晚上10：00-6：00和周末全天開(kāi)啟.一起運(yùn)行的還有另外一個(gè)Job:AUTO_SPACE_ADVISOR_JOB .可以通過(guò)以下查詢這個(gè)JOB的運(yùn)行情況： select * from Dba_Scheduler_Jobs where JOB_NAME =GATHER_STATS_JOB .關(guān)閉自動(dòng)統(tǒng)計(jì)功能：SQL exec BMS_SCHEDULER.DISABLE(

7、GATHER_STATS_JOB); Oracle 索引介紹Oracle索引簡(jiǎn)介索引是建立在表的一列或多個(gè)列上的輔助對(duì)象，目的是加快訪問(wèn)表中的數(shù)據(jù)(加快查詢)； 索引由根節(jié)點(diǎn)、分支節(jié)點(diǎn)和葉子節(jié)點(diǎn)組成，上級(jí)索引塊包含下級(jí)索引塊的索引數(shù)據(jù)，葉節(jié)點(diǎn)包含索引數(shù)據(jù)和確定行實(shí)際位置的rowid。查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過(guò)USER_INDEXES的方法來(lái)檢索模式(schema)的索引。訪問(wèn)USER_IND_COLUMNS視圖可得到一個(gè)給定表中被索引的特定列。 通過(guò)每個(gè)行的ROWID，索引Oracle提供了訪問(wèn)單行數(shù)據(jù)的能力。ROWID其實(shí)就是直接指向單獨(dú)行的線路圖。

8、 索引分類(lèi)索引分類(lèi)l邏輯上: 單列索引, 多列索引, 唯一索引,非惟一索引。l物理上: B*-Tree索引，反向索引，位圖索引。單列索引和復(fù)合索引單列索引和復(fù)合索引l單列索引是基于單個(gè)列所建立的索引。多列索引是基于兩列或多列所建立的索引。l單列索引 Create index emp_ind1 on emp(ename);l復(fù)合索引 Create index emp_ind2 on emp(ename,job); 惟一索引和非惟一索引惟一索引和非惟一索引l惟一索引是索引列值不能重復(fù)的索引。l非惟一索引是索引列值可以重復(fù)的索引。無(wú)論是惟一索引還是非惟一索引，索引列都允許NULL。B*-tree索引

9、索引lB*Tree索引是最常見(jiàn)的索引結(jié)構(gòu)，默認(rèn)建立的索引就是這種類(lèi)型的索引。B*Tree索引在檢索高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指該列有很多不同的值）時(shí)提供了最好的性能。當(dāng)取出的行數(shù)占總行數(shù)比例較小時(shí)B-Tree索引比全表檢索提供了更有效的方法。但當(dāng)檢查的范圍超過(guò)表的10%時(shí)就不能提高取回?cái)?shù)據(jù)的性能。B-Tree索引是基于二叉樹(shù)的，由分支塊（branch block）和葉塊（leaf block）組成。在樹(shù)結(jié)構(gòu)中，位于最底層底塊被稱(chēng)為葉塊，包含每個(gè)被索引列的值和行所對(duì)應(yīng)的rowid。在葉節(jié)點(diǎn)的上面是分支塊，用來(lái)導(dǎo)航結(jié)構(gòu)，包含了索引列（關(guān)鍵字）范圍和另一索引塊的地址 .l 創(chuàng)建索引： 1: cre

10、ate index STUDENT_IND_name on STUDENT(NAME) tablespace USERS ; 2:create index STUDENT_IND_name on STUDENT(NAME,AGE) tablespace USERS ;B*-tree索引索引B*-tree索引索引l假設(shè)我們要找索引中值為80的行，從索引樹(shù)的最上層入口開(kāi)始，定位到大于等于50，然后往左找，找到第2個(gè)分支塊，定位為75100，最后再定位到葉塊上，找到80所對(duì)應(yīng)的rowid，然后根據(jù)rowid去讀取數(shù)據(jù)塊獲取數(shù)據(jù)。如果查詢條件是范圍選擇的，比如where column 20 and c

11、olumn value,因?yàn)樵谒饕娜~塊中索引碼沒(méi)有分類(lèi)，所以不能通過(guò)搜索相鄰葉塊完成區(qū)域掃描。注意：只有索引中的值是逆向的，表中的值保持不變。位圖索引位圖索引l位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級(jí)鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數(shù)為一個(gè)很小的值，接近零），例如又一個(gè)“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬(wàn)條記錄，那么3/3000000約等于0，這種情況下最適合用位圖索引。l 創(chuàng)建位圖索引: create bitmap index inx_bitmap_emp on emp(sex);位圖索引的格式

12、位圖索引的格式 l行值 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 位圖索引位圖索引l如果搜索where gender=Male,要統(tǒng)計(jì)性別是”Male”的列行數(shù)的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where gender=Male or gender=Female的列的行數(shù)的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會(huì)

13、用內(nèi)部的轉(zhuǎn)換函數(shù)將位圖中的相關(guān)信息轉(zhuǎn)換成rowid來(lái)訪問(wèn)數(shù)據(jù)塊。 聚簇聚簇l聚簇是根據(jù)碼值找到數(shù)據(jù)的物理存儲(chǔ)位置，從而達(dá)到快速檢索數(shù)據(jù)的目的。聚簇索引的順序就是數(shù)據(jù)的物理存儲(chǔ)順序，葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。非聚簇索引的順序與數(shù)據(jù)物理排列順序無(wú)關(guān)，葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過(guò)有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。一個(gè)表最多只能有一個(gè)聚簇索引。 使用使用 Oracle 聚簇索引聚簇索引 l在Oracle當(dāng)中，聚簇不是索引的組織形式，而是表的組織形式。多用于表之間的連接字段。 例:dept(deptno, dnma,e loc) 和表 emp(empno, ename, job, mgr, . deptno), 常

14、在deptno上進(jìn)行連接,可以針對(duì)deptno字段建立聚簇，然后建立基于該聚簇的索引，并讓兩個(gè)表都使用上該聚簇。 Oracle 聚簇索引聚簇索引lCREATE CLUSTER lpersonnel( department_number NUMBER(2) )l SIZE 512 STORAGE (INITIAL 100K NEXT 50K); l CREATE TABLE emp (l empno NUMBER l PRIMARY KEY,l ename VARCHAR2(10) NOT NULL l CHECK (ename = UPPER(ename),l job VARCHAR2(9),

15、 l mgr NUMBER ,l comm NUMBER(9,0) DEFAULT NULL, l deptno NUMBER(2) NOT NULL )l CLUSTER personnel (deptno);l l CREATE TABLE dept (l deptno NUMBER(2),l dname VARCHAR2(9),l loc VARCHAR2(9)l CLUSTER personnel (deptno);l l CREATE INDEX idx_personnel ON CLUSTER personnel; Oracle 聚簇索引聚簇索引l這樣可以讓兩個(gè)表同時(shí)用上聚簇索引。

16、保證兩個(gè)表的記錄按照depno值盡量存放到同一個(gè)物理塊當(dāng)中。 使用索引的一些規(guī)則使用索引的一些規(guī)則1.索引對(duì)大表最有用，不要在小表上加索引.2.為每個(gè)表中的主碼指定一個(gè)唯一索引。3.索引對(duì)于那些頻繁出現(xiàn)在SQL命令中的where子句中的列最有用，不管這些列在選擇中用來(lái)限定行還是為了表連接。4.當(dāng)一個(gè)屬性中存在很多不同的值時(shí)，可以使用索引。Oracle建議當(dāng)一個(gè)屬性中有少于30個(gè)不同值時(shí)，索引不是很有用，當(dāng)屬性中有100或更多不同值時(shí)索引就很明顯地有用了。相似地，只有當(dāng)使用索引進(jìn)行查詢的結(jié)果不超過(guò)文件中所有記錄總數(shù)的20%時(shí)，使用索引才有幫助。5.檢查你的DBMS對(duì)索引的限制，即便要在每個(gè)表允許

17、的索引個(gè)數(shù)上。許多系統(tǒng)不超過(guò)16個(gè)索引而且限制每個(gè)索引鍵值的大小。對(duì)一個(gè)表創(chuàng)建的索引數(shù)一般不超過(guò)5個(gè).7. 對(duì)于包含空值的屬性建立索引時(shí)要小心，在很多DBMS里有空值的行不能在索引中作為參照。8. 時(shí)常需要做刪除、更新、插入操作的表不要?jiǎng)?chuàng)建索引.9. 將表和索引建立在不同的表空間內(nèi)(TABLESPACES). 不要將不屬于ORACLE內(nèi)部系統(tǒng)的對(duì)象存放到SYSTEM表空間里. 同時(shí),確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤(pán)上.減少I(mǎi)/O競(jìng)爭(zhēng).SQL優(yōu)化的一些規(guī)則:( 有些規(guī)則只有在基于規(guī)則的優(yōu)化器里有效).1選擇最有效率的表名順序：選擇最有效率的表名順序：1.把記錄少的表放在from子句的最后

18、面一個(gè)表.2.如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表.原因：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí), 會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行排序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中

19、合適記錄進(jìn)行合并. 2 WHERE子句中的連接順序 ： ORACLE采用自右向左的順序解析WHERE子句, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾. 3.SELECT子句中避免使用 * ORACLE在解析的過(guò)程中, 需要通過(guò)查詢數(shù)據(jù)字典將* 依次轉(zhuǎn)換成所有的列名.4. 使用表的別名(Alias) 當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤. (Column歧義指的是由于SQL中不同的表具有相同的Column名,當(dāng)SQL語(yǔ)句中出現(xiàn)這個(gè)Column時(shí),SQL解析器無(wú)法

20、判斷這個(gè)Column的歸屬) 5.減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù): 當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見(jiàn), 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.6.(可能的話)用TRUNCATE替代DELETE. 當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有COMMIT事務(wù),ORACLE會(huì)將數(shù)據(jù)恢復(fù)到執(zhí)行刪除命令之前的狀況. 而當(dāng)運(yùn)用TRUNCATE時(shí), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù)

21、.因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. (TRUNCATE只在刪除全表里的記錄時(shí)適用,TRUNCATE是DDL不是DML) 7. (可能的話)使用COMMIT 只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少: COMMIT所釋放的資源: a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息. b. 被程序語(yǔ)句獲得的鎖 c. redo log buffer 中的空間 d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi) 8.(可能的話)用Where子句替換HAVING子句 盡量少使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集

22、進(jìn)行過(guò)濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷(xiāo). 9.(某些情況下)可以用EXISTS替代IN . NOT EXISTS替代NOT IN 性能比較: 1.Select * from t1 where x in ( select y from t2) 2.select * from t1 where exists ( select 1 from t2 where t2. y = t1.x ) 當(dāng)t1記錄比較少,t2比較大時(shí)適合用exists(exists大部分情況會(huì)利用到index),當(dāng)子查詢記錄集很小時(shí)用in比較合適. 原因分析： 1

23、.Select * from T1 where x in ( select y from T2 ) 執(zhí)行的過(guò)程相當(dāng)于: select * from t1, ( select distinct y from t2 ) t3 where t1.x = t3.y; 2. select * from t1 where exists ( select 1 from t2 where t2.y = t1.x )執(zhí)行的過(guò)程相當(dāng)于:for x in ( select * from t1 ) loop if ( exists ( select 1 from t2 where t2.y = t1.x ) then

24、 OUTPUT THE RECORD end ifend loop這樣表 T1 要被完全掃描一遍 . 所以可以得出結(jié)論:當(dāng)t1記錄比較少,t2比較大時(shí)適合用exists(exists大部分情況會(huì)利用到index),當(dāng)子查詢記錄集很小時(shí)用in比較合適.10.用表連接替換EXISTS改進(jìn)第9打優(yōu)化規(guī)則的例子.11.用EXISTS替換DISTINCT EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊在子查詢的條件一旦滿足后 立刻返回結(jié)果. DISTINCT會(huì)先進(jìn)行排序,然后會(huì)根據(jù)排序后的順序去除相同的行.12.使用顯式的游標(biāo)(CURSOR) 使用隱式的游標(biāo),將會(huì)執(zhí)行兩次操作. 第一次檢索記錄,

25、第二次檢查T(mén)OO MANY ROWS 這個(gè)exception . 而顯式游標(biāo)不執(zhí)行第二次操作. 11-例： 1.(低效)SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO ;2.高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); l13.用索引提高效率 通常,通過(guò)索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Updat

26、e語(yǔ)句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 除了那些LONG或LONG RAW數(shù)據(jù)類(lèi)型, 你可以索引幾乎所有的列. 在大型表中使用索引特別有效. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來(lái) 存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤(pán)I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢. 定期的重構(gòu)索引是有必要的. ALTER INDEX REBUILD Oracle索引優(yōu)化

27、規(guī)則索引優(yōu)化規(guī)則：1. like件中不要以通配符(WILDCARD)開(kāi)始,否則索引將不被采用. 例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN; 2.避免在索引列上使用計(jì)算或改變索引列的類(lèi)型或使用!=及 例: SELECT FROM DEPT WHERE SAL * 12 25000; SELECT FROM EMP WHERE EMP_TYPE=to_char(123); select . Where ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA; select where empno!=8888 ;3.避免在

28、索引列上使用NOT .4.用=替代 . 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3 兩者的區(qū)別在于, 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄. l5.用UNION替換OR (適用于索引列) l通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION