在Oracle中合理創(chuàng)建數(shù)據(jù)庫(kù)的索引

在Oracle數(shù)據(jù)庫(kù)中 創(chuàng)建索引雖然比較簡(jiǎn)單 但是要合理的創(chuàng)建索引則比較困難了 筆者認(rèn)為 在創(chuàng)建索引時(shí)要做到三個(gè)適當(dāng) 即在適當(dāng)?shù)谋砩?適當(dāng)?shù)牧猩蟿?chuàng)建適當(dāng)數(shù)量的索引 雖然這可以通過一句話來概括優(yōu)化的索引的基本準(zhǔn)則 但是要做到這一點(diǎn)的話 需要數(shù)據(jù)庫(kù)管理員做出很大的努力 具體的來說 要做到這個(gè)三個(gè)適當(dāng)有如下幾個(gè)要求

創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),扎賚諾爾企業(yè)網(wǎng)站建設(shè),扎賚諾爾品牌網(wǎng)站建設(shè),網(wǎng)站定制,扎賚諾爾網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,扎賚諾爾網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

一 根據(jù)表的大小來創(chuàng)建索引

雖然給表創(chuàng)建索引 可以提高查詢的效率 但是數(shù)據(jù)庫(kù)管理員需要注意的是 索引也需要一定的開銷的 為此并不是說給所有的表都創(chuàng)建索引 那么就可以提高數(shù)據(jù)庫(kù)的性能 這個(gè)認(rèn)識(shí)是錯(cuò)誤的 恰恰相反 如果不管三七二十一 給所有的表都創(chuàng)建了索引 那么其反而會(huì)給數(shù)據(jù)庫(kù)的性能造成負(fù)面的影響 因?yàn)榇藭r(shí)濫用索引的開銷可能已經(jīng)遠(yuǎn)遠(yuǎn)大于由此帶來的性能方面的收益 所以筆者認(rèn)為 數(shù)據(jù)庫(kù)管理員首先需要做到 為合適的表來建立索引 而不是為所有的表建立索引

一般來說 不需要為比較小的表創(chuàng)建索引 如在一個(gè)ERP系統(tǒng)的數(shù)據(jù)庫(kù)中 department表用來存儲(chǔ)企業(yè)部門的信息 一般企業(yè)的部分也就十幾個(gè) 最多不會(huì)超過一百個(gè) 這 條記錄對(duì)于人來說 可能算是比較多了 但是對(duì)于計(jì)算機(jī)來說 這給他塞塞牙縫都還不夠 所以 對(duì)類似的小表沒有必要建立索引 因?yàn)榧词菇⒘怂饕?其性能也不會(huì)得到很大的改善 相反索引建立的開銷 如維護(hù)成本等等 要比這個(gè)要大 也就是說 付出的要比得到的多 顯然違反常理

另外 就是對(duì)于超大的表 也不一定要建立索引 有些表雖然比較大 記錄數(shù)量非常的多 但是此時(shí)為這個(gè)表建立索引并一定的合適 如系統(tǒng)中有一張表 其主要用來保存數(shù)據(jù)庫(kù)中的一些變更信息 往往這些信息只給數(shù)據(jù)庫(kù)管理員使用 此時(shí)為這張表建立索引的話 反而不合適 因?yàn)檫@張表很少用到 只有在出問題的時(shí)候才需要查看 其次其即使查看 需要查詢的紀(jì)錄也不會(huì)很多 可能就是最近一周的更新記錄等等 對(duì)于對(duì)于一些超大的表 建立索引有時(shí)候往往不能夠達(dá)到預(yù)計(jì)的效果 而且在打表上建立索引 其索引的開銷要比普通的表大的多 那么到底是否給大表建立索引呢?筆者認(rèn)為 主要是看兩個(gè)方面的內(nèi)容 首先是需要關(guān)注一下 在這張大表中經(jīng)常需要查詢的記錄數(shù)量 一般來說 如果經(jīng)常需要查詢的數(shù)據(jù)不超過 %到 %的話 那就沒有必要為其建立索引的必要 因?yàn)榇藭r(shí)建立索引的開銷可能要比性能的改善大的多 這個(gè)比例只是一個(gè)經(jīng)驗(yàn)的數(shù)據(jù) 如果數(shù)據(jù)庫(kù)管理員需要得出一個(gè)比較精確的結(jié)論 那么就需要進(jìn)行測(cè)試分析 即數(shù)據(jù)庫(kù)管理員需要測(cè)試一下全表掃描的時(shí)間 看看其是否比建立索引后的查詢時(shí)間要長(zhǎng)或者短 如果是長(zhǎng)的話 則說明有建立索引的必要 但是如果沒有的話 則說明還是全表掃描速度來的快 此時(shí)也就沒有必要建立索引了

