Oracle中的索引詳解

1、一、 ROWID的概念存儲(chǔ)了row在數(shù)據(jù)文件中的具體位置：64位 編碼的數(shù)據(jù)，A-Z, a-z, 0-9, +, 和 /，row在數(shù)據(jù)塊中的存儲(chǔ)方式SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;比 如：OOOOOOFFFBBBBBBRRROOOOOO：data object number, 對(duì)應(yīng)dba_objects.data_object_idFFF：file#, 對(duì)應(yīng)v$datafile.file#BBBBBB：block#RRR：row#Dbms_rowid包SELECT dbms_rowid.row

2、id_block_number('AAAGFqAABAAAIWEAAA' from dual;具 體到特定的物理文件二、 索引的概念1、 類似書的目錄結(jié)構(gòu)2、 Oracle 的“索引”對(duì)象，與表關(guān)聯(lián)的可選對(duì)象，提高SQL查詢語句的速度3、 索引直接指向包含所查詢值的行的位置，減少磁盤I/O4、 與所索引的表是相互獨(dú)立的物理結(jié)構(gòu)5、 Oracle 自動(dòng)使用并維護(hù)索引，插入、刪除、更新表后，自動(dòng)更新索引6、 語法：CREATE INDEX index ON table (column, column.;7、 B-tree結(jié)構(gòu)(非bitmap：一了解索引的工作原理：表：emp目標(biāo)：查

3、詢Frank的工資salary建立索 引：create index emp_name_idx on emp(name; 試驗(yàn)測(cè)試索引的作用：1. 運(yùn)行/rdbms/admin/utlxplan 腳本2. 建立測(cè)試表create table t as select * from dba_objects;insert into t select * from t;create table indextableas select rownum id,owner,object_name,subobject_name,object_id,data_object_id,object_type,c

4、reatedfrom t;3. set autotrace trace explain4. set timing on5. 分析表，可以得到cost6. 查詢 object_name=DBA_INDEXES7. 在object_name列上建立索引8. 再查詢思考索引的代價(jià)：插入，更新三、 唯一索引1、 何時(shí)創(chuàng)建：當(dāng)某列任意兩行的值都不相同2、 當(dāng)建立Primary Key(主鍵或者Unique constraint(唯一約束時(shí)，唯一索引將被自動(dòng)建立3、 語法：CREATE UNIQUE INDEX index ON table (column;4、 演示四、 組合索引1、 何時(shí)創(chuàng)建：當(dāng)兩個(gè)或

5、多個(gè)列經(jīng)常一起出現(xiàn)在where條件中時(shí)，則在這些列上同時(shí)創(chuàng)建組合索引2、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的最前面3、 演示(組合列，單獨(dú)列五、 位圖索引1、 何時(shí)創(chuàng)建：列中有非常多的重復(fù)的值時(shí)候。例如某列保存了 “性別”信息。Where 條件中包含了很多OR操作符。較少的update操作，因?yàn)橐鄳?yīng)的跟新所有的bitmap2、 結(jié)構(gòu)：位圖索引使用位圖作為鍵值，對(duì)于表中的每一數(shù)據(jù)行位圖包含了TRUE(1、FALSE(0、或NULL值。3、 優(yōu)點(diǎn)：位圖以一種壓縮格式存放，因此占用的磁盤空間比標(biāo)準(zhǔn)索引要小得多4、 語法：CREATE BITMAP INDEX

6、 index ON table (column, column.;5、 掩飾：create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC'分析，查找，建立索引，查找六、 基于函數(shù)的索引   1、 何時(shí)創(chuàng)建：在WHERE條件語句中包含函數(shù)或者表達(dá)式時(shí)2、 函數(shù)包括：算數(shù)表達(dá)式、PL/SQL函數(shù)、程序包函數(shù)、SQL函數(shù)、用戶自定義函數(shù)。3、 語法：CREATE INDEX index ON table (FUNCTION(column;4、 演

7、示必須要分析表，并且 query_rewrite_enabled=TRUE或者使用提示/*+ INDEX(ic_index*/七、 反向鍵索引目的：比如索引值是一個(gè)自動(dòng)增長的列：多個(gè)用戶對(duì)集中在少數(shù)塊上的索引行進(jìn)行修改，容易引起資源的爭用，比如對(duì)數(shù)據(jù)塊的等待。此時(shí)建立反向索 引。性能問題：語法：重建為標(biāo)準(zhǔn)索引：反之不行八、 鍵壓縮索引比如表landscp的數(shù)據(jù)如下：site feature jobBritten Park, Rose Bed 1, PruneBritten Park, Rose Bed 1, MulchBritten Park, Rose Bed 1,SprayBritten

8、Park, Shrub Bed 1, MulchBritten Park, Shrub Bed 1, WeedBritten Park, Shrub Bed 1, Hoe查詢時(shí)，以上3列均在where條件中同時(shí)出現(xiàn)，所以建立基于以上3列的組合索引。但是發(fā)現(xiàn)重復(fù)值很多，所以考慮壓縮特性。Create index zip_idxon landscp(site, feature, jobcompress 2;將索引項(xiàng)分成前綴(prefix和后綴(postfix兩部分。前兩項(xiàng)被放置到前綴部分。Prefix 0: Britten Park, Rose Bed 1Prefix 1: Britten Park, Shrub Bed 1實(shí)際所以的結(jié)構(gòu)為：0 Prune0 Mulch0 Spray1 Mulch1 Weed1 Hoe特點(diǎn)：組合索引的前綴部分具 有非選擇性時(shí)，考慮使用壓縮。減少I/O,增加性能。九、 索引組織表(IOT將表中的數(shù)據(jù)按照索 引的結(jié)構(gòu)存儲(chǔ)在索引中，提高查詢速度。犧牲插入更新的性能，換取查詢 性能。通常用于數(shù)據(jù)倉庫，提供大量的查詢，極少的插入修改工作。必須指定主鍵。插入數(shù)據(jù)時(shí)，會(huì)根據(jù)主鍵列進(jìn)行B樹索引排序，寫入