oracle有哪些索引

青灯夜游原創: 2022-04-18 17:18:098336瀏覽

oracle的索引類型有：非唯一索引、唯一索引、點陣圖索引、局部有前綴分區索引、局部無前綴分區索引、全域有前綴分區索引、雜湊分區索引、基於函數的索引。索引需在表中插入資料後創建，唯一索引可用「create unique index」語句建立。

oracle有哪些索引

本教學操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。

什麼是索引？

索引是建立在表格的一列或多個列上的輔助對象，目的是加快存取表中的資料；
Oracle儲存索引的資料結構是B*樹（平衡樹），點陣圖索引也是如此，只不過是葉子節點不同B*數索引；
索引由根節點、分支節點和葉子節點組成，上級索引塊包含下級索引塊的索引數據，葉節點包含索引資料和確定行實際位置的rowid。

索引說明

1）索引是資料庫物件之一，用於加快資料的檢索，類似於書籍的索引。在資料庫中索引可以減少資料庫程式查詢結果時需要讀取的資料量，類似於在書籍中我們利用索引可以不用翻閱整本書即可找到想要的資訊。

2）索引是建立在表上的可選物件；索引的關鍵在於透過一組排序後的索引鍵來取代預設的全表掃描檢索方式，從而提高檢索效率

#3）索引在邏輯上和物理上都與相關的表和資料無關，當建立或刪除索引時，不會影響基本的表；

4）索引一旦建立，在表上進行DML操作時（例如執行插入、修改或刪除相關操作時），oracle會自動管理索引，索引刪除，不會對錶產生影響

5）索引對使用者是透明的，無論表上是否有索引，sql語句的用法不變

6）oracle在建立主鍵時會自動在該列上建立索引

使用索引的目的：

加快查詢速度
減少I/O操作
消除磁碟排序（索引能加快排序速度）

#何時使用索引：

查詢傳回的記錄數排序表bf1112a0a45f39a8d4cdd910fc8646d8, != )
下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執行一次全表掃描<pre class="prettyprint">select * from dept where staff_num <> 1000;</pre>但是開發中的確需要這樣的查詢，難道沒有解決問題的方法了嗎？有！透過把用 or 語法取代不等號來查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。
```
select * from dept shere staff_num < 1000 or dept_id > 1000;
```
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) = &#39;01-MAY-82&#39;;
```
但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。
```
select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 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 = &#39;900198&#39;;
```
5、使用like子句

使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

Like 的字符串中第一个字符如果是‘%’则用不到索引
```
Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到
```
6、使用IN

尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多

用In还是用Exists的时机

当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists
例：
```
select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引
```
假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’一天的数据.下面的方法用不到索引
```
Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
```
而以下将会用到索引。
```
select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1
```
7、如果能不用到排序，则尽量避免排序。

用到排序的情况有
集合操作。Union ,minus ,intersect等,注：union all 是不排序的。
```
Order byGroup byDistinctIn
```
有时候也会用到排序
确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

在排序的字段上创建索引，让排序在内存中执行，加快排序速度。

8、在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

解决：执行表分析。获取表的最新信息。

9、获取的数据量过大，全部扫描效率更高

10、索引字段的值分散率太低，值太集中，如类型字段都是1,2，状态类型Y-有效/N-无效。这类型的字段最好别建索引。

尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

推荐教程：《Oracle教程》

以上是oracle有哪些索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

上一篇：oracle服務有哪些下一篇：oracle服務有哪些

看更多