總之 在考慮是否該為表建立索引時(shí) 一般來說小表沒有建立索引的必要 而對(duì)于打表的話 則需要進(jìn)行實(shí)際情況實(shí)際分析 簡(jiǎn)單一點(diǎn)的 可以根據(jù)大致的比率來確定 如果要精確一點(diǎn)的 則可以進(jìn)行全表掃描性能分析 以判斷建立索引后是否真的如預(yù)期那樣改善了數(shù)據(jù)庫(kù)性能

二 根據(jù)列的特征來創(chuàng)建索引

列的特點(diǎn)不同 索引創(chuàng)建的效果也不同 數(shù)據(jù)庫(kù)管理員需要了解為哪些列創(chuàng)建索引可以起到事倍功半的效果 同時(shí)也需要了解為哪些列創(chuàng)建索引反而起到的是事倍功半的效果 這有利于他們了解到底給為怎么樣的字段建立索引

根據(jù)筆者的經(jīng)驗(yàn) 往往為如下特征的列創(chuàng)建索引能夠起到比較明顯的效果 如對(duì)于一些重復(fù)內(nèi)容比較少的列 特別是對(duì)于那些定義了唯一約束的列 在這些列上建立索引 往往可以起到非常不錯(cuò)的效果 如對(duì)于一些null值的列與非Null值的列混合情況下 如果用戶需要經(jīng)常查詢所有的非Null值記錄的列 則最好為其設(shè)置索引 如果經(jīng)常需要多表連接查詢 在用與連接的列上設(shè)置索引可以達(dá)到事半功倍的效果

可見 索引設(shè)置的是否恰當(dāng) 不僅跟數(shù)據(jù)庫(kù)設(shè)計(jì)架構(gòu)有關(guān) 而且還跟企業(yè)的經(jīng)濟(jì)業(yè)務(wù)相關(guān) 為此 對(duì)于一些套裝軟件 雖然一開始數(shù)據(jù)庫(kù)管理員已經(jīng)做了索引的優(yōu)化工作 但是隨著后來經(jīng)濟(jì)數(shù)據(jù)的增加 這個(gè)索引的效果會(huì)越來越打折扣 這主要是因?yàn)橛涗浀谋砘绊懙搅怂饕齼?yōu)化的效果 所以筆者建議各位數(shù)據(jù)庫(kù)管理員 即使采用的是大牌軟件公司的套裝軟件 也需要隔一段時(shí)間 如一年 對(duì)數(shù)據(jù)庫(kù)的索引進(jìn)行優(yōu)化 該去掉的去掉 該調(diào)整的調(diào)整 以提高數(shù)據(jù)庫(kù)的性能

如在數(shù)據(jù)庫(kù)中有一張表是用來保存用戶信息的 其中有個(gè)字段身份證號(hào)碼 這是一個(gè)唯一的字段 在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí) 給這個(gè)字段創(chuàng)建了索引 但是當(dāng)這個(gè)數(shù)據(jù)庫(kù)投入使用之后 用戶不怎么輸入用戶的身份證號(hào)碼 而且平時(shí)也基本不按這個(gè)號(hào)碼來進(jìn)行查詢 當(dāng)記錄月來月多時(shí) 這個(gè)身份證號(hào)碼上的索引字段不但不能夠改善數(shù)據(jù)庫(kù)的查詢性能 反而成了雞肋 對(duì)于這些有很多NULL值的列 而且不會(huì)經(jīng)常查詢所有的非NULL值記錄的列 數(shù)據(jù)庫(kù)管理員要下決心 即使清除這些列上的索引

所以說索引的優(yōu)化與調(diào)整是一個(gè)動(dòng)態(tài)的過程 并不是說數(shù)據(jù)庫(kù)設(shè)計(jì)好之后就不需要經(jīng)過調(diào)整 數(shù)據(jù)庫(kù)管理員往往需要根據(jù)記錄的變化情況 來進(jìn)行適當(dāng)?shù)淖兏?以提高索引的效果

三 在一個(gè)表上創(chuàng)建多少索引合適?

雖然說 在表上創(chuàng)建索引的數(shù)量沒有限制 但是決不是越多越好 也就是說 在創(chuàng)建索引這項(xiàng)事情上 + 〉 往往不成立 有時(shí)候 創(chuàng)建索引越多 其可能會(huì)得到適得其反的效果 那么在一個(gè)表上 到底給創(chuàng)建多少索引合適呢?這個(gè)沒有一個(gè)明確的標(biāo)準(zhǔn) 而是需要數(shù)據(jù)庫(kù)管理員根據(jù)實(shí)際的用途以及數(shù)據(jù)庫(kù)中記錄的情況 來進(jìn)行判斷

