索引的概念
索引是一個可選的結構、與簇表和表相關聯，可以提高讀取資料的速度，可以在表的一列或多列上建立索引，索引直接指向包含所查詢值的行的位置，減少磁碟I/O，與索引的表時相互獨立的物理結構，Oracle自動使用並維護索引，插入、刪除、更新表後，自動更新。如果堆表沒有索引,那麼資料庫必須執行全表掃描來找到一個值

索引的建立語法
CREATE UNIUQE | BITMAP INDEX .
      ON .
          (|expression>ASC|DESC,
          |expression>ASC|DESC,...)
      TABLESPACE
      STORAGE
      LOGGING|NOLOGGING
      COMPUTE STATISTICS
      NOCOMPRESS|COMPRESS
      NOSORT|REVERSE
      PARTITION|GLOBAL PARTITION

相關說明
1）UNIQUE|BITMAP:指定UNIQUE為唯一索引，BITMAP為點陣圖索引，省略為B-Tree索引。
2）>|expression>ASC|DESC：可以對多列進行聯合索引，當為expression時即“基於函式的索引”
3）TABLESPACE：指定存放索引的表空間（索引和原表不在一個表空間時效率更高）
4）STORAGE：設定表空間的儲存引數
5）LOGGING | NOLOGGING：是否對索引產生重做日誌（對大表儘量使用NOLOGGING來減少佔用空間並提高效率 )
6）COMPUTE STATISTICS：建立新索引時收集統計資訊
7）NOCOMPRESS|COMPRESS：是否使用“鍵壓縮”（使用鍵壓縮可以刪除一個鍵列中出現的重複值）
8）NOSORT|REVERSE：NOSORT表示與表中相同的順序建立索引，REVERSE表示相反順序儲存索引值
9）PARTITION|NOPARTITION：可以在分割槽表和未分割槽表上對建立索引進行分割槽

索引的特點：

1）透過建立唯一性索引，可以保證資料庫表中的每一行資料的唯一性
2）可以大大加快資料的檢索速度，
3）可以加速表和表之間的連線，特別是在實現資料的參考完整性
4）使用分組和排序子句進行資料檢索時，可以減少查詢中分組和排序的時間
5）透過使用索引，在查詢過程中，使用最佳化隱藏器，提高系統的效能。

索引不足
1）建立和維護索引要耗費時間，這種時間隨著資料量的增加而增加
2）索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，需要的空間更大
3）當對錶中的資料進行增加、刪除、修改的時候，索引要動態維護，降低了資料的維護速度。

建立索引列的特點
1）經常需要搜尋的列
2）作為主鍵的列，強制該列的唯一性和組織表中資料的排列結構
3）經常用在連線的列上，加速連線
4）經常根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的
5）經常需啊喲排序的列上建立索引，因為索引已經排序，查詢可以利用索引的排序，加快排序查詢時間
6）經常使用在WHERE子句中的列上建立索引，加快條件判斷速度。

不應該建立索引列的特點
1）在查詢中很少使用或者參考的列不應該建立索引
2）只有很少資料值的列
3）對於定義為blob資料型別的列不應該增加索引
4）修改效能遠遠大於檢索效能時

限制索引
1）使用不等於運算子（<>,!=）
2）使用IS NULL 或 IS NOT NULL 如果索引列在某些行中存在NULL值，就不會使用這個索引（不包括點陣圖索引）
3）使用函式，如果不使用基於函式的索引，那麼在SQL語句的WHERE子句中對存在的索引的列使用函式時，會使最佳化器忽略掉這些索引。
4）比較不匹配的資料型別
例：abc_number是一個varchar2型別，在abc_number欄位上有索引。
      下面語句將執行全表掃描：
      select a,b,c,d,e from A where abc_number=123456;
      Oracle會自動把where子句變成to_number(abc_number)=123456
      限制了索引的使用，改為以下查詢可以使用索引：
select a,b,c,d,e from A where abc_number='123456';
        不匹配的資料型別之間比較會讓Oracle自動限制索引的使用

查詢索引：DBA_INDEXES, USER_INDEXES, USER_IND_COLUMNS

Oracle rowid
Oracle透過每個行的rowid，Oracle索引提供了訪問單行資料的能力，ROWID直接指向單獨行的線路圖

SQL> select rowid from dual;

ROWID
------------------
AAAAEC AAB AAAAgi AAA

rowid=物件編號（6）+檔案編號（3）+塊編號（6）+ 行編號（3）=18位

ROWID的格式如下：
物件編號      檔案編號      塊編號      行編號
AAAAEC        AAB           AAAAgi        AAA

索引分類

B樹索引（預設型別）
點陣圖索引
HASH索引
索引組織表索引
反轉鍵（reverse key）索引
基於函式的索引
分割槽索引
點陣圖連線索引

B樹索引（預設型別）
B樹索引在 Oracle中是一個通用索引。在建立時它就預設的型別。B樹索引可以是一個列的（簡單）索引，也可以是組合/複合（多個列）索引。 B樹索引最多可以包括 32列
B樹索引包括樹枝塊和樹葉塊
樹枝塊：包含鏈中下一個塊的ID號
樹葉塊：包含了索引值、ROWID、以及指向前一個和後一個樹葉塊的指標。

下圖為B樹索引的結構圖，

B-tree特點：
適合與大陸的增、刪、改（OLTP）
不能用包含OR運算子的查詢
適合高基數的列（唯一值多）
典型的樹狀結構
每個節點都是資料塊

點陣圖索引
點陣圖索引適用於離散度較低的列，它的葉塊中存放key, start rowid-end rowid,並應用一個函式把點陣圖中相應key值置1，點陣圖索引在邏輯or時效率最高。

例：
SQL>create bitmap index job_bitmap on emp1(job);

值/行   1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST   0 0 0 0 0 0 0 1 0 0 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER   0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT       0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN    0 1 1 0 1 0 0 0 0 0 0 0 0 0

SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';

值/行       1 2 3 4 5 6 7 8 9 10 11 12 13 14
-----------------------------------------------------------------------------
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
-----------------------------------------------------------------------------
or的結果 1 0 0 1 0 1 1 0 0    0 1 1 0 1

常用的B樹索引型別：

唯一或非唯一索引（Unique or non_unique): 唯一索引指鍵值不重複。
SQL> create unique index empno_idx on emp1(empno);
或
SQL> create index empno_idx on emp1(empno);

組合索引(Composite)：繫結了兩個或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);

反向鍵索引(Reverse)：將位元組倒置後組織鍵值。當使用序列產生主鍵索引時，可以防止葉節點出現熱塊現象（考點）。
SQL> create index mgr_idx on emp1(mgr) reverse;

函式索引(Function base)：以索引列值的函式值為鍵值去組織索引
SQL> create index fun_idx on emp1(lower(ename));

壓縮（Compress)：重複鍵值只儲存一次，就是說重複的鍵值在葉塊中就存一次，後跟所有與之匹配的rowid字串。
SQL> create index comp_idx on emp1(sal) compress;

升序或降序（Ascending or descending)：葉節點中的鍵值排列預設是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);

可以更改索引屬性：
alter index xxx ....

索引相關的資料字典

USER_INDEXES //索引主要資訊
USER_IND_CULUMNS //索引列的資訊

索引詳解

相關文章