1、Oracle 索引索引RDM 吳桂林索引的概念 索引是與表關(guān)聯(lián)的可選獨(dú)立對(duì)象,提高查詢(xún)速度 通過(guò)默認(rèn)的鍵值排序來(lái)取代全表掃描，提高查詢(xún)效率 索引是以二叉樹(shù)的機(jī)構(gòu)存儲(chǔ)的，葉節(jié)點(diǎn)中存儲(chǔ)的是表中數(shù)據(jù)行的rowid（數(shù)據(jù)的物理地址）B樹(shù)索引位圖索引索引優(yōu)缺點(diǎn) 優(yōu)點(diǎn)：提高查詢(xún)速度，含索引條件的select，update，delete；提高分組排序的速度 缺點(diǎn)：創(chuàng)建和維護(hù)索引需要耗費(fèi)時(shí)間，隨著數(shù)據(jù)量的增加而增加；索引需要物理空間，對(duì)表中的數(shù)據(jù)進(jìn)行insert，update，delete時(shí)，索引要進(jìn)行動(dòng)態(tài)維護(hù)索引的類(lèi)型 1. B_tree單列索引 2. B_tree復(fù)合索引 3.位圖索引 4.函數(shù)索引 5.

2、反向索引 6.分區(qū)索引和全局索引B_tree單列索引 基于單個(gè)列創(chuàng)建的B_TREE索引，是oracle默認(rèn)的索引類(lèi)型 由于索引是通過(guò)rowid來(lái)訪(fǎng)問(wèn)數(shù)據(jù)的，當(dāng)范圍掃描的數(shù)據(jù)占總數(shù)據(jù)量的10%以上時(shí)，使用索引的消耗不如全表掃描（全表掃描是多塊讀取，索引掃描每次有兩次IO，一次對(duì)索引塊，一次對(duì)數(shù)據(jù)）示例1select /*+full(a)*/a.passwd from t_userinfo a 2 where phonenumber =and phonenumber select /*+index(a pk_t_userinfo)*/a.passwd from t_use

3、rinfo a 2 where phonenumber =and phonenumber ”“ select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_LOCAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_LOCAL P1 10000 USABLEID_LOCAL P2 20000 USABLEID_LOCAL P3 MAXVALUE USABLE全局分區(qū)索引 全局分區(qū)索引

4、是對(duì)整個(gè)分區(qū)表建立的索引，然后由oracle對(duì)索引進(jìn)行分區(qū)，索引分區(qū)與分區(qū)表之間不是簡(jiǎn)單的一對(duì)一關(guān)系全局分區(qū)索引刪除id_local索引drop index id_local;重新在ID列上創(chuàng)建一個(gè)GLOBAL的索引create index id_global on test(id) global;SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;no rows selectedSQL select INDEX_NAME,INDEX_

5、TYPE,TABLE_NAME from dba_indexes where index_name=ID_GLOBAL;INDEX_NAME INDEX_TYPE TABLE_NAME- - -ID_GLOBAL NORMAL TEST從上面可以看出，它此時(shí)是個(gè)普通索引。dba_ind_partitions里根本就沒(méi)有記錄。全局分區(qū)索引SQLcreate index i_id_global on test(data) global partition by range(id) ( partition p1 values less than (10000) , partition p2 valu

6、es less than (MAXVALUE) ); partition by range(id) *ERROR at line 2:ORA-14038: GLOBAL partitioned index must be prefixed此錯(cuò)誤表示GLOBAL的索引必須是prefixed，即索引分區(qū)的列，必須是其基表的分區(qū)列。SQLcreate index id_global on test(id) global partition by range(id) ( partition p1 values less than (10000) , partition p2 values less t

7、han (MAXVALUE) );Index created.SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_GLOBAL P1 10000 USABLEID_GLOBAL P2 MAXVALUE USABLE典型索引失效1、在索引列上使用函數(shù)。如SUBSTR,DECODE,INSTR等，對(duì)索引列進(jìn)行運(yùn)算.需要建立函數(shù)索引就可以

8、解決了。2、新建的表還沒(méi)來(lái)得及生成統(tǒng)計(jì)信息，分析一下就好了3、基于cost的成本分析，訪(fǎng)問(wèn)的表過(guò)小，使用全表掃描的消耗小于使用索引。4、使用、not in 、not exist，對(duì)于這三種情況大多數(shù)情況下認(rèn)為結(jié)果集很大，一般大于5%-15%就不走索引而走FTS。5、單獨(dú)的、。6、like %_ 百分號(hào)在前。7、單獨(dú)引用復(fù)合索引里非第一位置的索引列。8、字符型字段為數(shù)字時(shí)在where條件里不添加引號(hào)。9、當(dāng)變量采用的是times變量，而表的字段采用的是date變量時(shí).或相反情況。10、索引失效，可以考慮重建索引，rebuild online。11、B-tree索引 is null不會(huì)走,is n