通常來說 表的索引越多 其查詢的速度也就越快 但是 表的更新速度則會(huì)降低 這主要是因?yàn)楸淼母?如往表中插入一條記錄)速度 反而隨著索引的增加而增加 這主要是因?yàn)?在更新記錄的同時(shí)需要更新相關(guān)的索引信息 為此 到底在表中創(chuàng)建多少索引合適 就需要在這個(gè)更新速度與查詢速度之間取得一個(gè)均衡點(diǎn) 如對(duì)于一些數(shù)據(jù)倉(cāng)庫(kù)或者決策型數(shù)據(jù)庫(kù)系統(tǒng) 其主要用來進(jìn)行查詢 相關(guān)的記錄往往是在數(shù)據(jù)庫(kù)初始化的時(shí)候倒入 此時(shí) 設(shè)置的索引多一點(diǎn) 可以提高數(shù)據(jù)庫(kù)的查詢性能 同時(shí)因?yàn)橛涗洸辉趺锤?所以索引比較多的情況下 也不會(huì)影響到更新的速度 即使在起初的時(shí)候需要導(dǎo)入大量的數(shù)據(jù) 此時(shí)也可以先將索引禁用掉 等到數(shù)據(jù)導(dǎo)入完畢后 再啟用索引 可以通過這種方式來減少索引對(duì)數(shù)據(jù)更新的影響 相反 如果那些表中經(jīng)常需要更新記錄 如一些事務(wù)型的應(yīng)用系統(tǒng) 數(shù)據(jù)更新操作是家常便飯的事情 此時(shí)如果在一張表中建立過多的索引 則會(huì)影響到更新的速度 由于更新操作比較頻繁 所以對(duì)其的負(fù)面影響 要比查詢效率提升要大的多 此時(shí)就需要限制索引的數(shù)量 只在一些必要的字段上建立索引

筆者在平時(shí)數(shù)據(jù)庫(kù)優(yōu)化時(shí) 往往會(huì)根據(jù)這些表的用途來為列設(shè)置索引 可以查詢相關(guān)的動(dòng)態(tài)視圖 看看對(duì)于這張表的操作 是更新操作(包括更新 刪除 插入等等)占的比例大 還是查詢操作占的比例大 當(dāng)過多的索引已經(jīng)影響到更新操作的速度時(shí) 則數(shù)據(jù)庫(kù)管理員就需要先禁用某些索引 以提高數(shù)據(jù)庫(kù)的性能

lishixinzhi/Article/program/Oracle/201311/18407

ORACLE索引提高效率

用索引提高效率

索引是表的一個(gè)概念部分 用來提高檢索數(shù)據(jù)的效率 實(shí)際上 ORACLE使用了一個(gè)復(fù)雜的自平衡B tree結(jié)構(gòu) 通常 通過索引查詢數(shù)據(jù)比全表掃描要快 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時(shí) ORACLE優(yōu)化器將使用索引 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率 另一個(gè)使用索引的好處是 它提供了主鍵(primary key)的唯一性驗(yàn)證

除了那些LONG或LONG RAW數(shù)據(jù)類型 你可以索引幾乎所有的列 通常 在大型表中使用索引特別有效 當(dāng)然 你也會(huì)發(fā)現(xiàn) 在掃描小表時(shí) 使用索引同樣能提高效率

雖然使用索引能得到查詢效率的提高 但是我們也必須注意到它的代價(jià) 索引需要空間來

存儲(chǔ) 也需要定期維護(hù) 每當(dāng)有記錄在表中增減或索引列被修改時(shí) 索引本身也會(huì)被修改 這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理 那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢

定期的重構(gòu)索引是有必要的

ALTER INDEX INDEXNAME REBUILD TABLESPACENAME

索引的操作

ORACLE對(duì)索引有兩種訪問模式

索引唯一掃描 ( INDEX UNIQUE SCAN)

大多數(shù)情況下 優(yōu)化器通過WHERE子句訪問INDEX

例如:

表LODGING有兩個(gè)索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER

SELECT * FROM LODGING

WHERE LODGING = ROSE HILL ;

在內(nèi)部 上述SQL將被分成兩步執(zhí)行 首先 LODGING_PK 索引將通過索引唯一掃描的方式被訪問 獲得相對(duì)應(yīng)的ROWID 通過ROWID訪問表的方式 執(zhí)行下一步檢索

如果被檢索返回的列包括在INDEX列中 ORACLE將不執(zhí)行第二步的處理(通過ROWID訪問表) 因?yàn)闄z索數(shù)據(jù)保存在索引中 單單訪問索引就可以完全滿足查詢結(jié)果

下面SQL只需要INDEX UNIQUE SCAN 操作

SELECT LODGING FROM LODGING WHERE LODGING = ROSE HILL ;

索引范圍查詢(INDEX RANGE SCAN)

適用于兩種情況:

基于一個(gè)范圍的檢索

基于非唯一性索引的檢索

例 :

SELECT LODGING FROM LODGING WHERE LODGING LIKE M% ;

WHERE子句條件包括一系列值 ORACLE將通過索引范圍查詢的方式查詢LODGING_PK 由于索引范圍查詢將返回一組值 它的效率就要比索引唯一掃描低一些

例 :

SELECT LODGING FROM LODGING WHERE MANAGER = BILL GATES ;

這個(gè)SQL的執(zhí)行分兩步 LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值 由于LODGING$MANAGER是一個(gè)非唯一性的索引 數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描

