ORACLE索引提高效率

用索引提高效率

創(chuàng)新互聯(lián)建站長期為上千余家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為瑞麗企業(yè)提供專業(yè)的網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站設(shè)計(jì)，瑞麗網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

索引是表的一個(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ù)庫不能對(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索引創(chuàng)建后能修改其區(qū)參數(shù)嗎

Oracle 內(nèi)在索引和參數(shù)數(shù)的調(diào)整：如果你不能更改代碼

本文是有關(guān)Oracle 基于成本的內(nèi)部優(yōu)化()

和結(jié)構(gòu)化查詢語言(SQL)性能最優(yōu)化的一系列文章(共12部分)中的第10部分。每個(gè)技巧類的文章都摘錄自即將由Rampant科技出版社出版的書

《Oracle 結(jié)構(gòu)化查詢語言(SQL)和內(nèi)在索引》，作者是Kimberly Floss。從該系列的主頁上，你能看到其他即將發(fā)布的部分。

如果你不能更改代碼。。。

雖然系統(tǒng)級(jí)性能調(diào)優(yōu)不是結(jié)構(gòu)化查詢語言(SQL)性能調(diào)優(yōu)的最好解決方案，但是，當(dāng)你不能改變某些SQL語句時(shí)(例如，提供商提供的某些包中的SQL語句)，使用SQL處理的常規(guī)模式來調(diào)節(jié)系統(tǒng)性能是極其有幫助的。在日常工作經(jīng)驗(yàn)的基礎(chǔ)上，員改變某些優(yōu)化參數(shù)來適應(yīng)庫緩沖區(qū)中SQL類型的變化是很常見的事。

一些較為常見的變更：

1、針對(duì)Oracle參數(shù)的更改。對(duì)optimizer_mode，optimizer__cost_adj和optimizer_index_caching的改變能對(duì)SQL執(zhí)行計(jì)劃產(chǎn)生巨大影響。

2、 針對(duì)統(tǒng)計(jì)參數(shù)的更改。使用dbms_stats包導(dǎo)入特定的統(tǒng)計(jì)參數(shù)(針對(duì)當(dāng)然處理模式作了調(diào)整的)可對(duì)SQL的執(zhí)行速度產(chǎn)生巨大影響。

3、 使用自動(dòng)化查詢重新寫入。使用Oracle實(shí)體化視圖能夠預(yù)先聚集、預(yù)先匯總數(shù)據(jù)，從而減少運(yùn)行時(shí)刻表連接的數(shù)量。對(duì)于更新比較少的數(shù)據(jù)庫，也可以通過預(yù)先連接表來提高處理速度。

一些對(duì)性能調(diào)優(yōu)最重要的Oracle優(yōu)化參數(shù)如下：

* optimizer_mode(優(yōu)化模式)－在Oracle 中，有許多優(yōu)化模式，都是由參數(shù)optimizer_mode的值決定的。這個(gè)參數(shù)的取值范圍是rule, choose, all_rows, first_rows, first_rows_1, first_rows_10 和 first_rows_100.

我們以定義“最好的”執(zhí)行計(jì)劃作為開始點(diǎn)。在任何給定的時(shí)間，庫緩沖區(qū)中的所有SQL語句都需要有“最好的”執(zhí)行計(jì)劃(當(dāng)然，由于在任何給定的時(shí)間

里處理需求可能不同，所以這個(gè)最優(yōu)執(zhí)行計(jì)劃可能會(huì)經(jīng)常發(fā)生變化)

如何選擇和優(yōu)化Oracle索引

Oracle 建立索引及SQL優(yōu)化

Oracle 建立索引及SQL優(yōu)化

數(shù)據(jù)庫索引:

索引有單列索引

復(fù)合索引之說

如何某表的某個(gè)字段有主鍵約束和唯一性約束，則Oracle 則會(huì)自動(dòng)在相應(yīng)的約束列上建議唯一索引。數(shù)據(jù)庫索引主要進(jìn)行提高訪問速度。

建設(shè)原則:

1、索引應(yīng)該經(jīng)常建在Where 子句經(jīng)常用到的列上。如果某個(gè)大表經(jīng)常使用某個(gè)字段進(jìn)行查詢，并且檢索行數(shù)小于總表行數(shù)的5%。則應(yīng)該考慮。

2、對(duì)于兩表連接的字段，應(yīng)該建立索引。如果經(jīng)常在某表的一個(gè)字段進(jìn)行Order By 則也經(jīng)過進(jìn)行索引。

3、不應(yīng)該在小表上建設(shè)索引。

優(yōu)缺點(diǎn):

1、索引主要進(jìn)行提高數(shù)據(jù)的查詢速度。 當(dāng)進(jìn)行DML時(shí)，會(huì)更新索引。因此索引越多，則DML越慢，其需要維護(hù)索引。 因此在創(chuàng)建索引及DML需要權(quán)衡。

創(chuàng)建索引:

單一索引:Create Index Index-Name On Table_Name(Column_Name);

復(fù)合索引: Create Index i_deptno_job on emp(deptno,job); —在emp表的deptno、job列建立索引。

select * from emp where deptno=66 and job='sals' -走索引。

select * from emp where deptno=66 OR job='sals' -將進(jìn)行全表掃描。不走索引

select * from emp where deptno=66 -走索引。

select * from emp where job='sals' -進(jìn)行全表掃描、不走索引。

如果在where 子句中有OR 操作符或單獨(dú)引用Job 列(索引列的后面列) 則將不會(huì)走索引，將會(huì)進(jìn)行全表掃描。

Sql 優(yōu)化:

當(dāng)Oracle數(shù)據(jù)庫拿到SQL語句時(shí)，其會(huì)根據(jù)查詢優(yōu)化器分析該語句，并根據(jù)分析結(jié)果生成查詢執(zhí)行計(jì)劃。

也就是說，數(shù)據(jù)庫是執(zhí)行的查詢計(jì)劃，而不是Sql語句。

查詢優(yōu)化器有rule-based-optimizer(基于規(guī)則的查詢優(yōu)化器) 和Cost-Based-optimizer(基于成本的查詢優(yōu)化器)。

其中基于規(guī)則的查詢優(yōu)化器在10g版本中消失。

對(duì)于規(guī)則查詢，其最后查詢的是全表掃描。而CBO則會(huì)根據(jù)統(tǒng)計(jì)信息進(jìn)行最后的選擇。

1、先執(zhí)行From -Where -Group By-Order By

2、執(zhí)行From 字句是從右往左進(jìn)行執(zhí)行。因此必須選擇記錄條數(shù)最少的表放在右邊。這是為什么呢？

3、對(duì)于Where字句其執(zhí)行順序是從后向前執(zhí)行、因此可以過濾最大數(shù)量記錄的條件必須寫在Where子句的末尾，而對(duì)于多表之間的連接，則寫在之前。

因?yàn)檫@樣進(jìn)行連接時(shí)，可以去掉大多不重復(fù)的項(xiàng)。

