|
一．索引介紹
1.1 索引的創(chuàng)建語(yǔ)法：
CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
相關(guān)說(shuō)明
1） UNIQUE | BITMAP：指定UNIQUE為唯一值索引，BITMAP為位圖索引，省略為B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以對(duì)多列進(jìn)行聯(lián)合索引，當(dāng)為expression時(shí)即“基于函數(shù)的索引”
3）TABLESPACE：指定存放索引的表空間(索引和原表不在一個(gè)表空間時(shí)效率更高)
4）STORAGE：可進(jìn)一步設(shè)置表空間的存儲(chǔ)參數(shù)
5）LOGGING | NOLOGGING：是否對(duì)索引產(chǎn)生重做日志(對(duì)大表盡量使用NOLOGGING來(lái)減少占用空間并提高效率)
6）COMPUTE STATISTICS：創(chuàng)建新索引時(shí)收集統(tǒng)計(jì)信息
7）NOCOMPRESS | COMPRESS<nn>：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個(gè)鍵列中出現(xiàn)的重復(fù)值)
8）NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲(chǔ)索引值
9）PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對(duì)創(chuàng)建的索引進(jìn)行分區(qū)
1.2 索引特點(diǎn)：
第一，通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
第三，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
第四，在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。
第五，通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
1.3 索引不足：
第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。
第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。
第三，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
1.4 應(yīng)該建索引列的特點(diǎn)：
1）在經(jīng)常需要搜索的列上，可以加快搜索的速度；
2）在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；
3）在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
4）在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的；
5）在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間；
6）在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
1.5 不應(yīng)該建索引列的特點(diǎn)：
第一，對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因?yàn)椋热贿@些列很少使用到，因此有索引或者無(wú)索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。
第二，對(duì)于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。這是因?yàn)?，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。
第三，對(duì)于那些定義為blob數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因?yàn)?，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。
第四，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)?，修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。
1.6 限制索引
限制索引是一些沒(méi)有經(jīng)驗(yàn)的開(kāi)發(fā)人員經(jīng)常犯的錯(cuò)誤之一。在SQL中有很多陷阱會(huì)使一些索引無(wú)法使用。下面討論一些常見(jiàn)的問(wèn)題：
1.6.1 使用不等于操作符（<>、!=）
下面的查詢即使在cust_rating列有一個(gè)索引，查詢語(yǔ)句仍然執(zhí)行一次全表掃描。
select cust_Id,cust_name from customers where cust_rating <> 'aa';
把上面的語(yǔ)句改成如下的查詢語(yǔ)句，這樣，在采用基于規(guī)則的優(yōu)化器而不是基于代價(jià)的優(yōu)化器（更智能）時(shí)，將會(huì)使用索引。
select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';
特別注意：通過(guò)把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
1.6.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同樣會(huì)限制索引的使用。因?yàn)镹ULL值并沒(méi)有被定義。在SQL語(yǔ)句中使用NULL會(huì)有很多的麻煩。因此建議開(kāi)發(fā)人員在建表時(shí)，把需要索引的列設(shè)成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索引，關(guān)于位圖索引在稍后在詳細(xì)討論）。
1.6.3 使用函數(shù)
如果不使用基于函數(shù)的索引，那么在SQL語(yǔ)句的WHERE子句中對(duì)存在索引的列使用函數(shù)時(shí)，會(huì)使優(yōu)化器忽略掉這些索引。 下面的查詢不會(huì)使用索引（只要它不是基于函數(shù)的索引）
select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
把上面的語(yǔ)句改成下面的語(yǔ)句，這樣就可以通過(guò)索引進(jìn)行查找。
select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);
1.6.4 比較不匹配的數(shù)據(jù)類型
也是比較難于發(fā)現(xiàn)的性能問(wèn)題之一。 注意下面查詢的例子，account_number是一個(gè)VARCHAR2類型,在account_number字段上有索引。
下面的語(yǔ)句將執(zhí)行全表掃描：
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自動(dòng)把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number ='990354';
特別注意：不匹配的數(shù)據(jù)類型之間比較會(huì)讓Oracle自動(dòng)限制索引的使用,即便對(duì)這個(gè)查詢執(zhí)行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。
1.7 查詢索引
查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過(guò)USER_INDEXES的方法來(lái)檢索模式(schema)的索引。訪問(wèn)USER_IND_COLUMNS視圖可得到一個(gè)給定表中被索引的特定列。
1.8 組合索引
當(dāng)某個(gè)索引包含有多個(gè)已索引的列時(shí)，稱這個(gè)索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問(wèn)方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個(gè)組合索引鍵，該索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中對(duì)第一列（empno）指定一個(gè)值，否則就不能使用這個(gè)索引鍵進(jìn)行一次范圍掃描。
特別注意：在Oracle9i之前，只有在使用到索引的前導(dǎo)索引時(shí)才可以使用組合索引！
1.9 ORACLE ROWID
通過(guò)每個(gè)行的ROWID，索引Oracle提供了訪問(wèn)單行數(shù)據(jù)的能力。ROWID其實(shí)就是直接指向單獨(dú)行的線路圖。如果想檢查重復(fù)值或是其他對(duì)ROWID本身的引用，可以在任何表中使用和指定rowid列。
1.10 選擇性
使用USER_INDEXES視圖，該視圖中顯示了一個(gè)distinct_keys列。比較一下唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷索引的選擇性。選擇性越高，索引返回的數(shù)據(jù)就越少。
1.11 群集因子(Clustering Factor)
Clustering Factor位于USER_INDEXES視圖中。該列反映了數(shù)據(jù)相對(duì)于已建索引的列是否顯得有序。如果Clustering Factor列的值接近于索引中的樹葉塊(leaf block)的數(shù)目，表中的數(shù)據(jù)就越有序。如果它的值接近于表中的行數(shù)，則表中的數(shù)據(jù)就不是很有序。
1.12 二元高度(Binary height)
索引的二元高度對(duì)把ROWID返回給用戶進(jìn)程時(shí)所要求的I/O量起到關(guān)鍵作用。在對(duì)一個(gè)索引進(jìn)行分析后，可以通過(guò)查詢DBA_INDEXES的B- level列查看它的二元高度。二元高度主要隨著表的大小以及被索引的列中值的范圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會(huì)增加。更新索引列也類似于刪除操作，因?yàn)樗黾恿艘褎h除鍵的數(shù)目。重建索引可能會(huì)降低二元高度。
1.13 快速全局掃描
從Oracle7.3后就可以使用快速全局掃描(Fast Full Scan)這個(gè)選項(xiàng)。這個(gè)選項(xiàng)允許Oracle執(zhí)行一個(gè)全局索引掃描操作?？焖偃謷呙枳x取B-樹索引上所有樹葉塊。初始化文件中的 DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)可以控制同時(shí)被讀取的塊的數(shù)目。
1.14 跳躍式掃描
從Oracle9i開(kāi)始，索引跳躍式掃描特性可以允許優(yōu)化器使用組合索引，即便索引的前導(dǎo)列沒(méi)有出現(xiàn)在WHERE子句中。索引跳躍式掃描比全索引掃描要快的多。
下面的比較他們的區(qū)別：
SQL> set timing on
SQL> create index TT_index on TT(teamid,areacode);
索引已創(chuàng)建。
已用時(shí)間: 00: 02: 03.93
SQL> select count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時(shí)間: 00: 00: 08.31
SQL> select /*+ index(tt TT_index )*/ count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時(shí)間: 00: 00: 07.37
1.15 索引的類型
B-樹索引 位圖索引 HASH索引 索引編排表
反轉(zhuǎn)鍵索引 基于函數(shù)的索引 分區(qū)索引 本地和全局索引
二． 索引分類
Oracle提供了大量索引選項(xiàng)。知道在給定條件下使用哪個(gè)選項(xiàng)對(duì)于一個(gè)應(yīng)用程序的性能來(lái)說(shuō)非常重要。一個(gè)錯(cuò)誤的選擇可能會(huì)引發(fā)死鎖，并導(dǎo)致數(shù)據(jù)庫(kù)性能急劇下降或進(jìn)程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經(jīng)運(yùn)行了幾個(gè)小時(shí)甚至幾天的進(jìn)程在幾分鐘得以完成，這樣會(huì)使您立刻成為一位英雄。下面就將簡(jiǎn)單的討論每個(gè)索引選項(xiàng)。
下面討論的索引類型：
B樹索引(默認(rèn)類型)
位圖索引
HASH索引
索引組織表索引
反轉(zhuǎn)鍵(reverse key)索引
基于函數(shù)的索引
分區(qū)索引(本地和全局索引)
位圖連接索引
2.1 B樹索引 (默認(rèn)類型)
B樹索引在Oracle中是一個(gè)通用索引。在創(chuàng)建索引時(shí)它就是默認(rèn)的索引類型。B樹索引可以是一個(gè)列的(簡(jiǎn)單)索引，也可以是組合/復(fù)合(多個(gè)列)的索引。B樹索引最多可以包括32列。
在下圖的例子中，B樹索引位于雇員表的last_name列上。這個(gè)索引的二元高度為3；接下來(lái)，Oracle會(huì)穿過(guò)兩個(gè)樹枝塊(branch block)，到達(dá)包含有ROWID的樹葉塊。在每個(gè)樹枝塊中，樹枝行包含鏈中下一個(gè)塊的ID號(hào)。
樹葉塊包含了索引值、ROWID，以及指向前一個(gè)和后一個(gè)樹葉塊的指針。Oracle可以從兩個(gè)方向遍歷這個(gè)二叉樹。B樹索引保存了在索引列上有值的每個(gè)數(shù)據(jù)行的ROWID值。Oracle不會(huì)對(duì)索引列上包含NULL值的行進(jìn)行索引。如果索引是多個(gè)列的組合索引，而其中列上包含NULL值，這一行就會(huì)處于包含NULL值的索引列中，且將被處理為空(視為NULL)。
技巧：索引列的值都存儲(chǔ)在索引中。因此，可以建立一個(gè)組合(復(fù)合)索引，這些索引可以直接滿足查詢，而不用訪問(wèn)表。這就不用從表中檢索數(shù)據(jù)，從而減少了I/O量。
B-tree 特點(diǎn)：
適合與大量的增、刪、改（OLTP）
不能用包含OR操作符的查詢；
適合高基數(shù)的列（唯一值多）
典型的樹狀結(jié)構(gòu)；
每個(gè)結(jié)點(diǎn)都是數(shù)據(jù)塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊數(shù)據(jù)是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的范圍；
2.2 位圖索引
位圖索引非常適合于決策支持系統(tǒng)(Decision Support System，DSS)和數(shù)據(jù)倉(cāng)庫(kù)，它們不應(yīng)該用于通過(guò)事務(wù)處理應(yīng)用程序訪問(wèn)的表。它們可以使用較少到中等基數(shù)(不同值的數(shù)量)的列訪問(wèn)非常大的表。盡管位圖索引最多可達(dá)30個(gè)列，但通常它們都只用于少量的列。
例如，您的表可能包含一個(gè)稱為Sex的列，它有兩個(gè)可能值：男和女。這個(gè)基數(shù)只為2，如果用戶頻繁地根據(jù)Sex列的值查詢?cè)摫?，這就是位圖索引的基列。當(dāng)一個(gè)表內(nèi)包含了多個(gè)位圖索引時(shí)，您可以體會(huì)到位圖索引的真正威力。如果有多個(gè)可用的位圖索引，Oracle就可以合并從每個(gè)位圖索引得到的結(jié)果集，快速刪除不必要的數(shù)據(jù)。
Bitmapt 特點(diǎn)：
適合與決策支持系統(tǒng)；
做UPDATE代價(jià)非常高；
非常適合OR操作符的查詢；
基數(shù)比較少的時(shí)候才能建位圖索引；
技巧：對(duì)于有較低基數(shù)的列需要使用位圖索引。性別列就是這樣一個(gè)例子，它有兩個(gè)可能值：男或女(基數(shù)僅為2)。位圖對(duì)于低基數(shù)(少量的不同值)列來(lái)說(shuō)非?？欤@是因?yàn)樗饕某叽缦鄬?duì)于B樹索引來(lái)說(shuō)小了很多。因?yàn)檫@些索引是低基數(shù)的B樹索引，所以非常小，因此您可以經(jīng)常檢索表中超過(guò)半數(shù)的行，并且仍使用位圖索引。
當(dāng)大多數(shù)條目不會(huì)向位圖添加新的值時(shí)，位圖索引在批處理(單用戶)操作中加載表(插入操作)方面通常要比B樹做得好。當(dāng)多個(gè)會(huì)話同時(shí)向表中插入行時(shí)不應(yīng)該使用位圖索引，在大多數(shù)事務(wù)處理應(yīng)用程序中都會(huì)發(fā)生這種情況。
示例
下面來(lái)看一個(gè)示例表PARTICIPANT，該表包含了來(lái)自個(gè)人的調(diào)查數(shù)據(jù)。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位圖索引。下圖顯示了每個(gè)直方圖中的數(shù)據(jù)平衡情況，以及對(duì)訪問(wèn)每個(gè)位圖索引的查詢的執(zhí)行路徑。圖中的執(zhí)行路徑顯示了有多少個(gè)位圖索引被合并，可以看出性能得到了顯著的提高。
如上圖圖所示，優(yōu)化器依次使用4個(gè)單獨(dú)的位圖索引，這些索引的列在WHERE子句中被引用。每個(gè)位圖記錄指針(例如0或1)，用于指示表中的哪些行包含位圖中的已知值。有了這些信息后，Oracle就執(zhí)行BITMAP AND操作以查找將從所有4個(gè)位圖中返回哪些行。該值然后被轉(zhuǎn)換為ROWID值，并且查詢繼續(xù)完成剩余的處理工作。注意，所有4個(gè)列都有非常低的基數(shù)，使用索引可以非?？焖俚胤祷仄ヅ涞男?。
技巧：在一個(gè)查詢中合并多個(gè)位圖索引后，可以使性能顯著提高。位圖索引使用固定長(zhǎng)度的數(shù)據(jù)類型要比可變長(zhǎng)度的數(shù)據(jù)類型好。較大尺寸的塊也會(huì)提高對(duì)位圖索引的存儲(chǔ)和讀取性能。
下面的查詢可顯示索引類型。
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ----------------------
TT_INDEX NORMAL
IX_CUSTADDR_TP NORMAL
B樹索引作為NORMAL列出；而位圖索引的類型值為BITMAP。
技巧：如果要查詢位圖索引列表，可以在USER _INDEXES視圖中查詢index_type列。
建議不要在一些聯(lián)機(jī)事務(wù)處理(OLTP)應(yīng)用程序中使用位圖索引。B樹索引的索引值中包含ROWID，這樣Oracle就可以在行級(jí)別上鎖定索引。位圖索引存儲(chǔ)為壓縮的索引值，其中包含了一定范圍的ROWID，因此Oracle必須針對(duì)一個(gè)給定值鎖定所有范圍內(nèi)的ROWID。這種鎖定類型可能在某些DML語(yǔ)句中造成死鎖。SELECT語(yǔ)句不會(huì)受到這種鎖定問(wèn)題的影響。
位圖索引的使用限制：
基于規(guī)則的優(yōu)化器不會(huì)考慮位圖索引。
當(dāng)執(zhí)行ALTER TABLE語(yǔ)句并修改包含有位圖索引的列時(shí)，會(huì)使位圖索引失效。
位圖索引不包含任何列數(shù)據(jù)，并且不能用于任何類型的完整性檢查。
位圖索引不能被聲明為唯一索引。
位圖索引的最大長(zhǎng)度為30。
技巧：不要在繁重的OLTP環(huán)境中使用位圖索引
2.3 HASH索引
使用HASH索引必須要使用HASH集群。建立一個(gè)集群或HASH集群的同時(shí)，也就定義了一個(gè)集群鍵。這個(gè)鍵告訴Oracle如何在集群上存儲(chǔ)表。在存儲(chǔ)數(shù)據(jù)時(shí)，所有與這個(gè)集群鍵相關(guān)的行都被存儲(chǔ)在一個(gè)數(shù)據(jù)庫(kù)塊上。如果數(shù)據(jù)都存儲(chǔ)在同一個(gè)數(shù)據(jù)庫(kù)塊上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過(guò)執(zhí)行一個(gè)HASH函數(shù)和I/O來(lái)訪問(wèn)數(shù)據(jù)——而通過(guò)使用一個(gè)二元高度為4的B樹索引來(lái)訪問(wèn)數(shù)據(jù)，則需要在檢索數(shù)據(jù)時(shí)使用4個(gè)I/O。如下圖所示，其中的查詢是一個(gè)等價(jià)查詢，用于匹配HASH列和確切的值。Oracle可以快速使用該值，基于HASH函數(shù)確定行的物理存儲(chǔ)位置。
HASH索引可能是訪問(wèn)數(shù)據(jù)庫(kù)中數(shù)據(jù)的最快方法，但它也有自身的缺點(diǎn)。集群鍵上不同值的數(shù)目必須在創(chuàng)建HASH集群之前就要知道。需要在創(chuàng)建HASH集群的時(shí)候指定這個(gè)值。低估了集群鍵的不同值的數(shù)字可能會(huì)造成集群的沖突(兩個(gè)集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的。沖突會(huì)造成用來(lái)存儲(chǔ)額外行的緩沖溢出，然后造成額外的I/O。如果不同HASH值的數(shù)目已經(jīng)被低估，您就必須在重建這個(gè)集群之后改變這個(gè)值。
ALTER CLUSTER命令不能改變HASH鍵的數(shù)目。HASH集群還可能浪費(fèi)空間。如果無(wú)法確定需要多少空間來(lái)維護(hù)某個(gè)集群鍵上的所有行，就可能造成空間的浪費(fèi)。如果不能為集群的未來(lái)增長(zhǎng)分配好附加的空間，HASH集群可能就不是最好的選擇。如果應(yīng)用程序經(jīng)常在集群表上進(jìn)行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來(lái)的增長(zhǎng)分配好集群的剩余空間量，全表掃描可能非常消耗資源。
在實(shí)現(xiàn)HASH集群之前一定要小心。您需要全面地觀察應(yīng)用程序，保證在實(shí)現(xiàn)這個(gè)選項(xiàng)之前已經(jīng)了解關(guān)于表和數(shù)據(jù)的大量信息。通常，HASH對(duì)于一些包含有序值的靜態(tài)數(shù)據(jù)非常有效。
技巧：HASH索引在有限制條件(需要指定一個(gè)確定的值而不是一個(gè)值范圍)的情況下非常有用。
2.4 索引組織表
索引組織表會(huì)把表的存儲(chǔ)結(jié)構(gòu)改成B樹結(jié)構(gòu)，以表的主鍵進(jìn)行排序。這種特殊的表和其他類型的表一樣，可以在表上執(zhí)行所有的DML和DDL語(yǔ)句。由于表的特殊結(jié)構(gòu)，ROWID并沒(méi)有被關(guān)聯(lián)到表的行上。
對(duì)于一些涉及精確匹配和范圍搜索的語(yǔ)句，索引組織表提供了一種基于鍵的快速數(shù)據(jù)訪問(wèn)機(jī)制。基于主鍵值的UPDATE和DELETE語(yǔ)句的性能也同樣得以提高，這是因?yàn)樾性谖锢砩嫌行?。由于鍵列的值在表和索引中都沒(méi)有重復(fù)，存儲(chǔ)所需要的空間也隨之減少。
如果不會(huì)頻繁地根據(jù)主鍵列查詢數(shù)據(jù)，則需要在索引組織表中的其他列上創(chuàng)建二級(jí)索引。不會(huì)頻繁根據(jù)主鍵查詢表的應(yīng)用程序不會(huì)了解到使用索引組織表的全部?jī)?yōu)點(diǎn)。對(duì)于總是通過(guò)對(duì)主鍵的精確匹配或范圍掃描進(jìn)行訪問(wèn)的表，就需要考慮使用索引組織表。
技巧：可以在索引組織表上建立二級(jí)索引。
2.5 反轉(zhuǎn)鍵索引
當(dāng)載入一些有序數(shù)據(jù)時(shí)，索引肯定會(huì)碰到與I/O相關(guān)的一些瓶頸。在數(shù)據(jù)載入期間，某部分索引和磁盤肯定會(huì)比其他部分使用頻繁得多。為了解決這個(gè)問(wèn)題，可以把索引表空間存放在能夠把文件物理分割在多個(gè)磁盤上的磁盤體系結(jié)構(gòu)上。
為了解決這個(gè)問(wèn)題，Oracle還提供了一種反轉(zhuǎn)鍵索引的方法。如果數(shù)據(jù)以反轉(zhuǎn)鍵索引存儲(chǔ)，這些數(shù)據(jù)的值就會(huì)與原先存儲(chǔ)的數(shù)值相反。這樣，數(shù)據(jù)1234、1235和1236就被存儲(chǔ)成4321、5321和6321。結(jié)果就是索引會(huì)為每次新插入的行更新不同的索引塊。
技巧：如果您的磁盤容量有限，同時(shí)還要執(zhí)行大量的有序載入，就可以使用反轉(zhuǎn)鍵索引。
不可以將反轉(zhuǎn)鍵索引與位圖索引或索引組織表結(jié)合使用。因?yàn)?span style="color:rgb(255,0,0)">不能對(duì)位圖索引和索引組織表進(jìn)行反轉(zhuǎn)鍵處理。
2.6 基于函數(shù)的索引
可以在表中創(chuàng)建基于函數(shù)的索引。如果沒(méi)有基于函數(shù)的索引，任何在列上執(zhí)行了函數(shù)的查詢都不能使用這個(gè)列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基于函數(shù)的索引：
select * from emp where UPPER(job) = 'MGR';
下面的查詢使用JOB列上的索引，但是它將不會(huì)返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';
可以創(chuàng)建這樣的索引，允許索引訪問(wèn)支持基于函數(shù)的列或數(shù)據(jù)。可以對(duì)列表達(dá)式UPPER(job)創(chuàng)建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));
盡管基于函數(shù)的索引非常有用，但在建立它們之前必須先考慮下面一些問(wèn)題：
能限制在這個(gè)列上使用的函數(shù)嗎？如果能，能限制所有在這個(gè)列上執(zhí)行的所有函數(shù)嗎
是否有足夠應(yīng)付額外索引的存儲(chǔ)空間？
在每列上增加的索引數(shù)量會(huì)對(duì)針對(duì)該表執(zhí)行的DML語(yǔ)句的性能帶來(lái)何種影響？
基于函數(shù)的索引非常有用，但在實(shí)現(xiàn)時(shí)必須小心。在表上創(chuàng)建的索引越多，INSERT、UPDATE和DELETE語(yǔ)句的執(zhí)行就會(huì)花費(fèi)越多的時(shí)間。
注意：對(duì)于優(yōu)化器所使用的基于函數(shù)的索引來(lái)說(shuō)，必須把初始參數(shù)QUERY _REWRITE _ ENABLED設(shè)定為TRUE。
示例：
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes
create index ratio_idx1 on sample (ratio(balance, limit));
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!
2.7 分區(qū)索引
分區(qū)索引就是簡(jiǎn)單地把一個(gè)索引分成多個(gè)片斷。通過(guò)把一個(gè)索引分成多個(gè)片斷，可以訪問(wèn)更小的片斷(也更快)，并且可以把這些片斷分別存放在不同的磁盤驅(qū)動(dòng)器上(避免I/O問(wèn)題)。B樹和位圖索引都可以被分區(qū)，而HASH索引不可以被分區(qū)?？梢杂泻脦追N分區(qū)方法：表被分區(qū)而索引未被分區(qū)；表未被分區(qū)而索引被分區(qū)；表和索引都被分區(qū)。不管采用哪種方法，都必須使用基于成本的優(yōu)化器。分區(qū)能夠提供更多可以提高性能和可維護(hù)性的可能性
有兩種類型的分區(qū)索引：本地分區(qū)索引和全局分區(qū)索引。每個(gè)類型都有兩個(gè)子類型，有前綴索引和無(wú)前綴索引。表各列上的索引可以有各種類型索引的組合。如果使用了位圖索引，就必須是本地索引。把索引分區(qū)最主要的原因是可以減少所需讀取的索引的大小，另外把分區(qū)放在不同的表空間中可以提高分區(qū)的可用性和可靠性。
在使用分區(qū)后的表和索引時(shí)，Oracle還支持并行查詢和并行DML。這樣就可以同時(shí)執(zhí)行多個(gè)進(jìn)程，從而加快處理這條語(yǔ)句。
2.7.1.本地分區(qū)索引(通常使用的索引)
可以使用與表相同的分區(qū)鍵和范圍界限來(lái)對(duì)本地索引分區(qū)。每個(gè)本地索引的分區(qū)只包含了它所關(guān)聯(lián)的表分區(qū)的鍵和ROWID。本地索引可以是B樹或位圖索引。如果是B樹索引，它可以是唯一或不唯一的索引。
這種類型的索引支持分區(qū)獨(dú)立性，這就意味著對(duì)于單獨(dú)的分區(qū)，可以進(jìn)行增加、截取、刪除、分割、脫機(jī)等處理，而不用同時(shí)刪除或重建索引。Oracle自動(dòng)維護(hù)這些本地索引。本地索引分區(qū)還可以被單獨(dú)重建，而其他分區(qū)不會(huì)受到影響。
2.7.1.1 有前綴的索引
有前綴的索引包含了來(lái)自分區(qū)鍵的鍵，并把它們作為索引的前導(dǎo)。例如，讓我們?cè)俅位仡檖articipant表。在創(chuàng)建該表后，使用survey_id和survey_date這兩個(gè)列進(jìn)行范圍分區(qū)，然后在survey_id列上建立一個(gè)有前綴的本地索引，如下圖所示。這個(gè)索引的所有分區(qū)都被等價(jià)劃分，就是說(shuō)索引的分區(qū)都使用表的相同范圍界限來(lái)創(chuàng)建。
技巧：本地的有前綴索引可以讓Oracle快速剔除一些不必要的分區(qū)。也就是說(shuō)沒(méi)有包含WHERE條件子句中任何值的分區(qū)將不會(huì)被訪問(wèn)，這樣也提高了語(yǔ)句的性能。
2.7.1.2 無(wú)前綴的索引
無(wú)前綴的索引并沒(méi)有把分區(qū)鍵的前導(dǎo)列作為索引的前導(dǎo)列。若使用有同樣分區(qū)鍵(survey_id和survey_date)的相同分區(qū)表，建立在survey_date列上的索引就是一個(gè)本地的無(wú)前綴索引，如下圖所示?？梢栽诒淼娜我涣猩蟿?chuàng)建本地?zé)o前綴索引，但索引的每個(gè)分區(qū)只包含表的相應(yīng)分區(qū)的鍵值。
如果要把無(wú)前綴的索引設(shè)為唯一索引，這個(gè)索引就必須包含分區(qū)鍵的子集。在這個(gè)例子中，我們必須把包含survey和(或)survey_id的列進(jìn)行組合(只要survey_id不是索引的第一列，它就是一個(gè)有前綴的索引)。
技巧：對(duì)于一個(gè)唯一的無(wú)前綴索引，它必須包含分區(qū)鍵的子集。
2.7.2. 全局分區(qū)索引
全局分區(qū)索引在一個(gè)索引分區(qū)中包含來(lái)自多個(gè)表分區(qū)的鍵。一個(gè)全局分區(qū)索引的分區(qū)鍵是分區(qū)表中不同的或指定一個(gè)范圍的值。在創(chuàng)建全局分區(qū)索引時(shí)，必須定義分區(qū)鍵的范圍和值。全局索引只能是B樹索引。Oracle在默認(rèn)情況下不會(huì)維護(hù)全局分區(qū)索引。如果一個(gè)分區(qū)被截取、增加、分割、刪除等，就必須重建全局分區(qū)索引，除非在修改表時(shí)指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。
2.7.2.1 有前綴的索引
通常，全局有前綴索引在底層表中沒(méi)有經(jīng)過(guò)對(duì)等分區(qū)。沒(méi)有什么因素能限制索引的對(duì)等分區(qū)，但Oracle在生成查詢計(jì)劃或執(zhí)行分區(qū)維護(hù)操作時(shí)，并不會(huì)充分利用對(duì)等分區(qū)。如果索引被對(duì)等分區(qū)，就必須把它創(chuàng)建為一個(gè)本地索引，這樣Oracle可以維護(hù)這個(gè)索引，并使用它來(lái)刪除不必要的分區(qū)，如下圖所示。在該圖的3個(gè)索引分區(qū)中，每個(gè)分區(qū)都包含指向多個(gè)表分區(qū)中行的索引條目。
分區(qū)的、全局有前綴索引
技巧：如果一個(gè)全局索引將被對(duì)等分區(qū)，就必須把它創(chuàng)建為一個(gè)本地索引，這樣Oracle可以維護(hù)這個(gè)索引，并使用它來(lái)刪除不必要的分區(qū)。
2.7.2.2 無(wú)前綴的索引
Oracle不支持無(wú)前綴的全局索引。
2.8 位圖連接索引
位圖連接索引是基于兩個(gè)表的連接的位圖索引，在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中使用這種索引改進(jìn)連接維度表和事實(shí)表的查詢的性能。創(chuàng)建位圖連接索引時(shí)，標(biāo)準(zhǔn)方法是連接索引中常用的維度表和事實(shí)表。當(dāng)用戶在一次查詢中結(jié)合查詢事實(shí)表和維度表時(shí)，就不需要執(zhí)行連接，因?yàn)樵谖粓D連接索引中已經(jīng)有可用的連接結(jié)果。通過(guò)壓縮位圖連接索引中的ROWID進(jìn)一步改進(jìn)性能，并且減少訪問(wèn)數(shù)據(jù)所需的I/O數(shù)量。
創(chuàng)建位圖連接索引時(shí)，指定涉及的兩個(gè)表。相應(yīng)的語(yǔ)法應(yīng)該遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;
位圖連接的語(yǔ)法比較特別，其中包含F(xiàn)ROM子句和WHERE子句，并且引用兩個(gè)單獨(dú)的表。索引列通常是維度表中的描述列——就是說(shuō)，如果維度是CUSTOMER，并且它的主鍵是CUSTOMER_ID，則通常索引Customer_Name這樣的列。如果事實(shí)表名為SALES，可以使用如下的命令創(chuàng)建索引：
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;
如果用戶接下來(lái)使用指定Customer_Name列值的WHERE子句查詢SALES和CUSTOMER表，優(yōu)化器就可以使用位圖連接索引快速返回匹配連接條件和Customer_Name條件的行。
位圖連接索引的使用一般會(huì)受到限制：
1）只可以索引維度表中的列。
2）用于連接的列必須是維度表中的主鍵或唯一約束；如果是復(fù)合主鍵，則必須使用連接中的每一列。
3）不可以對(duì)索引組織表創(chuàng)建位圖連接索引，并且適用于常規(guī)位圖索引的限制也適用于位圖連接索引。
注： 本文整理自《Oracle Database 10g 性能調(diào)整與優(yōu)化》
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
網(wǎng)上資源： http://tianlesoftware.download.csdn.net
相關(guān)視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(滿); DBA2 群：62697977(滿)
DBA3 群：63306533; 聊天 群：40132017
|