由于SQL返回LODGING列 而它并不存在于LODGING$MANAGER索引中 所以在索引范圍查詢后會(huì)執(zhí)行一個(gè)通過ROWID訪問表的操作

WHERE子句中 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開始 索引將不被采用

SELECT LODGING FROM LODGING WHERE MANAGER LIKE ％HANMAN ;

在這種情況下 ORACLE將使用全表掃描

基礎(chǔ)表的選擇

基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問) 根據(jù)優(yōu)化器的不同 SQL語句中基礎(chǔ)表的選擇是不一樣的

如果你使用的是CBO (COST BASED OPTIMIZER) 優(yōu)化器會(huì)檢查SQL語句中的每個(gè)表的物理大小 索引的狀態(tài) 然后選用花費(fèi)最低的執(zhí)行路徑

如果你用RBO (RULE BASED OPTIMIZER) 并且所有的連接條件都有索引對(duì)應(yīng) 在這種情況下 基礎(chǔ)表就是FROM 子句中列在最后的那個(gè)表

舉例:

SELECT A NAME B MANAGER FROM　WORKER A LODGING B

WHERE　A LODGING = B LODING;

由于LODGING表的LODING列上有一個(gè)索引 而且WORKER表中沒有相比較的索引 WORKER表將被作為查詢中的基礎(chǔ)表

多個(gè)平等的索引

當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí) ORACLE會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并 檢索出僅對(duì)全部索引有效的記錄

在ORACLE選擇執(zhí)行路徑時(shí) 唯一性索引的等級(jí)高于非唯一性索引 然而這個(gè)規(guī)則只有

當(dāng)WHERE子句中索引列和常量比較才有效 如果索引列和其他表的索引類相比較 這種子句在優(yōu)化器中的等級(jí)是非常低的

如果不同表中兩個(gè)想同等級(jí)的索引將被引用 FROM子句中表的順序?qū)Q定哪個(gè)會(huì)被率先使用 FROM子句中最后的表的索引將有最高的優(yōu)先級(jí)

如果相同表中兩個(gè)想同等級(jí)的索引將被引用 WHERE子句中最先被引用的索引將有最高的優(yōu)先級(jí)

舉例:

DEPTNO上有一個(gè)非唯一性索引 EMP_CAT也有一個(gè)非唯一性索引

SELECT ENAME FROM EMP WHERE DEPT_NO = AND EMP_CAT = A ;

這里 DEPTNO索引將被最先檢索 然后同EMP_CAT索引檢索出的記錄進(jìn)行合并 執(zhí)行路徑如下:

TABLE ACCESS BY ROWID ON EMP AND EQUAL INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

等式比較和范圍比較

當(dāng)WHERE子句中有索引列 ORACLE不能合并它們 ORACLE將用范圍比較

舉例:

DEPTNO上有一個(gè)非唯一性索引 EMP_CAT也有一個(gè)非唯一性索引

SELECT ENAME FROM EMP WHERE DEPTNO AND EMP_CAT = A ;

這里只有EMP_CAT索引被用到 然后所有的記錄將逐條與DEPTNO條件進(jìn)行比較 執(zhí)行路徑如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON CAT_IDX

不明確的索引等級(jí)

當(dāng)ORACLE無法判斷索引的等級(jí)高低差別 優(yōu)化器將只使用一個(gè)索引 它就是在WHERE子句中被列在最前面的

舉例:

DEPTNO上有一個(gè)非唯一性索引 EMP_CAT也有一個(gè)非唯一性索引

SELECT ENAME FROM EMP WHERE DEPTNO AND EMP_CAT A ;

這里 ORACLE只用到了DEPT_NO索引 執(zhí)行路徑如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

我們來試一下以下這種情況:

SQL select index_name uniqueness from user_indexes where table_name = EMP ;

INDEX_NAME UNIQUENES

EMPNO UNIQUE

EMPTYPE NONUNIQUE

SQL select * from emp where empno = and emp_type = A ;

no rows selected

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE

TABLE ACCESS (BY INDEX ROWID) OF EMP

INDEX (RANGE SCAN) OF EMPTYPE (NON UNIQUE)

雖然EMPNO是唯一性索引 但是由于它所做的是范圍比較 等級(jí)要比非唯一性索引的等式比較低!

強(qiáng)制索引失效

如果兩個(gè)或以上索引具有相同的等級(jí) 你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(gè)(通過它 檢索出的記錄數(shù)量少)

舉例:

SELECT ENAME FROM EMP WHERE EMPNO =

AND DEPTNO + = /*DEPTNO上的索引將失效*/

AND EMP_TYPE || = A /*EMP_TYPE上的索引將失效*/

這是一種相當(dāng)直接的提高查詢效率的辦法 但是你必須謹(jǐn)慎考慮這種策略 一般來說 只有在你希望單獨(dú)優(yōu)化幾個(gè)SQL時(shí)才能采用它

這里有一個(gè)例子關(guān)于何時(shí)采用這種策略

