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

1、Oracle 優(yōu)化器介紹.Oracle索引介紹.SQL優(yōu)化規(guī)則介紹.索引優(yōu)化規(guī)則介紹.Oracle優(yōu)化器模式優(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-Based Optimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-Based Optimization,簡稱為CBO). RBO方式優(yōu)化器在分析SQL語

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

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

4、e;可以在init文件中對整個instance的所有會話設(shè)置.也可以單獨對某個會話設(shè)置： SQLALTER SESSION SET optimizer_mode= FIRST_ROWS; 查看統(tǒng)計信息： 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;人工進行統(tǒng)計：對某一個用戶下的所有表和索引執(zhí)行統(tǒng)計分析:execute dbms_stats.gather_schema_stats(ownname =SCOTT,cascade=true); 對單個表執(zhí)行統(tǒng)計分析:EXECUTE dbms_stats.gather_table_stats (ownname=SCOTT, tabname=EMP,estimate_percent=50,cascade=true). 從Oracle Database 10g開始，Or

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

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

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

9、索引lB*Tree索引是最常見的索引結(jié)構(gòu)，默認建立的索引就是這種類型的索引。B*Tree索引在檢索高基數(shù)數(shù)據(jù)列（高基數(shù)數(shù)據(jù)列是指該列有很多不同的值）時提供了最好的性能。當取出的行數(shù)占總行數(shù)比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當檢查的范圍超過表的10%時就不能提高取回數(shù)據(jù)的性能。B-Tree索引是基于二叉樹的，由分支塊（branch block）和葉塊（leaf block）組成。在樹結(jié)構(gòu)中，位于最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應(yīng)的rowid。在葉節(jié)點的上面是分支塊，用來導(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的行，從索引樹的最上層入口開始，定位到大于等于50，然后往左找，找到第2個分支塊，定位為75100，最后再定位到葉塊上，找到80所對應(yīng)的rowid，然后根據(jù)rowid去讀取數(shù)據(jù)塊獲取數(shù)據(jù)。如果查詢條件是范圍選擇的，比如where column 20 and c

11、olumn value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜索相鄰葉塊完成區(qū)域掃描。注意：只有索引中的值是逆向的，表中的值保持不變。位圖索引位圖索引l位圖索引主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)，不支持行級鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數(shù)為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那么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)計性別是”Male”的列行數(shù)的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where gender=Male or gender=Female的列的行數(shù)的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會

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

14、在deptno上進行連接,可以針對deptno字段建立聚簇，然后建立基于該聚簇的索引，并讓兩個表都使用上該聚簇。 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這樣可以讓兩個表同時用上聚簇索引。

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

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

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

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

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

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

22、進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷. 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 ) 當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當子查詢記錄集很小時用in比較合適. 原因分析： 1

23、.Select * from T1 where x in ( select y from T2 ) 執(zhí)行的過程相當于: 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í)行的過程相當于: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é)論:當t1記錄比較少,t2比較大時適合用exists(exists大部分情況會利用到index),當子查詢記錄集很小時用in比較合適.10.用表連接替換EXISTS改進第9打優(yōu)化規(guī)則的例子.11.用EXISTS替換DISTINCT EXISTS 使查詢更為迅速,因為RDBMS核心模塊在子查詢的條件一旦滿足后 立刻返回結(jié)果. DISTINCT會先進行排序,然后會根據(jù)排序后的順序去除相同的行.12.使用顯式的游標(CURSOR) 使用隱式的游標,將會執(zhí)行兩次操作. 第一次檢索記錄,

25、第二次檢查TOO MANY ROWS 這個exception . 而顯式游標不執(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.用索引提高效率 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當ORACLE找出執(zhí)行查詢和Updat

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

27、規(guī)則索引優(yōu)化規(guī)則：1. like件中不要以通配符(WILDCARD)開始,否則索引將不被采用. 例:SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN; 2.避免在索引列上使用計算或改變索引列的類型或使用!=及 例: 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將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄. l5.用UNION替換OR (適用于索引列) l通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION