索引的概念及創(chuàng)建
?
??? 學(xué)習(xí)一下索引。索引應(yīng)該是Oracle的初級內(nèi)容中比較重要的一部分。因為是否創(chuàng)建索引，對SQL的查詢效率會有比較大的影響。當(dāng)然對于何種索引，索引如何工作的原理，基本上只需要了解就可以了，不需要太過于深入，除非以后要做專門的SQL調(diào)優(yōu)，否則一般的情況下很少會需要設(shè)置索引類型的情況。
?
?
什么是索引：
?
??? 索引是一種與表有關(guān)的數(shù)據(jù)庫結(jié)構(gòu)，是建立在表的一列或多個列上的輔助對象。使用索引可以有一下好處：
??? 1、加快查詢速度；
??? 2、減少I/O操作
??? 3、消除磁盤排序
?
?
索引的創(chuàng)建格式：
?
?
??? 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：可以對多列進(jìn)行聯(lián)合索引，當(dāng)為expression時即“基于函數(shù)的索引”
??? TABLESPACE：指定存放索引的表空間(索引和原表不在一個表空間時效率更高)
??? STORAGE：可進(jìn)一步設(shè)置表空間的存儲參數(shù)
??? LOGGING | NOLOGGING：是否對索引產(chǎn)生重做日志(對大表盡量使用NOLOGGING來減少占用空間并提高效率)
??? COMPUTE STATISTICS：創(chuàng)建新索引時收集統(tǒng)計信息
??? NOCOMPRESS | COMPRESS<nn>：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個鍵列中出現(xiàn)的重復(fù)值)
??? NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲索引值
??? PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對創(chuàng)建的索引進(jìn)行分區(qū)
?
??? 使用USER_IND_COLUMNS查詢某個TABLE中的相應(yīng)字段索引建立情況
??? 使用DBA_INDEXES/USER_INDEXES查詢所有索引的具體設(shè)置情況。
?
?
?
?
??? 在Oracle中的索引可以分為：B樹索引、位圖索引、反向鍵索引、基于函數(shù)的索引、簇索引、全局索引、局部索引等，下面逐一講解：
?
一、B樹索引：
?
??? 最常用的索引，各葉子節(jié)點中包括的數(shù)據(jù)有索引列的值和數(shù)據(jù)表中對應(yīng)行的ROWID，簡單的說，在B樹索引中，是通過在索引中保存排過續(xù)的索引列值與相對應(yīng)記錄的ROWID來實現(xiàn)快速查詢的目的。其邏輯結(jié)構(gòu)如圖：
?
??? 
?
??? 可以保證無論用戶要搜索哪個分支的葉子結(jié)點，都需要經(jīng)過相同的索引層次，即都需要相同的I/O次數(shù)。
?
??? B樹索引的創(chuàng)建示例：
??? create index ind_t on t1(id)
;
?
??? 注1：索引的針對字段創(chuàng)建的，相同字段不能創(chuàng)建一個以上的索引；
??? 注2：默認(rèn)的索引是不唯一的，但是也可以加上unique，表示該索引的字段上沒有重復(fù)值(定義unique約束時會自動創(chuàng)建)；
??? 注3：創(chuàng)建主鍵時，默認(rèn)在主鍵上創(chuàng)建了B樹索引，因此不能再在主鍵上創(chuàng)建索引。
?
?
二、位圖索引：
?
??? 有些字段中使用B樹索引的效率仍然不高，例如性別的字段中，只有“男、女”兩個值，則即便使用了B樹索引，在進(jìn)行檢索時也將返回接近一半的記錄。
??? 所以當(dāng)字段的基數(shù)很低時，需要使用位圖索引。(“低”的標(biāo)準(zhǔn)是取值數(shù)量 < 行數(shù)*1%)
?
??? 
??? 位圖索引的邏輯結(jié)構(gòu)如上圖所示：索引中不再記錄rowid和鍵值，而是將每個值作為一列，用0和1表示該行是否等于該鍵值(0表示否;1表示是)。其中位圖索引的行順序與原表的行順序一致，可以在查詢數(shù)據(jù)的過程中對應(yīng)計算出行的原始物理位置。
?
??? 位圖索引的創(chuàng)建示例：
??? create bitmap index ind_t on t1(type);
?
??? 注：位圖索引不可能是唯一索引，也不能進(jìn)行鍵值壓縮。
?
?
三、反向鍵索引：
?
??? 考慮這個情況：某一字段的值是1-1000順序排列，建立B樹索引后依舊遞增，到后來該B數(shù)索引不斷在后面增加分支，會形成如下如的不對稱樹：
??? 
?
??? 反向鍵索引是一種特殊的B樹索引，在存儲構(gòu)造中與B樹索引完全相同，但是針對數(shù)值時，反向鍵索引會先反向每個鍵值的字節(jié)，然后對反向后的新數(shù)據(jù)進(jìn)行索引。例如輸入2008則轉(zhuǎn)換為8002，這樣當(dāng)數(shù)值一次增加時，其反向鍵在大小中的分布仍然是比較平均的。
?
??? 反向鍵索引的創(chuàng)建示例：
??? create index ind_t on t1(id) reverse;
?
??? 注：鍵的反轉(zhuǎn)由系統(tǒng)自行完成。對于用戶是透明的。
?
?
四、基于函數(shù)的索引：
?
??? 有的時候，需要進(jìn)行如下查詢：select * from t1 where to_char(date,'yyyy')>'2007';
??? 但是即便在date字段上建立了索引，還是不得不進(jìn)行全表掃描。在這種情況下，可以使用基于函數(shù)的索引。其創(chuàng)建語法如下：
??? create index ind_t on t1(to_char(date,'yyyy'));
?
??? 注：簡單來說，基于函數(shù)的索引，就是將查詢要用到的表達(dá)式作為索引項。
?
?
五、全局索引和局部索引：
?
??? 這個索引貌似很復(fù)雜，其實很簡單。總得來說一句話，就是無論怎么分區(qū)，都是為了方便管理。
?
??? 具體索引和表的關(guān)系有三種：
??? 1、局部分區(qū)索引：分區(qū)索引和分區(qū)表1對1
??? 2、全局分區(qū)索引：分區(qū)索引和分區(qū)表N對N
??? 3、全局非分區(qū)索引：非分區(qū)索引和分區(qū)表1對N
?
??? 創(chuàng)建示例：
?
??? 首先創(chuàng)建一個分區(qū)表
??? 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
??? );
?
??? 創(chuàng)建局部分區(qū)索引(1v1)：
??? create index ind_t on student(stuno)
??? local(
???? partition part_01 tablespace A2,
???? partition part_02 tablespace A1
??? ); --local后面可以不加
?
??? 創(chuàng)建全局分區(qū)索引(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
??? ); --只可以進(jìn)行range分區(qū)
?
??? 創(chuàng)建全局非分區(qū)索引(1vN)
???
create index ind_t on student(stuno) GLOBAL;
?
??? 注：全局非分區(qū)索引不能是位圖索引，但可以是唯一索引。
?
?