假設(shè)在EMP表的EMP_TYPE列上有一個(gè)非唯一性的索引而EMP_CLASS上沒有索引

SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS = X ;

優(yōu)化器會(huì)注意到EMP_TYPE上的索引并使用它 這是目前唯一的選擇 如果 一段時(shí)間以后 另一個(gè)非唯一性建立在EMP_CLASS上 優(yōu)化器必須對(duì)兩個(gè)索引進(jìn)行選擇 在通常情況下 優(yōu)化器將使用兩個(gè)索引并在他們的結(jié)果集合上執(zhí)行排序及合并 然而 如果其中一個(gè)索引（EMP_TYPE）接近于唯一性而另一個(gè)索引（EMP_CLASS）上有幾千個(gè)重復(fù)的值 排序及合并就會(huì)成為一種不必要的負(fù)擔(dān) 在這種情況下 你希望使優(yōu)化器屏蔽掉EMP_CLASS索引

用下面的方案就可以解決問題

SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS|| = X ;

避免在索引列上使用計(jì)算．

WHERE子句中 如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．

舉例:

低效

SELECT … FROM DEPT WHERE SAL * ;

高效:

SELECT … FROM DEPT WHERE SAL / ;

自動(dòng)選擇索引

如果表中有兩個(gè)以上（包括兩個(gè)）索引 其中有一個(gè)唯一性索引 而其他是非唯一性．

在這種情況下 ORACLE將使用唯一性索引而完全忽略非唯一性索引．

舉例:

SELECT ENAME FROM EMP WHERE EMPNO =

AND DEPTNO = ;

這里 只有EMPNO上的索引是唯一性的 所以EMPNO索引將用來檢索記錄．

TABLE ACCESS BY ROWID ON EMP

INDEX UNIQUE SCAN ON EMP_NO_IDX

避免在索引列上使用NOT

通常 　我們要避免在索引列上使用NOT NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的

影響 當(dāng)ORACLE 遇到 NOT 他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描

舉例:

低效: (這里 不使用索引)

SELECT … FROM DEPT WHERE DEPT_CODE NOT = ;

高效: (這里 使用了索引)

SELECT … FROM DEPT WHERE DEPT_CODE ;

需要注意的是 在某些時(shí)候 ORACLE優(yōu)化器會(huì)自動(dòng)將NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作符

NOT to =

NOT = to

NOT to =

NOT = to

SQL select * from emp where NOT empno ;

no rows selected

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE

TABLE ACCESS (BY INDEX ROWID) OF EMP

INDEX (RANGE SCAN) OF EMPNO (UNIQUE)

SQL select * from emp where empno = ;

no rows selected

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE

TABLE ACCESS (BY INDEX ROWID) OF EMP

INDEX (RANGE SCAN) OF EMPNO (UNIQUE)

兩者的效率完全一樣 也許這符合作者關(guān)于 在某些時(shí)候 ORACLE優(yōu)化器會(huì)自動(dòng)將NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作符 的觀點(diǎn)．

用=替代

如果DEPTNO上有一個(gè)索引

高效:

SELECT * FROM EMP WHERE DEPTNO =

低效:

SELECT * FROM EMP WHERE DEPTNO

lishixinzhi/Article/program/Oracle/201311/17710

Oracle索引技術(shù)之如何建立最佳索引

怎樣建立最佳索引? 1、明確地創(chuàng)建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 ); 2、創(chuàng)建基于函數(shù)的索引 常用與UPPER、LOWER、TO_CHAR(date)等函數(shù)分類上，例： create index idx_func on emp(UPPER(ename)) tablespace tablespace_name; 3、創(chuàng)建位圖索引 對(duì)基數(shù)較小，且基數(shù)相對(duì)穩(wěn)定的列建立索引時(shí)，首先應(yīng)該考慮位圖索引，例： create bitmap index idx_bitm on class (classno) tablespace tablespace_name; 4、明確地創(chuàng)建唯一索引 可以用create unique index語句來創(chuàng)建唯一索引，例： create unique index dept_unique_idx on dept(dept_no) tablespace idx_1; 5、創(chuàng)建與約束相關(guān)的索引 可以用using index字句，為與unique和primary key約束相關(guān)的索引，例： alter table table_name add constraint PK_primary_keyname primary key(field_name) using index tablespace tablespace_name; 如何創(chuàng)建局部區(qū)索引？ 1)基礎(chǔ)表必須是分區(qū)表 2)分區(qū)數(shù)量與基礎(chǔ)表相同 3)每個(gè)索引分區(qū)的子分區(qū)數(shù)量與相應(yīng)的基礎(chǔ)表分區(qū)相同 4)基礎(chǔ)表的自分區(qū)中的行的索引項(xiàng)，被存儲(chǔ)在該索引的相應(yīng)的自分區(qū)中，例如 create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID) Pctfree 5 Tablespace TBS_AK01_IDX Storage( MaxExtents 32768 PctIncrease 0 FreeLists 1 FreeList Groups 1 ) local / 如何創(chuàng)建范圍分區(qū)的全局索引？ 基礎(chǔ)表可以是全局表和分區(qū)表 create index idx_start_date on tg_cdr01(start_date) global partition by range(start_date) (partition p01_idx vlaues less than ('0106') partition p01_idx vlaues less than ('0111') ... partition p01_idx vlaues less than ('0401')) / 如何重建現(xiàn)存的索引？ 重建現(xiàn)存的索引的當(dāng)前時(shí)刻不會(huì)影響查詢 重建索引可以刪除額外的數(shù)據(jù)塊 提高索引查詢效率 alter index idx_name rebuild nologging; 對(duì)于分區(qū)索引 alter index idx_name rebuild partition partition_name nologging; 刪除索引的原因？ 1)不再需要的索引 2)索引沒有針對(duì)其相關(guān)的表所發(fā)布的查詢提供所期望的性能改善 3)應(yīng)用沒有用該索引來查詢數(shù)據(jù) 4)該索引無效，必須在重建之前刪除該索引 5)該索引已經(jīng)變的太碎了，必須在重建之前刪除該索引 語句： drop index idx_name; drop index idx_name partition partition_name; 建立索引的代價(jià)？ 基礎(chǔ)表維護(hù)時(shí)，系統(tǒng)要同時(shí)維護(hù)索引，不合理的索引將嚴(yán)重影響系統(tǒng)資源， 主要表現(xiàn)在CPU和I/O上。 插入、更新、刪除數(shù)據(jù)產(chǎn)生大量db file sequential read鎖等待。

