轉載 索引的概念及建立 學習一下索引。索引應該是Oracle的初級内容中比較重要的一部分。因為是否建立索引,對SQL的查詢效率會有比較大的影響。當然對于何種索引,索引如何工作的原理,基本上隻需要了解就可以了,不需要太過于深入,除非以後要做專門的SQL調優,否則一般的情況下很少會需要設定索引類型的情況。 什麼是索引: 索引是一種與表有關的資料庫結構,是建立在表的一列或多個列上的輔助對象。使用索引可以有一下好處:
1、加快查詢速度;
2、減少I/O操作
3、消除磁盤排序 索引的建立格式:
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> 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:可以在分區表和未分區表上對建立的索引進行分區 使用USER_IND_COLUMNS查詢某個TABLE中的相應字段索引建立情況 使用DBA_INDEXES/USER_INDEXES查詢所有索引的具體設定情況。 在Oracle中的索引可以分為:B樹索引、位圖索引、反向鍵索引、基于函數的索引、簇索引、全局索引、局部索引等,下面逐一講解: 一、B樹索引: 最常用的索引,各葉子節點中包括的資料有索引列的值和資料表中對應行的ROWID,簡單的說,在B樹索引中,是通過在索引中儲存排過續的索引列值與相對應記錄的ROWID來實作快速查詢的目的。其邏輯結構如圖:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZukUMxQWO2UTOwMjNxMTMfBzLchTMvwVOwETMwIzLcRnbl1GajFGd0F2LcRXZu5ibkN3YukGavw1LcpDc0RHaiojIsJye.gif)
可以保證無論使用者要搜尋哪個分支的葉子結點,都需要經過相同的索引層次,即都需要相同的I/O次數。 B樹索引的建立示例: create index ind_t on t1(id) ; 注1:索引的針對字段建立的,相同字段不能建立一個以上的索引; 注2:預設的索引是不唯一的,但是也可以加上unique,表示該索引的字段上沒有重複值(定義unique限制時會自動建立); 注3:建立主鍵時,預設在主鍵上建立了B樹索引,是以不能再在主鍵上建立索引。 二、位圖索引: 有些字段中使用B樹索引的效率仍然不高,例如性别的字段中,隻有“男、女”兩個值,則即便使用了B樹索引,在進行檢索時也将傳回接近一半的記錄。 是以當字段的基數很低時,需要使用位圖索引。(“低”的标準是取值數量 < 行數*1%)
位圖索引的邏輯結構如上圖所示:索引中不再記錄rowid和鍵值,而是将每個值作為一列,用0和1表示該行是否等于該鍵值(0表示否;1表示是)。其中位圖索引的行順序與原表的行順序一緻,可以在查詢資料的過程中對應計算出行的原始實體位置。 位圖索引的建立示例: create bitmap index ind_t on t1(type); 注:位圖索引不可能是唯一索引,也不能進行鍵值壓縮。 三、反向鍵索引: 考慮這個情況:某一字段的值是1-1000順序排列,建立B樹索引後依舊遞增,到後來該B數索引不斷在後面增加分支,會形成如下如的不對稱樹:
反向鍵索引是一種特殊的B樹索引,在存儲構造中與B樹索引完全相同,但是針對數值時,反向鍵索引會先反向每個鍵值的位元組,然後對反向後的新資料進行索引。例如輸入2008則轉換為8002,這樣當數值一次增加時,其反向鍵在大小中的分布仍然是比較平均的。 反向鍵索引的建立示例: create index ind_t on t1(id) reverse; 注:鍵的反轉由系統自行完成。對于使用者是透明的。 四、基于函數的索引: 有的時候,需要進行如下查詢:select * from t1 where to_char(date,'yyyy')>'2007'; 但是即便在date字段上建立了索引,還是不得不進行全表掃描。在這種情況下,可以使用基于函數的索引。其建立文法如下: create index ind_t on t1(to_char(date,'yyyy')); 注:簡單來說,基于函數的索引,就是将查詢要用到的表達式作為索引項。 五、全局索引和局部索引: 這個索引貌似很複雜,其實很簡單。總得來說一句話,就是無論怎麼分區,都是為了友善管理。 具體索引和表的關系有三種: 1、局部分區索引:分區索引和分區表1對1 2、全局分區索引:分區索引和分區表N對N 3、全局非分區索引:非分區索引和分區表1對N 建立示例: 首先建立一個分區表 create table student ( stuno number(5), sname vrvhar2(10), deptno number(5) ) partition by hash (deptno) ( partition part_01 tablespace A1, partition part_02 tablespace A2 ); 建立局部分區索引(1v1): create index ind_t on student(stuno) local( partition part_01 tablespace A2, partition part_02 tablespace A1 ); --local後面可以不加 建立全局分區索引(NvN): create index ind_t on student(stuno) global partition by range(stuno) ( partition p1 values less than(1000) tablespace A1, partition p2 values less than(maxvalue) tablespace A2 ); --隻可以進行range分區 建立全局非分區索引(1vN) create index ind_t on student(stuno) GLOBAL; 注:全局非分區索引不能是位圖索引,但可以是唯一索引。