9、ot null會(huì)走,位圖索引 is null,is not null 都會(huì)走、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會(huì)走。創(chuàng)建索引的注意 1、一般來(lái)說(shuō)，不需要為比較小的表創(chuàng)建索引； 2、即使是大表，如果經(jīng)常需要查詢(xún)的數(shù)據(jù)不超過(guò)10%到15%的話(huà)，那就沒(méi)有必要為其建立索引的必要。因?yàn)榇藭r(shí)建立索引的開(kāi)銷(xiāo)可能要比性能的改善大的多。這個(gè)比例只是一個(gè)經(jīng)驗(yàn)的數(shù)據(jù)。如果數(shù)據(jù)庫(kù)管理員需要得出一個(gè)比較精確的結(jié)論，那么就需要進(jìn)行測(cè)試分析。 3、如對(duì)于一些重復(fù)內(nèi)容比較少的列，特別是對(duì)于那些定義了唯一約束的列。在這些列上建立索引，往往可以起到非常不錯(cuò)的效果。如對(duì)于一些null值的列與非Nu

10、ll值的列混合情況下，如果用戶(hù)需要經(jīng)常查詢(xún)所有的非Null值記錄的列，則最好為其設(shè)置索引。如果經(jīng)常需要多表連接查詢(xún)，在用與連接的列上設(shè)置索引可以達(dá)到事半功倍的效果。創(chuàng)建索引的注意 4、數(shù)據(jù)庫(kù)管理員，需要隔一段時(shí)間，如一年，對(duì)數(shù)據(jù)庫(kù)的索引進(jìn)行優(yōu)化。該去掉的去掉，該調(diào)整的調(diào)整，以提高數(shù)據(jù)庫(kù)的性能。 5、通常來(lái)說(shuō)，表的索引越多，其查詢(xún)的速度也就越快。但是，表的更新速度則會(huì)降低。這主要是因?yàn)楸淼母?如往表中插入一條記錄)速度，反而隨著索引的增加而增加。這主要是因?yàn)?，在更新記錄的同時(shí)需要更新相關(guān)的索引信息。為此，到底在表中創(chuàng)建多少索引合適，就需要在這個(gè)更新速度與查詢(xún)速度之間取得一個(gè)均衡點(diǎn)。創(chuàng)建索引的注

11、意6、對(duì)于一些數(shù)據(jù)倉(cāng)庫(kù)或者決策型數(shù)據(jù)庫(kù)系統(tǒng)，其主要用來(lái)進(jìn)行查詢(xún)。相關(guān)的記錄往往是在數(shù)據(jù)庫(kù)初始化的時(shí)候倒入。此時(shí)，設(shè)置的索引多一點(diǎn)，可以提高數(shù)據(jù)庫(kù)的查詢(xún)性能。同時(shí)因?yàn)橛涗洸辉趺锤拢运饕容^多的情況下，也不會(huì)影響到更新的速度。即使在起初的時(shí)候需要導(dǎo)入大量的數(shù)據(jù)，此時(shí)也可以先將索引禁用掉。等到數(shù)據(jù)導(dǎo)入完畢后，再啟用索引?？梢酝ㄟ^(guò)這種方式來(lái)減少索引對(duì)數(shù)據(jù)更新的影響。相反，如果那些表中經(jīng)常需要更新記錄，如一些事務(wù)型的應(yīng)用系統(tǒng)，數(shù)據(jù)更新操作是家常便飯的事情。此時(shí)如果在一張表中建立過(guò)多的索引，則會(huì)影響到更新的速度。7、關(guān)于位圖索引。基數(shù)是位圖索引中的一個(gè)基本的定義，它是指數(shù)據(jù)庫(kù)表中某個(gè)字段內(nèi)容中不重復(fù)

12、的數(shù)值。如在員工信息表中的性別字段，一般就只有男跟女兩個(gè)值，所以，其基數(shù)為2;婚姻狀況字段的話(huà)，則其只有已婚、未婚、離婚三種狀態(tài)，其基數(shù)就為3;民族一覽內(nèi)也是只有有限的幾個(gè)值Oracle創(chuàng)建索引的基本規(guī)則選擇索引字段的原則:在WHERE子句中最頻繁使用的字段 聯(lián)接語(yǔ)句中的聯(lián)接字段 選擇高選擇性的字段(如果很少的字段擁有相同值,即有很多獨(dú)特值,則選擇性很好) Oracle在UNIQUE和主鍵字段上自動(dòng)建立索引 在選擇性很差的字段上建索引只有在這個(gè)字段的值分布非常傾斜的情況下才有益(在這種情況下,某一,兩個(gè)字段值比其它字段值少出現(xiàn)很多) 不要在很少獨(dú)特值的字段上建B-TREE索引,在這種情況下,你