Oracle如何維護(hù)索引

對(duì)三個(gè)字段建立索引：

create index Stuname on student(name);

create index Stusex on student(sex);

create index Stugrade on student(grade);

注意的問題，考慮是不是要建立唯一索引（unique），如果有學(xué)號(hào)的話，可以考慮建立唯一索引引。

再就是對(duì)經(jīng)常查詢，但又相對(duì)穩(wěn)定的可以建立聚簇索引，提高查詢效率

如何更好的使用Oracle全文索引

不使用Oracle text功能,也有很多方法可以在Oracle數(shù)據(jù)庫(kù)中搜索文本.可以使用標(biāo)準(zhǔn)的INSTR函數(shù)和LIKE操作符實(shí)現(xiàn)。

SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') 0;

SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';

有很多時(shí)候，使用instr和like是很理想的, 特別是搜索僅跨越很小的表的時(shí)候.然而通過這些文本定位的方法將導(dǎo)致全表掃描,對(duì)資源來說消耗比較昂貴,而且實(shí)現(xiàn)的搜索功能也非常有限，因此對(duì)海量的文本數(shù)據(jù)進(jìn)行搜索時(shí)，建議使用oralce提供的全文檢索功能 建立全文檢索的步驟步驟一 檢查和設(shè)置數(shù)據(jù)庫(kù)角色首先檢查數(shù)據(jù)庫(kù)中是否有CTXSYS用戶和CTXAPP腳色。如果沒有這個(gè)用戶和角色，意味著你的數(shù)據(jù)庫(kù)創(chuàng)建時(shí)未安裝intermedia功能。你必須修改數(shù)據(jù)庫(kù)以安裝這項(xiàng)功能。 默認(rèn)安裝情況下，ctxsys用戶是被鎖定的，因此要先啟用ctxsys的用戶。 步驟二 賦權(quán) 在ctxsys用戶下把ctx_ddl的執(zhí)行權(quán)限賦于要使用全文索引的用戶，例：

grant execute on ctx_ddl to pomoho;

步驟三 設(shè)置詞法分析器(lexer)

Oracle實(shí)現(xiàn)全文檢索，其機(jī)制其實(shí)很簡(jiǎn)單。即通過Oracle專利的詞法分析器(lexer),將文章中所有的表意單元(Oracle 稱為 term)找出來，記錄在一組 以dr$開頭的表中，同時(shí)記下該term出現(xiàn)的位置、次數(shù)、hash 值等信息。檢索時(shí)，Oracle 從這組表中查找相應(yīng)的term，并計(jì)算其出現(xiàn)頻率，根據(jù)某個(gè)算法來計(jì)算每個(gè)文檔的得分(score),即所謂的‘匹配率’。而lexer則是該機(jī)制的核心，它決定了全文檢索的效率。Oracle 針對(duì)不同的語言提供了不同的 lexer, 而我們通常能用到其中的三個(gè)：

n basic_lexer: 針對(duì)英語。它能根據(jù)空格和標(biāo)點(diǎn)來將英語單詞從句子中分離，還能自動(dòng)將一些出現(xiàn)頻率過高已經(jīng)失去檢索意義的單詞作為‘垃圾’處理，如if , is 等，具有較高的處理效率。但該lexer應(yīng)用于漢語則有很多問題，由于它只認(rèn)空格和標(biāo)點(diǎn)，而漢語的一句話中通常不會(huì)有空格，因此，它會(huì)把整句話作為一個(gè) term,事實(shí)上失去檢索能力。以‘中國(guó)人民站起來了’這句話為例，basic_lexer 分析的結(jié)果只有一個(gè)term ,就是‘中國(guó)人民站起來了’。此時(shí)若檢索‘中國(guó)’，將檢索不到內(nèi)容。