4. SELECT子句中避免使用(*)ORACLE在解析的過程中, 會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間

5、索引失效的情況:

① Not Null/Null 如果某列建立索引,當(dāng)進(jìn)行Select * from emp where depto is not null/is null。 則會(huì)是索引失效。

② 索引列上不要使用函數(shù),SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'?

或者SELECT Col FROM tbl WHERE name LIKE '%ABC%' 而SELECT Col FROM tbl WHERE name LIKE 'ABC%' 會(huì)使用索引。

③ 索引列上不能進(jìn)行計(jì)算SELECT Col FROM tbl WHERE col / 10 10 則會(huì)使索引失效，應(yīng)該改成

SELECT Col FROM tbl WHERE col 10 * 10

④ 索引列上不要使用NOT （ != 、 ）如:SELECT Col FROM tbl WHERE col ! = 10?

應(yīng)該 改成：SELECT Col FROM tbl WHERE col 10 OR col 10 。

6、用UNION替換OR(適用于索引列)

union:是將兩個(gè)查詢的結(jié)果集進(jìn)行追加在一起，它不會(huì)引起列的變化。 由于是追加操作，需要兩個(gè)結(jié)果集的列數(shù)應(yīng)該是相關(guān)的，

并且相應(yīng)列的數(shù)據(jù)類型也應(yīng)該相當(dāng)?shù)?。union 返回兩個(gè)結(jié)果集，同時(shí)將兩個(gè)結(jié)果集重復(fù)的項(xiàng)進(jìn)行消除。 如果不進(jìn)行消除，用UNOIN ALL.

通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效.?

如果有column沒有被索引, 查詢效率可能會(huì)因?yàn)槟銢]有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

高效:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE REGION = “MELBOURNE”

低效:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫在最前面.

7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

在許多基于基礎(chǔ)表的查詢中, 為了滿足一個(gè)條件, 往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接. 在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.?

在子查詢中, NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無論在哪種情況下, NOT IN都是最低效的(因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷).?

為了避免使用NOT IN, 我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.

例子：

高效: SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

低效: SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)