Oracle索引详解
Oracle索引
- 一、索引介紹
- 1、什么是索引？
- 2、使用索引的目的
- 二、索引的分類及結構
- 1、邏輯上：
- 2、物理上：
- 三、各種索引詳解
- 1、 B樹索引
- （1）特點：
- （2）技巧：
- 2、位圖索引
- （1）特點：
- （2）技巧：
- 3、 反向索引
- （1）特點：
- （2）技巧：
- 4、HASH索引
- （1）特點：
- 5、函數索引
- 6、分區索引和全局索引
- 7、域索引 Domain
- 四、怎樣建立索引
- 1、普通索引
- 2、唯一索引
- 3、位圖索引
- 4、組合索引
- 5、基于函數索引
- 6、反向鍵索引
- 7.重置索引
- 8.刪除索引
- 五、索引失效細節
- 1.使用不等于操作符(<>, !=)
- 2.使用 is null 或 is not null
- 3.使用函數
- 4.比較不匹配的數據類型
- 5.使用like子句
- 6.使用ｉｎ
- 7.如果能不用到排序，則盡量避免排序
一、索引介紹
1、什么是索引？
??索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數據；Oracle存儲索引的數據結構是B樹，位圖索引也是如此，只不過是葉子節點不同B數索引；索引由根節點、分支節點和葉子節點組成，上級索引塊包含下級索引塊的索引數據，葉節點包含索引數據和確定行實際位置的rowid。
2、使用索引的目的
??當查詢返回的記錄數排序表<40%非排序表 <7%且表的碎片較多（頻繁增加、刪除）時可以加快查詢速度減少I/O操作消除磁盤排序
二、索引的分類及結構
1、邏輯上：
Single column/Concatenated 單行索引/多行索引
Unique/NonUnique 唯一索引/非唯一索引
2、物理上：
B-tree B樹索引
Bitmap 位圖索引
REVERSE 反向索引
HASHHASH索引
Function-based基于函數的索引
Partitioned/NonPartitioned 分區索引/非分區索引
Domain 域索引
三、各種索引詳解
1、 B樹索引
??Oracle數據庫中最常見的索引類型是b-tree索引，也就是B-樹索引，以其同名的計算科學結構命名。CREATE INDEX語句時，默認就是在創建b-tree索引。沒有特別規定可用于任何情況。
（1）特點：
??適合與大量的增、刪、改（OLTP）
??不能用包含OR操作符的查詢；
??適合高基數的列（唯一值多）
??典型的樹狀結構；
??每個結點都是數據塊；
??大多都是物理上一層、兩層或三層不定，邏輯上三層；
??葉子塊數據是排序的，從左向右遞增；
??在分支塊和根塊中放的是索引的范圍；
（2）技巧：
??索引列的值都存儲在索引中。因此，可以建立一個組合(復合)索引，這些索引可以直接滿足查詢，而不用訪問表。這就不用從表中檢索數據，從而減少了I/O量。
2、位圖索引
??位圖索引非常適合于決策支持系統(Decision Support System，DSS)和數據倉庫，它們不應該用于通過事務處理應用程序訪問的表。它們可以使用較少到中等基數(不同值的數量)的列訪問非常大的表。盡管位圖索引最多可達30個列，但通常它們都只用于少量的列。
例如，您的表可能包含一個稱為Sex的列，它有兩個可能值：男和女。這個基數只為2，如果用戶頻繁地根據Sex列的值查詢該表，這就是位圖索引的基列。當一個表內包含了多個位圖索引時，您可以體會到位圖索引的真正威力。如果有多個可用的位圖索引，Oracle就可以合并從每個位圖索引得到的結果集，快速刪除不必要的數據。
（1）特點：
??適合與決策支持系統；
??做UPDATE代價非常高；
??非常適合OR操作符的查詢；
??基數比較少的時候才能建位圖索引；
（2）技巧：
??對于有較低基數的列需要使用位圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女(基數僅為2)。位圖對于低基數(少量的不同值)列來說非常快，這是因為索引的尺寸相對于B樹索引來說小了很多。因為這些索引是低基數的B樹索引，所以非常小，因此您可以經常檢索表中超過半數的行，并且仍使用位圖索引。
??當大多數條目不會向位圖添加新的值時，位圖索引在批處理(單用戶)操作中加載表(插入操作)方面通常要比B樹做得好。當多個會話同時向表中插入行時不應該使用位圖索引，在大多數事務處理應用程序中都會發生這種情況。
??在一個查詢中合并多個位圖索引后，可以使性能顯著提高。位圖索引使用固定長度的數據類型要比可變長度的數據類型好。較大尺寸的塊也會提高對位圖索引的存儲和讀取性能。
3、 反向索引
??這個索引不常見，但是特定情況特別有效，比如一個varchar(5)位字段(員工編號)含值
（10001,10002,10033,10005,10016…）
這種情況默認索引分布過于密集，不能利用好服務器的并行
但是反向之后10001,20001,33001,50001,61001就有了一個很好的分布，能高效的利用好并行運算。
（1）特點：
??不可以將反轉鍵索引與位圖索引或索引組織表結合使用。因為不能對位圖索引和索引組織表進行反轉鍵處理。
（2）技巧：
??如果您的磁盤容量有限，同時還要執行大量的有序載入，就可以使用反轉鍵索引。
4、HASH索引
??使用HASH索引必須要使用HASH集群。建立一個集群或HASH集群的同時，也就定義了一個集群鍵。這個鍵告訴Oracle如何在集群上存儲表。在存儲數據時，所有與這個集群鍵相關的行都被存儲在一個數據庫塊上。如果數據都存儲在同一個數據庫塊上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過執行一個HASH函數和I/O來訪問數據——而通過使用一個二元高度為4的B樹索引來訪問數據，則需要在檢索數據時使用4個I/O。其中的查詢是一個等價查詢，用于匹配HASH列和確切的值。Oracle可以快速使用該值，基于HASH函數確定行的物理存儲位置。
??HASH索引可能是訪問數據庫中數據的最快方法，但它也有自身的缺點。集群鍵上不同值的數目必須在創建HASH集群之前就要知道。需要在創建HASH集群的時候指定這個值。低估了集群鍵的不同值的數字可能會造成集群的沖突(兩個集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的。沖突會造成用來存儲額外行的緩沖溢出，然后造成額外的I/O。如果不同HASH值的數目已經被低估，您就必須在重建這個集群之后改變這個值。
??ALTER CLUSTER命令不能改變HASH鍵的數目。HASH集群還可能浪費空間。如果無法確定需要多少空間來維護某個集群鍵上的所有行，就可能造成空間的浪費。如果不能為集群的未來增長分配好附加的空間，HASH集群可能就不是最好的選擇。如果應用程序經常在集群表上進行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來的增長分配好集群的剩余空間量，全表掃描可能非常消耗資源。
在實現HASH集群之前一定要小心。您需要全面地觀察應用程序，保證在實現這個選項之前已經了解關于表和數據的大量信息。通常，HASH對于一些包含有序值的靜態數據非常有效。
（1）特點：
??可以在表中創建基于函數的索引。如果沒有基于函數的索引，任何在列上執行了函數的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基于函數的索引：
select * from emp where UPPER(job) = 'MGR';下面的查詢使用JOB列上的索引，但是它將不會返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';??可以創建這樣的索引，允許索引訪問支持基于函數的列或數據。可以對列表達式UPPER(job)創建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));??盡管基于函數的索引非常有用，但在建立它們之前必須先考慮下面一些問題：
能限制在這個列上使用的函數嗎？如果能，能限制所有在這個列上執行的所有函數嗎
??是否有足夠應付額外索引的存儲空間？
??在每列上增加的索引數量會對針對該表執行的DML語句的性能帶來何種影響？
??基于函數的索引非常有用，但在實現時必須小心。在表上創建的索引越多，INSERT、UPDATE和DELETE語句的執行就會花費越多的時間。
5、函數索引
??可以在表中創建基于函數的索引。如果沒有基于函數的索引，任何在列上執行了函數的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基于函數的索引：
select * from emp where UPPER(job) = 'MGR';下面的查詢使用JOB列上的索引，但是它將不會返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';??可以創建這樣的索引，允許索引訪問支持基于函數的列或數據。可以對列表達式UPPER(job)創建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));盡管基于函數的索引非常有用，但在建立它們之前必須先考慮下面一些問題：
??能限制在這個列上使用的函數嗎？如果能，能限制所有在這個列上執行的所有函數嗎
??是否有足夠應付額外索引的存儲空間？
??在每列上增加的索引數量會對針對該表執行的DML語句的性能帶來何種影響？
基于函數的索引非常有用，但在實現時必須小心。在表上創建的索引越多，INSERT、UPDATE和DELETE語句的執行就會花費越多的時間。
6、分區索引和全局索引
??分區索引就是簡單地把一個索引分成多個片斷。通過把一個索引分成多個片斷，可以訪問更小的片斷(也更快)，并且可以把這些片斷分別存放在不同的磁盤驅動器上(避免I/O問題)。B樹和位圖索引都可以被分區，而HASH索引不可以被分區。可以有好幾種分區方法：表被分區而索引未被分區；表未被分區而索引被分區；表和索引都被分區。不管采用哪種方法，都必須使用基于成本的優化器。分區能夠提供更多可以提高性能和可維護性的可能性
有兩種類型的分區索引：本地分區索引和全局分區索引。每個類型都有兩個子類型，有前綴索引和無前綴索引。表各列上的索引可以有各種類型索引的組合。如果使用了位圖索引，就必須是本地索引。把索引分區最主要的原因是可以減少所需讀取的索引的大小，另外把分區放在不同的表空間中可以提高分區的可用性和可靠性。
??在使用分區后的表和索引時，Oracle還支持并行查詢和并行DML。這樣就可以同時執行多個進程，從而加快處理這條語句。
??可以使用與表相同的分區鍵和范圍界限來對本地索引分區。每個本地索引的分區只包含了它所關聯的表分區的鍵和ROWID。本地索引可以是B樹或位圖索引。如果是B樹索引，它可以是唯一或不唯一的索引。
??這種類型的索引支持分區獨立性，這就意味著對于單獨的分區，可以進行增加、截取、刪除、分割、脫機等處理，而不用同時刪除或重建索引。Oracle自動維護這些本地索引。本地索引分區還可以被單獨重建，而其他分區不會受到影響。
7、域索引 Domain
??域索引實際為用戶自定義索引，域索引主要對存儲在數據庫中的媒體，圖像數據進行索引，這些數據在oracle中基本上以BLOB類型存儲，不同的應用存儲格式也不同，oracle不可能提供某一種現成的算法對這些數據進行索引，為了能夠對這些類型數據快速訪問，oracle提供了現成的接口函數，用戶可以針對自己的數據格式實現這些接口函數，以達到對這些數據的快速訪問。
四、怎樣建立索引
CREATE UNIQUE | 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 | NOLOGGINGCOMPUTE STATISTICSNOCOMPRESS | COMPRESS<nn>NOSORT | REVERSEPARTITION | GLOBAL PARTITION<partition_setting> UNIQUE | BITMAP：指定UNIQUE為唯一值索引，BITMAP為位圖索引，省略為B-Tree索引。
<column_name> | <expression> ASC | DESC：可以對多列進行聯合索引，當為expression時即“基于函數的索引”
TABLESPACE：指定存放索引的表空間(索引和原表不在一個表空間時效率更高)
STORAGE：可進一步設置表空間的存儲參數
LOGGING | NOLOGGING：是否對索引產生重做日志(對大表盡量使用NOLOGGING來減少占用空間并提高效率)
COMPUTE STATISTICS：創建新索引時收集統計信息
NOCOMPRESS | COMPRESS<nn>：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個鍵列中出現的重復值)
NOSORT | REVERSE：NOSORT表示與表中相同的順序創建索引，REVERSE表示相反順序存儲索引值
PARTITION | NOPARTITION：可以在分區表和未分區表上對創建的索引進行分區
1、普通索引
create index index_text_txt on test(txt);2、唯一索引
create unique index <index_name> on <table_name>(<coiumn_name>);3、位圖索引
create bitmap index <index_name> on <table_name>(<column_name>)4、組合索引
create index <index_name> on <table_name>(<column_name1><column_name2>)5、基于函數索引
create index <index_name> on <table_name>(column_name) reverse; create index <index_name> on <table_name>(upper(column_name))6、反向鍵索引
create index <index_name> on <table_name>(column_name) reverse;7.重置索引
alter index <index_name> rebuild;8.刪除索引
drop index <index_name>五、索引失效細節
1.使用不等于操作符(<>, !=)
??下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執行一次全表掃描
select * from dept where staff_num <> 1000;??但是開發中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎？
??有！
??通過把用 or 語法替代不等號進行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。
2.使用 is null 或 is not null
??使用 is null 或is nuo null也會限制索引的使用，因為數據庫并沒有定義null值。如果被索引的列中有很多null，就不會使用這個索引（除非索引是一個位圖索引，關于位圖索引，會在以后的blog文章里做詳細解釋）。在sql語句中使用null會造成很多麻煩。
解決這個問題的辦法就是：建表時把需要索引的列定義為非空(not null)
3.使用函數
??如果沒有使用基于函數的索引，那么where子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。下面的查詢就不會使用索引：
select * from staff where trunc(birthdate) = '01-MAY-82';??但是把函數應用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以通過索引進行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);4.比較不匹配的數據類型
??比較不匹配的數據類型也是難于發現的性能問題之一。下面的例子中，dept_id是一個varchar2型的字段，在這個字段上有索引，但是下面的語句會執行全表掃描。
select * from dept where dept_id = 900198;??這是因為oracle會自動把where子句轉換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。把SQL語句改為如下形式就可以使用索引
select * from dept where dept_id = '900198';5.使用like子句
??使用like子句查詢時，數據需要把所有的記錄都遍歷來進行判斷，索引不能發揮作用，這種情況也要盡量避免。
Like 的字符串中第一個字符如果是‘%’則用不到索引
Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到
6.使用ｉｎ
??盡管In寫法要比exists簡單一些，exists一般來說性能要比In要高的多
用In還是用Exists的時機，當in的集合比較小的時候,或者用Exists無法用到選擇性高的索引的時候，用In要好，否則就要用Exists
例：
??假定TEST表的dt字段是date類型的并且對dt建了索引。
??如果要查‘20041010’一天的數據.下面的方法用不到索引
7.如果能不用到排序，則盡量避免排序
??用到排序的情況有集合操作。Union ,minus ,intersect等,注：union all 是不排序的。Order by、Group by、Distinct、In 有時候也會用到排序，確實要排序的時候也盡量要排序小數據量,盡量讓排序在內存中執行，有文章說，內存排序的速度是硬盤排序的1萬倍。
總結
以上是生活随笔為你收集整理的Oracle索引详解的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Cocos2d-x 3.17.2 集成X
- 下一篇： Kettle文件下载