n chinese_vgram_lexer: 專門的漢語分析器，支持所有漢字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。該分析器按字為單元來分析漢語句子?！袊?guó)人民站起來了’這句話，會(huì)被它分析成如下幾個(gè)term: ‘中’，‘中國(guó)’，‘國(guó)人’，‘人民’，‘民站’，‘站起’，起來’，‘來了’，‘了’。可以看出，這種分析方法，實(shí)現(xiàn)算法很簡(jiǎn)單，并且能實(shí)現(xiàn)‘一網(wǎng)打盡’，但效率則是差強(qiáng)人意。

n chinese_lexer: 這是一個(gè)新的漢語分析器，只支持utf8字符集。上面已經(jīng)看到，chinese vgram lexer這個(gè)分析器由于不認(rèn)識(shí)常用的漢語詞匯，因此分析的單元非常機(jī)械，像上面的‘民站’，‘站起’在漢語中根本不會(huì)單獨(dú)出現(xiàn)，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進(jìn)就是該分析器 能認(rèn)識(shí)大部分常用漢語詞匯，因此能更有效率地分析句子，像以上兩個(gè)愚蠢的單元將不會(huì)再出現(xiàn)，極大 提高了效率。但是它只支持 utf8, 如果你的數(shù)據(jù)庫(kù)是zhs16gbk字符集，則只能使用笨笨的那個(gè)Chinese vgram lexer.

如果不做任何設(shè)置，Oracle 缺省使用basic_lexer這個(gè)分析器。要指定使用哪一個(gè)lexer, 可以這樣操作：

第一． 當(dāng)前用戶下下建立一個(gè)preference(例：在pomoho用戶下執(zhí)行以下語句)

exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

第二． 在建立全文索引索引時(shí)，指明所用的lexer:

CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context

parameters('lexer my_lexer');

這樣建立的全文檢索索引，就會(huì)使用chinese_vgram_lexer作為分析器。

步驟四 建立索引

通過以下語法建立全文索引

CREATE INDEX [schema.]index on [schema.]table(column) INDEXTYPE IS ctxsys.context [ONLINE]

LOCAL [(PARTITION [partition] [PARAMETERS('paramstring')]

[, PARTITION [partition] [PARAMETERS('paramstring')]])]

[PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE];

例：

CREATE INDEX ctx_idx_menuname ON pubmenu(menuname)

indextype is ctxsys.context parameters('lexer my_lexer')

步驟五 使用索引

使用全文索引很簡(jiǎn)單，可以通過：

select * from pubmenu where contains(menuname,'上傳圖片')0

全文索引的種類

建立的Oracle Text索引被稱為域索引(domain index)，包括4種索引類型：

l CONTEXT

2 CTXCAT

3 CTXRULE

4 CTXXPATH

依據(jù)你的應(yīng)用程序和文本數(shù)據(jù)類型你可以任意選擇一種。

對(duì)多字段建立全文索引

很多時(shí)候需要從多個(gè)文本字段中查詢滿足條件的記錄，這時(shí)就需要建立針對(duì)多個(gè)字段的全文索引，例如需要從pmhsubjects(專題表)的 subjectname(專題名稱)和briefintro(簡(jiǎn)介)上進(jìn)行全文檢索，則需要按以下步驟進(jìn)行操作：

? 建議多字段索引的preference

以ctxsys登錄，并執(zhí)行：

EXEC ctx_ddl.create_preference(' ctx_idx_subject_pref',

'MULTI_COLUMN_DATASTORE');

? 建立preference對(duì)應(yīng)的字段值(以ctxsys登錄)

EXEC ctx_ddl.set_attribute(' ctx_idx_subject_pref ','columns','subjectname,briefintro');

? 建立全文索引

CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname)

INDEXTYPE ISctxsys.CONTEXT PARAMETERS('DATASTORE ctxsys.ctx_idx_subject_pref lexer my_lexer')

? 使用索引

select * from pmhsubjects where contains(subjectname,'李宇春')0

全文索引的維護(hù)

對(duì)于CTXSYS.CONTEXT索引，當(dāng)應(yīng)用程序?qū)磉M(jìn)行DML操作后，對(duì)基表的索引維護(hù)是必須的。索引維護(hù)包括索引同步和索引優(yōu)化。

在索引建好后，我們可以在該用戶下查到Oracle自動(dòng)產(chǎn)生了以下幾個(gè)表：(假設(shè)索引名為myindex)：

DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要，可以查詢一下該表，看看有什么內(nèi)容：

SELECT token_text, token_count FROM dr$i_rsk1$I WHERE ROWNUM = 20;