13、可以考慮在這些字段上建位圖索引.在聯(lián)機(jī)事務(wù)處理環(huán)境下,并發(fā)性非常高,索引經(jīng)常被修改,所以不應(yīng)該建位圖索引 不要在經(jīng)常被修改的字段上建索引.當(dāng)有UPDATE,DELETE,INSETT操作時(shí),ORACLE除了要更新表的數(shù)據(jù)外,同時(shí)也要更新索引,而且就象更新數(shù)據(jù)一樣,或產(chǎn)生還原和重做條目 不要在有用到函數(shù)的字段上建索引,ORACLE在這種情況,優(yōu)化器不會(huì)用到索引,除非你建立函數(shù)索引 當(dāng)建立索引后,請(qǐng)比較一下索引后所獲得的查詢(xún)性能的提高和UPDATE,DELETE,INSERT操作性能上的損失,比較得失后,再最后決定是否需建立這個(gè)索引 Oracle創(chuàng)建索引的基本規(guī)則復(fù)合索引的優(yōu)點(diǎn):改善選擇性:復(fù)合索

14、引比單個(gè)字段的索引更具選擇性 減少I(mǎi)/O:如果要查詢(xún)的字段剛好全部包含在復(fù)合索引的字段里,則ORACLE只須訪(fǎng)問(wèn)索引,無(wú)須訪(fǎng)問(wèn)表 什么情況下優(yōu)化器會(huì)用到復(fù)合索引呢? (a) 當(dāng)SQL語(yǔ)句的WHERE子句中有用到復(fù)合索引的領(lǐng)導(dǎo)字段時(shí),ORACLE優(yōu)化器會(huì)考慮用到復(fù)合索引來(lái)訪(fǎng)問(wèn). (b) 當(dāng)某幾個(gè)字段在SQL語(yǔ)句的WHERE子句中經(jīng)常通過(guò)AND操作符聯(lián)合在一起使用作為過(guò)濾謂詞,并且這幾個(gè)字段合在一起時(shí)選擇性比各自單個(gè)字段的選擇性要更好時(shí),可 能考慮用這幾個(gè)字段來(lái)建立復(fù)合索引. (c) 當(dāng)有幾個(gè)查詢(xún)語(yǔ)句都是查詢(xún)同樣的幾個(gè)字段值時(shí),則可以考慮在這幾個(gè)字段上建立復(fù)合索引.復(fù)合索引字段排序的原則:確保在W

15、HERE子句中使用到的字段是復(fù)合索引的領(lǐng)導(dǎo)字段 如果某個(gè)字段在WHERE子句中最頻繁使用,則在建立復(fù)合索引時(shí),考慮把這個(gè)字段排在第一位(在CREATE INDEX語(yǔ)句中) 如果所有的字段在WHERE子句中使用頻率相同,則將最具選擇性的字段排在最前面,將最不具選擇性的字段排在最后面 如果所有的字段在WHERE子句中使用頻率相同,如果數(shù)據(jù)在物理上是按某一個(gè)字段排序的,則考慮將這個(gè)字段放在復(fù)合索引的第一位 Oracle創(chuàng)建索引的基本規(guī)則四、建立索引常用的規(guī)則如下表的主鍵、外鍵必須有索引； 數(shù)據(jù)量超過(guò)300的表應(yīng)該有索引； 經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引； 經(jīng)常出現(xiàn)在Where子句

16、中的字段，特別是大表的字段，應(yīng)該建立索引； 索引應(yīng)該建在選擇性高的字段上； 索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引； 復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替： A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段； B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢(xún)是否極少甚至沒(méi)有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引； C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引； D、如果復(fù)合索引所包含的字段超過(guò)3個(gè)，那么仔細(xì)考慮其必要性，考慮減少?gòu)?fù)合的字段； E、如果既有單字段索引

17、，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引； 刪除無(wú)用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響； 不走索引不走索引的幾種情況1. 隱式的類(lèi)型轉(zhuǎn)換例：fnumber是字符型，但是查詢(xún)時(shí)使用數(shù)字型select * from t_lea_waybill where fnumber = 122355722. 符號(hào)的查詢(xún)例：select * from wlbussiness where id 12030001 and id select * from tbl where userid = 100;執(zhí)行計(jì)劃-Plan hash value: 1167568666

18、-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| TBL | 1 | 38 | 2 (0)| 00:00:01 |* 2 | INDEX UNIQUE SCAN | TBLID | 1 | | 1 (0)| 00:00:01 |-從上面的執(zhí)行計(jì)劃可以看出，優(yōu)化器首先是根據(jù)為剛才建立的索引TBLID來(lái)找到100的ROWID，然后根據(jù)ROWID去找到10

19、0所在的行數(shù)據(jù)。示例 index full scan 為上面的表增加一個(gè)聯(lián)合索引，在TBL.NAME和TBL.DEPTNO兩個(gè)列上，SQL如下： CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO); Select NAME,DEPTNO from tbl示例index range scan執(zhí)行下面的語(yǔ)句：ChenZw select * from tbl where userid between 10 and 100;已選擇91行。執(zhí)行計(jì)劃-Plan hash value: 2314926374-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 91 | 3458 | 3 (0)| 00:00:01 | 1 |