這里就不列出查詢接過了?？梢钥吹剑摫碇斜４娴钠鋵?shí)就是Oracle 分析你的文檔后，生成的term記錄在這里，包括term出現(xiàn)的位置、次數(shù)、hash值等。當(dāng)文檔的內(nèi)容改變后，可以想見這個(gè)I表的內(nèi)容也應(yīng)該相應(yīng)改變，才能保證Oracle在做全文檢索時(shí)正確檢索到內(nèi)容(因?yàn)樗^全文檢索，其實(shí)核心就是查詢這個(gè)表)。這就用到sync(同步) 和 optimize(優(yōu)化)了。

同步(sync): 將新的term 保存到I表；

優(yōu)化(optimize): 清除I表的垃圾，主要是將已經(jīng)被刪除的term從I表刪除。

當(dāng)基表中的被索引文檔發(fā)生insert、update、delete操作的時(shí)候，基表的改變并不能馬上影響到索引上直到同步索引?？梢圆樵円晥D CTX_USER_PENDING查看相應(yīng)的改動(dòng)。例如：

SELECT pnd_index_name, pnd_rowid,

TO_CHAR (pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp

FROM ctx_user_pending;

該語句的輸出類似如下：

PND_INDEX_NAME PND_ROWID TIMESTAMP

------------------------------ ------------------ --------------------

MYINDEX AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50

同步和優(yōu)化方法: 可以使用Oracle提供的ctx_ddl包同步和優(yōu)化索引

一. 對(duì)于CTXCAT類型的索引來說， 當(dāng)對(duì)基表進(jìn)行DML操作的時(shí)候，Oracle自動(dòng)維護(hù)索引。對(duì)文檔的改變馬上反映到索引中。CTXCAT是事務(wù)形的索引。

索引的同步

在對(duì)基表插入，修改，刪除之后同步索引。推薦使用sync同步索引。語法：

ctx_ddl.sync_index(

idx_name IN VARCHAR2 DEFAULT NULL

memory IN VARCHAR2 DEFAULT NULL,

part_name IN VARCHAR2 DEFAULT NULL

parallel_degree IN NUMBER DEFAULT 1);

idx_name 索引名稱

memory 指定同步索引需要的內(nèi)存。默認(rèn)是系統(tǒng)參數(shù)DEFAULT_INDEX_MEMORY 。

指定一個(gè)大的內(nèi)存時(shí)候可以加快索引效率和查詢速度，且索引有較少的碎片

part_name 同步哪個(gè)分區(qū)索引。

parallel_degree 并行同步索引。設(shè)置并行度。

例如：

同步索引myindex:Exec ctx_ddl.sync_index ('myindex');

實(shí)施建議：建議通過oracle的job對(duì)索引進(jìn)行同步

索引的優(yōu)化

經(jīng)常的索引同步將會(huì)導(dǎo)致你的CONTEXT索引產(chǎn)生碎片。索引碎片嚴(yán)重的影響了查詢的反應(yīng)速度。你可以定期優(yōu)化索引來減少碎片，減少索引大小，提高查詢效率。

當(dāng)文本從表中刪除的時(shí)候，Oracle Text標(biāo)記刪除的文檔，但是并不馬上修改索引。因此，就的文檔信息占據(jù)了不必要的空間，導(dǎo)致了查詢額外的開銷。你必須以FULL模式優(yōu)化索引，從索引中刪除無效的舊的信息。這個(gè)過程叫做垃圾處理。當(dāng)你經(jīng)常的對(duì)表文本數(shù)據(jù)進(jìn)行更新，刪除操作的時(shí)候，垃圾處理是很必要的。

exec ctx_ddl.optimize_index ('myidx', 'full');

實(shí)施建議：每天在系統(tǒng)空閑的時(shí)候?qū)θ乃饕M(jìn)行相應(yīng)的優(yōu)化，以提高檢索的效率

P.S.定時(shí)優(yōu)化索引

3.定時(shí)優(yōu)化同步域索引

創(chuàng)建定時(shí)任務(wù)，定期優(yōu)化和同步域索引

SQL create or replace procedure hsp_sync_index as

2 begin

3 ctx_ddl.sync_index('id_cont_msg');

4 end;

5 /

Procedure created.

Elapsed: 00:00:00.08

SQL VARIABLE jobno number;

SQL BEGIN

2 DBMS_JOB.SUBMIT(:jobno,'hsp_sync_index();',

3 SYSDATE, 'SYSDATE + (1/24/4)');

4 commit;

5 END;

6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

SQL create or replace procedure hsp_optimize_index as

2 begin

3 ctx_ddl.optimize_index('id_cont_msg','FULL');

4 end;

5 /

SQL VARIABLE jobno number;

SQL BEGIN

2 DBMS_JOB.SUBMIT(:jobno,'hsp_optimize_index();',

3 SYSDATE, 'SYSDATE + 1');

4 commit;

5 END;

6 /

Procedure created.

Elapsed: 00:00:00.03

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

SQL