Oracle——03索引
Oracle——03索引
Oracle 索引 詳解
建立Oracle索引的標準語法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空間名;
建立反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空間名;
1、明確地建立索引
create index index_name on table_name(field_name)
tablespace tablespace_name
pctfree 5
initrans 2
maxtrans 255
storage
(
minextents 1
maxextents 16382
pctincrease 0
);
2、建立基於函式的索引
常用與UPPER、LOWER、TO_CHAR(date)等函式分類上，例：
create index idx_func on emp(UPPER(ename)) tablespace tablespace_name;
3、建立點陣圖索引
對基數較小，且基數相對穩定的列建立索引時，首先應該考慮點陣圖索引，例：
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
4、明確地建立唯一索引
可以用create unique index語句來建立唯一索引，例：
create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
5、建立與約束相關的索引
可以用using index字句，為與unique和primary key約束相關的索引，例：
alter table table_name
add constraint PK_primary_keyname primary key(field_name)
using index tablespace tablespace_name;
如何建立區域性區索引？
1)基礎表必須是分割槽表
2)分割槽數量與基礎表相同
3)每個索引分割槽的子分割槽數量與相應的基礎表分割槽相同
4)基礎表的自分割槽中的行的索引項，被儲存在該索引的相應的自分割槽中，例如
create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID)
Pctfree 5
Tablespace TBS_AK01_IDX
Storage(
MaxExtents 32768
PctIncrease 0
FreeLists 1
FreeList Groups 1
)
local
/
如何建立範圍分割槽的全域性索引？
基礎表可以是全域性表和分割槽表
create index idx_start_date on tg_cdr01(start_date)
global partition by range(start_date)
(partition p01_idx vlaues less than ('0106')
partition p01_idx vlaues less than ('0111')
...
partition p01_idx vlaues less than ('0401'))
/
如何重建現存的索引？
重建現存的索引的當前時刻不會影響查詢
重建索引可以刪除額外的資料塊
提高索引查詢效率
alter index idx_name rebuild nologging;
對於分割槽索引
alter index idx_name rebuild partition partition_name nologging;
刪除索引的原因？
1)不再需要的索引
2)索引沒有針對其相關的表所釋出的查詢提供所期望的效能改善
3)應用沒有用該索引來查詢資料
4)該索引無效，必須在重建之前刪除該索引
5)該索引已經變的太碎了，必須在重建之前刪除該索引
語句：
drop index idx_name;
drop index idx_name partition partition_name;
建立索引的代價？
基礎表維護時，系統要同時維護索引，不合理的索引將嚴重影響系統資源，
主要表現在CPU和I/O上。
插入、更新、刪除資料產生大量db file sequential read鎖等待。
1.重建索引
alter index index_name1 rebuild;
2.重建索引並轉移到別的表空間
alter index index_name1 rebuild tablespace other_tablespace; --這個表空間要首先建立
3.修改索引的一些引數設定
alter index index_name rebuild pctfree 30 storage(next 100k);
4.自動為索引分配空間
alter index index_name allocate extent;
5.整合索引碎片
alter index index_name coalesce;
6.刪除一個索引
drop index index_name;
7.開啟對索引的監控,然後分析索引
--首先開啟對索引的監控
alter index index_name monitoring usage;
--然後執行一個與索引有關的查詢
--接著關閉對索引的監控
alter index index_name nomonitoring usage;
--然後查詢v$object_usage檢視
select index_name,start_monitoring,end_monitoring
from v$object_usage
where index_name = '........'
一、重建索引的前提
1、表上頻繁發生update,delete操作；
2、表上發生了alter table ..move操作（move操作導致了rowid變化）。
二、重建索引的標準
1、索引重建是否有必要，一般看索引是否傾斜的嚴重，是否浪費了空間， 那應該如何才可以判斷索引是否傾斜的嚴重，是否浪費了空間， 對索引進行結構分析（如下）：
SQL>Analyze index index_name validate structure;
2、在執行步驟1的session中查詢index_stats表，不要到別的session去查詢。
SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats;
說明：當 查詢出來的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的場合 ， 該索引考慮重建 。
舉例： (t_gl_assistbalance 26 萬多條資訊 )
SQL> select count(*) from t_gl_assistbalance ;
輸出結果：
COUNT(*)
----------
265788
SQL> Analyze index IX_GL_ASSTBAL_1 validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
輸出結果：
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
4 1
三、重建索引的方式
1、drop 原來的索引，然後再建立索引;
舉例：
刪除索引：drop index IX_PM_USERGROUP;
建立索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid);
說明：此方式耗時間，無法在24*7環境中實現，不建議使用。
2 、直接重建：
舉例：
alter index indexname rebuild; 或alter index indexname rebuild online;
說明：此方式比較快，可以在24*7環境中實現，建議使用此方式。
四、alter index rebuild 內部過程和注意點
alter index rebuild 和alter index rebuil online的區別
1、掃描方式不同
1.1、Rebuild以index fast full scan（or table full scan） 方式讀取原索引中的資料來構建一個新的索引，有排序的操作;
1.2、rebuild online 執行表掃描獲取資料，有排序的操作;
說明：Rebuild 方式 (index fast full scan or table full scan 取決於統計資訊的cost)
舉例1
SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |
| 2 | SORT CREATE INDEX | | 999K| 4882K| |
| 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| |
---------------------------------------------------------------------
舉例2
SQL> explain plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | |
| 2 | SORT CREATE INDEX | | 2072K| 9M| |
| 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 |
舉例3 ( 注意和 舉例1 比較 )
Rebuil online 方式 ：
SQL> explain plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |
| 2 | SORT CREATE INDEX | | 999K| 4882K| |
| 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 |
2 、rebuild 會阻塞 dml 操作 ，rebuild online 不會阻塞 dml 操作 ；
3 、rebuild online 時系統會產生一個 SYS_JOURNAL_xxx 的 IOT 型別的系統臨時日誌表 ， 所有 rebuild online 時索引的變化都記錄在這個表中 ， 當新的索引建立完成後 ， 把這個表的記錄維護到新的索引中去 ， 然後 drop 掉舊的索引 ，rebuild online 就完成了。
注意點：
1、 執行rebuild操作時，需要檢查表空間是否足夠；
2、雖然說rebuild online操作允許dml操作，但是還是建議在業務不繁忙時間段進行；
Rebuild操作會產生大量redo log ；
五、重建分割槽表上的分割槽索引
重建分割槽索引方法：
Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
Partition name 可以從user_ind_partitions查詢
Tablepace 引數允許alter index操作更改索引的儲存空間；
六、索引狀態描述
在資料字典中檢視索引狀態，發現有三種：
valid:當前索引有效
N/A :分割槽索引 有效
unusable:索引失效
七、術語
1、高基數：簡單理解就是表中列的不同值多。
2、低基數：建單理解就是表中的列的不同值少。
3、以刪除的葉節點數量：指得是資料行的delete操作從邏輯上刪除的索引節點 的數量，要記住oracle在刪除資料行後，將 “ 死 “ 節點保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除資料行後可以不必重新平衡索引。
4、索引高度：索引高度是指由於資料行的插入操作而產生的索引層數，當表中新增大量資料時，oracle將生成索引的新層次以適應加入的資料行，因此，oracle索引可能有4層，但是這隻會出現在索引數中產生大量插入操作的區域。Oracle索引的三層結構可以支援數百萬的專案，而具備4層或是更多層的需要重建。
5、每次索引訪問的讀取數：是指利用索引讀取一資料行時所需要的邏輯I/O運算元，邏輯讀取不必是物理讀取，因為索引的許多內容已經儲存在資料緩衝區，然而，任何資料大於10的索引都需要重建。
6、什麼時候重建呢？
察看 dba_indexes 中的 blevel 。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大於等於4。則需要重建，
如下 ：Select index_name,blevel from dba_indexes where blevel>=4.
另一個從重建中受益的指標顯然是當該索引中的被刪除項佔總的項數的百分比。如果在20%以上時，也應當重建，如下
SQL>analyze index index_name validate structure
SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’
就能看到是否這個索引被刪除的百分比。
7、什麼樣的重建方式更好？
（1）、建索引的辦法：
1.1、刪除並從頭開始建立索引。
1.2 、 使用 alter index index_name rebuild 命令重建索引。
1.3 、 使用 alter index index_name coalesce 命令重建索引。
（2）、下面討論一下這三種方法的優缺點：
2.1、刪除並從頭開始建索引：方法是最慢的，最耗時的。一般不建議。
2.2、Alter index index_name rebuild
快速重建索引的一種有效的辦法，因為使用現有索引項來重建新索引，如果客戶操作時有其他使用者在對這個表操作，儘量使用帶online引數來最大限度的減少索引重建時將會出現的任何加鎖問題，alter index index_name rebuild online。
但是,由於新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁碟空間可臨時使用，當索引建完後把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引移到新的表空間。
Alter index index_name rebuild tablespace tablespace_name 。
這個命令的執行步驟如下：
首先，逐一讀取現有索引，以獲取索引的關鍵字。
其次，按新的結構填寫臨時資料段。
最後，一旦操作成功，刪除原有索引樹，降臨時資料段重新命名為新的索引。
需要注意的是alter index index_name rebuild 命令中必須使用tablespace字句，以保證重建工作是在現有索引相同的表空間進行。
2.3、alter index index_name coalesce
使用帶有coalesce引數時重建期間不需要額外空間，它只是在重建索引時將處於同一個索引分支內的葉塊拼合起來，這最大限度的減少了與查詢過程中相關的潛在的加鎖問題，但是，coalesce選項不能用來將一個索引轉移到其他表空間。
八、其他
1、truncate 分割槽操作和truncate 普通表的區別？
1.1、Truncate 分割槽操作會導致全域性索引失效; truncate 普通表對索引沒有影響；
1.2、Truncate 分割槽操作不會釋放全域性索引中的空間，而truncate 普通表會釋放索引所佔空間；
2、rename 表名操作對索引沒有影響，因為rename操作只是更改了資料字典，表中資料行的rowid並沒有發生變化
總結：
1、判斷是否需要重建索引：
SQL>analyze index index_name validate structure;
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
（ 或 Select index_name,blevel from dba_indexes where blevel>=4 ）;
說明 ： 當查詢出來的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的場合 ， 該索引考慮重建 ；
2 、重建索引方法 ：
方法一、
Alter index index_name rebuild tablespace tablespace_name;
優點：是快速重建索引的一種有效的辦法，可以用來將一個索引移到新的表空間。
缺點：重建期間需要額外空間。
方法二、
alter index index_name coalesce;
優點：重建期間不需要額外空間。
缺點：coalesce選項不能用來將一個索引轉移到其他表空間。
索引的三大特點：
1.索引數的高度一般較低
2.索引由索引列儲存的值及rowid組成
索引SELECT * FROM T WHERE ID = 1會導致索引回表的產生，若不需要看全部資料可用SELECT ID FROM T WHERE ID= 1或可建多列的複核索引，但是複核索引最好不要超過3列的複核。在更新操作不頻繁的情況下可考慮使用索引組織表
3.索引本身是有序的
減少ORDER BY、DISTINCT排序所浪費的COST
聚合因子：
建立索引的列的順序與索引自動排序的列的順序的不對應度被稱為聚合因子，聚合因子越大索引回表讀越浪費時間(索引回表讀不可避免的情況下)
Oracle執行計劃分類：
1、TABLE ACCESS FULL 全表掃描
2、INDEX FAST FULL SCAN 索引快速掃描 (不考慮排序COUNT(ID), SUM(ID), AVG(ID)，列必須非空或IS NOT NULL)
3、INDEX FULL SCAN 索引全掃 (SELECT ID FROM T WHERE ID = 120)
4、INDEX FULL SCAN(MIN/MAX) 最大值最小值索引全掃 (SELECT MAX(ID) FROM T)
5、TABLE ACCESS BY INDEX ROWID 索引回表讀 (SELECT * FROM T WHERE ID = 1)
6、INDEX RANGE SCAN 索引範圍掃描 (SELECT * FROM T WHERE ID < 100)
7、BITMAP INDEX FAST FULL SCAN 點陣圖索引快速掃描 (不考慮排序COUNT(ID), SUM(ID), AVG(ID)，點陣圖快速索引掃描速度非常快將近是普通索引的百倍.列不必非空)
btree索引優化簡介
T表 欄位：OBJECT_ID, OBJECT_NAME
單列索引：
CREATE INDEX IDX1_OBJECT_ID ON T(OBJECT_ID);
COUNT(*)、SUM、AVG優化：
改為COUNT(OBJECT_ID)查詢，但是必須保證OBJECT_ID列非空。
可用如下方法告知oracle可走IDX1_OBJECT_ID索引：
1、SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_ID IS NOT NULL;
2、設定欄位非空
MAX/MIN優化：
根據第三個特性有序排列，所以MAX/MIN的查詢代價會非常小。
SELECT MAX(OBJECT_ID) FROM T; 不需加上IS NOT NULL; 使用執行計劃：INDEX FULL SCAN(MIN/MAX);
ORDER BY、DISTINCT排序優化：
SELECT * FROM T WHERE OBJECT_ID < 100 ORDER BY OBJECT_ID;
未建立索引的情況下會進行排序產生TEMPSPC;
建立索引的情況下不需要產生排序 會使用IDX1_OBJECT_ID索引
點陣圖索引
建立語法：
CREATE BITMAP INDEX IDX_BITM_T_STATUS ON T(STATUS);
適用在更新非常少的表，建立在重複度較高的列(性別)
儲存結構：
點陣圖索引儲存的是位元位值
函式索引：
CREATE TABLE T AS SELECT * FROM DBA_OBJECT;
CREATE INDEX IDX_OBJECT_ID ON T(OBJECT_ID);
CREATE INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME);
CREATE INDEX IDX_CREATED ON T(CREATED);
SELECT * FROM T WHERE UPPER(OBJECT_NAME) = 'T';
普通的BTREE索引，如果在對列做運算的條件下是無法使用索引查詢的，會使用TABLE ACCESS FULL;
建立語句：
CREATE INDEX IDX_UPPER_OBJ_NAME ON(UPPER(OBJECT_NAME));
SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'T';
函式索引的TYPE是：FUNCTION-BASED NORMAL;
函式索引的cost比全表掃描要小，但是比普通的索引要大的多。
SELECT * FROM T WHERE OBJECT_ID - 10<30;
這時候如果在object_id列建立普通索引時無法使用的。oracle會預設使用全表掃描的方式進行查詢。可有以下兩個思路進行優化：
1、SELECT * FROM T WHERE OBEJCT_ID < 40;
2、在OBJECT_ID - 10上建立函式索引
寫sql時要注意規範，很多語句是等價的。
SELECT * FROM T WHERE SUBSTR(OBJECT_NAME,1，4) = 'CLUS' = SELECT * FROM T WHERE OBJECT_NAME LIKE 'CLUS%';
SELECT * FROM T WHERE TRUNC(CREATED) >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND TRUNC(CREATED) <= TO_DATE('2012-10-03','YYYY-MM-DD')
=
SELECT * FROM T WHERE CREATED >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND CREATED < TO_DATE('2012-10-03','YYYY-MM-DD')+1;
Oracle 索引 詳解
一． 索引介紹
1.1 索引的建立 語法 ：
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>
相關說明
1） UNIQUE | BITMAP ：指定UNIQUE 為唯一值索引， BITMAP 為點陣圖索引， 省略為B-Tree 索引。
2） <column_name> | <expression> ASC | DESC ：可以對多列進行聯合索引，當為expression 時即 “ 基於函式的索引 ”
3） TABLESPACE ：指定存放索引的表空間 (索引和原表不在一個表空間時效率更高 )
4） STORAGE ：可進一步設定表空間的儲存引數
5） LOGGING | NOLOGGING ：是否對索引產生重做日誌( 對大表儘量使用 NOLOGGING 來減少佔用空間並提高效率 )
6） COMPUTE STATISTICS ：建立新索引時收集統計資訊
7） NOCOMPRESS | COMPRESS<nn> ：是否使用“ 鍵壓縮 ”( 使用鍵壓縮可以刪除一個鍵列中出現的重複值 )
8） NOSORT | REVERSE ：NOSORT 表示與表中相同的順序建立索引， REVERSE 表示相反順序儲存索引值
9） PARTITION | NOPARTITION ：可以在 分割槽表 和未分割槽表上對建立的索引進行分割槽
1. 2 索引特點：
第一 ，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二 ，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
第三 ，可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
第四 ，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五 ，通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。
1. 3 索引不足：
第一 ，建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
第二 ，索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。
第三 ，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。
1. 4 應該建索引列的特點：
1） 在經常需要搜尋的列上，可以加快搜尋的速度；
2） 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
3） 在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；
4） 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
5） 在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
6） 在經常使用在WHERE 子句中的列上面建立索引，加快條件的判斷速度。
1. 5 不應該建索引列的特點：
第一 ，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。
第二 ，對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
第三 ，對於那些定義為 blob 資料型別的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。
第四 ，當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。
1.6 限制索引
限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL 中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：
1.6.1 使用不等於操作符（<> 、 != ）
下面的查詢即使在cust_rating 列有一個索引，查詢語句仍然執行一次全表掃描。
select cust_Id,cust_name from customers where cust_rating <> 'aa';
把上面的語句改成如下的查詢語句，這樣，在採用基於規則的優化器而不是基於代價的優化器（更智慧）時，將會使用索引。
select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';
特別注意：通過把不等於操作符改成 OR 條件，就可以使用索引，以避免全表掃描。
1.6. 2 使用 IS NULL 或 IS NOT NULL
使用 IS NULL 或 IS NOT NULL 同樣會限制索引的使用 。因為 NULL 值並沒有被定義。在 SQL 語句中使用 NULL 會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成 NOT NULL 。如果被索引的列在某些行中存在 NULL 值，就不會使用這個索引（除非索引是一個點陣圖索引，關於點陣圖索引在稍後在詳細討論）。
1.6 .3 使用函式
如果不使用基於函式的索引，那麼在SQL 語句的 WHERE 子句中對存在索引的列使用函式時，會使優化器忽略掉這些索引。
下面的查詢不會使用索引（只要它不是基於函式的索引）
select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
把上面的語句改成下面的語句，這樣就可以通過索引進行查詢。
select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);
1.6 .4 比較不匹配的資料型別
也是比較難於發現的效能問題之一。 注意下面查詢的例子，account_number 是一個 VARCHAR2 型別 , 在 account_number 欄位上有索引。
下面的語句將執行全表掃描 ：
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自動把 where 子句變成 to_number(account_number)=990354 ，這樣就限制了索引 的使用 , 改成下面的查詢就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number ='990354';
特別注意： 不匹配的資料型別之間比較會讓Oracle 自動限制索引的使用 , 即便對這個查詢執行 Explain Plan 也不能讓您明白為什麼做了一次 “ 全表掃描 ” 。
1. 7 查詢 索引
查詢DBA_INDEXES 檢視可得到表中所有索引的列表，注意只能通過 USER_INDEXES 的方法來檢索模式 (schema) 的索引。訪問 USER_IND_COLUMNS 檢視可得到一個給定表中被索引的特定列。
1. 8 組合索引
當某個索引包含有多個已索引的列時，稱這個索引為 組合（concatented ）索引 。在 Oracle9i 引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表 emp 有一個組合索引鍵，該索引包含了 empno 、 ename 和 deptno 。在 Oracle9i 之前除非在 where 之句中對第一列（ empno ）指定一個值，否則就不能使用這個索引鍵進行一次範圍掃描。
特別注意：在Oracle9i 之前，只有在使用到索引的前導索引時才可以使用組合索引！
1. 9 ORACLE ROWID
通過每個行的ROWID ，索引 Oracle 提供了訪問單行資料的能力。 ROWID 其實就是直接指向單獨行的線路圖。如果想檢查重複值或是其他對 ROWID 本身的引用，可以在任何表中使用和指定 rowid 列。
1.10 選擇性
使用USER_INDEXES 檢視，該檢視中顯示了一個 distinct_keys 列。比較一下唯一鍵的數量和表中的行數，就可以判斷索引的選擇性。選擇性越高，索引返回的資料就越少。
1.11 群集因子(Clustering Factor)
Clustering Factor位於 USER_INDEXES 檢視中。該列反映了資料相對於已 建 索引的列是否顯得有序。如果Clustering Factor 列的值接近於索引中的樹葉塊 (leaf block) 的數目，表中的資料就越有序。如果它的值接近於表中的行數，則表中的資料就不是很有序。
1.12 二元高度(Binary height)
索引的二元高度對把ROWID 返回給使用者程式時所要求的 I/O 量起到關鍵作用。在對一個索引進行分析後，可以通過查詢 DBA_INDEXES 的 B- level 列檢視它的二元高度 。二元高度主要隨著表的大小以及被索引的列中值的範圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會增加。更新索引列也類似於刪除操作，因為它增加了已刪除鍵的數目。 重建索引可能會降低二元高度 。
1.13 快速全域性掃描
從 Oracle7.3後就可以使用快速全域性掃描 (Fast Full Scan) 這個選項。這個選項允許 Oracle 執行一個全域性索引掃描操作。快速全域性掃描讀取 B- 樹索引上所有樹葉塊。初始化檔案中的 DB_FILE_MULTIBLOCK_READ_COUNT 引數可以控制同時被讀取的塊的數目。
1.14 跳躍式掃描
從Oracle9i 開始，索引跳躍式掃描特性可以允許優化器使用組合索引，即便索引的前導列沒有出現在 WHERE 子句中。索引跳躍式掃描比全索引掃描 要快的多。
下面的 比較他們的區別 ：
SQL> set timing on
SQL> create index TT_index on TT(teamid,areacode);
索引已建立。
已用時間: 00: 02: 03.93
SQL> select count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時間: 00: 00: 08.31
SQL> select /*+ index(tt TT_index )*/ count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時間: 00: 00: 07.37
1.15 索引的型別
B-樹索引
點陣圖索引
HASH索引
索引編排表
反轉鍵索引
基於函式的索引
分割槽索引
本地和全域性索引
二． 索引分類
Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對於一個應用程式的效能來說非常重要。一個錯誤的選擇可能會引發死鎖，並導致資料庫效能急劇下降或程式終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經執行了幾個小時甚至幾天的程式在幾分鐘得以完成，這樣會使您立刻成為一位英雄。 下面 就將簡單的討論每個索引選項。
下面討論的索引型別：
B樹索引 (預設型別 )
點陣圖索引
HASH索引
索引組織表索引
反轉鍵(reverse key) 索引
基於函式的索引
分割槽索引( 本地和全域性索引 )
點陣圖連線索引
2.1 B樹索引 (預設型別 )
B樹索引在 Oracle 中是一個通用索引。在建立索引時它就是預設的索引型別 。 B 樹索引可以是一個列的 ( 簡單 ) 索引，也可以是組合 / 複合 ( 多個列 ) 的索引。 B 樹索引最多可以包括 32 列 。
在 下圖 的例子中，B 樹索引位於僱員表的 last_name 列上。這個索引的二元高度為 3 ；接下來， Oracle 會穿過兩個樹枝塊 (branch block) ，到達包含有 ROWID 的樹葉塊。在每個樹枝塊中，樹枝行包含鏈中下一個塊的 ID 號。
樹葉塊包含 了 索引值 、 ROWID ，以及指向前一個和後一個樹葉塊的 指標 。Oracle 可以從兩個方向遍歷這個二叉樹。 B 樹索引儲存了在索引列上有值的每個資料行的 ROWID 值。 Oracle不會對索引列上包含 NULL 值的行進行索引 。如果索引是多個列的組合索引，而其中列上包含NULL 值，這一行就會處於包含 NULL 值的索引列中，且將被處理為空 ( 視為 NULL) 。
技巧 ： 索引列的值都儲存在索引中。因此，可以建立一個組合 ( 複合 ) 索引，這些索引可以直接滿足查詢，而不用訪問表。這就不用從表中檢索資料，從而減少了 I/O 量。
B-tree 特點 ：
適合與大量的增、刪、改（OLTP ）
不能用包含OR 操作符的查詢；
適合高基數的列（唯一值多）
典型的樹狀結構；
每個結點都是資料塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊資料是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的範圍；
2.2 點陣圖索引
點陣圖索引非常適合於決策支援系統(Decision Support System ， DSS) 和資料倉儲 ，它們不應該用於通過事務處理應用程式訪問的表。它們可以使用較少到中等基數( 不同值的數量 ) 的列訪問非常大的表。儘管點陣圖索引最多可達 30 個列，但通常它們都只用於少量的列。
例如，您的表可能包含一個稱為Sex 的列，它有兩個可能值：男和女。這個基數只為 2 ，如果使用者頻繁地根據 Sex 列的值查詢該表，這就是點陣圖索引的基列。當一個表內包含了多個點陣圖索引時，您可以體會到點陣圖索引的真正威力。如果有多個可用的點陣圖索引， Oracle 就可以合併從每個點陣圖索引得到的結果集，快速刪除不必要的資料。
Bitmap t 特點 ：
適合與決策支援系統；
做UPDATE 代價非常高；
非常適合OR 操作符的查詢；
基數比較少的時候才能建點陣圖索引；
技巧： 對於有較低基數的列需要使用點陣圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女( 基數僅為 2) 。 點陣圖對於低基數( 少量的不同值 ) 列來說非常快 ，這是因為索引的尺寸相對於B 樹索引來說小了很多。因為這些索引是低基數的 B 樹索引，所以非常小，因此您可以經常檢索表中超過半數的行，並且仍使用點陣圖索引。
當大多數條目不會向點陣圖新增新的值時，點陣圖索引在批處理( 單使用者 ) 操作中載入表 ( 插入操作 ) 方面通常要比 B 樹做得好。當多個會話同時向表中插入行時不應該使用點陣圖索引，在大多數事務處理應用程式中都會發生這種情況。
示例
下面來看一個示例表PARTICIPANT ，該表包含了來自個人的調查資料。列 Age_Code 、 Income_Level 、 Education_Level 和 Marital_Status 都包括了各自的點陣圖索引。 下圖 顯示了每個直方圖中的資料平衡情況，以及對訪問每個點陣圖索引的查詢的執行路徑。圖中的執行路徑顯示了有多少個點陣圖索引被合併，可以看出效能得到了顯著的提高。
如 上圖 圖所示，優化器依次使用4 個單獨的點陣圖索引，這些索引的列在 WHERE 子句中被引用。每個點陣圖記錄指標 ( 例如 0 或 1) ，用於指示表中的哪些行包含點陣圖中的已知值。有了這些資訊後， Oracle 就執行 BITMAP AND 操作以查詢將從所有 4 個點陣圖中返回哪些行。該值然後被轉換為 ROWID 值，並且查詢繼續完成剩餘的處理工作。 注意，所有4 個列都有非常低的基數，使用索引可以非常快速地返回匹配的行。
技巧： 在一個查詢中合併多個點陣圖索引後，可以使效能顯著提高。點陣圖索引使用固定長度的資料型別要比可變長度的資料型別好。較大尺寸的塊也會提高對點陣圖索引的儲存和讀取效能。
下面的查詢可顯示索引型別。
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ----------------------
TT_INDEX NORMAL
IX_CUSTADDR_TP NORMAL
B樹索引 作為NORMAL 列出；而 點陣圖索引 的型別值為 BITMAP 。
技巧： 如果要查詢點陣圖索引列表，可以在USER _INDEXES 檢視中查詢 index_type 列。
建議不要在一些聯機事務處理(OLTP) 應用程式中使用點陣圖索引 。B 樹索引的索引值中包含 ROWID ，這樣 Oracle 就可以在行級別上鎖定索引。點陣圖索引儲存為壓縮的索引值，其中包含了一定範圍的 ROWID ，因此 Oracle 必須針對一個給定值鎖定所有範圍內的 ROWID 。這種鎖定型別可能在某些 DML 語句中造成死鎖。 SELECT 語句不會受到這種鎖定問題的影響。
點陣圖索引 的使用 限制 ：
基於規則的優化器不會考慮點陣圖索引。
當執行ALTER TABLE 語句並修改包含有點陣圖索引的列時，會使點陣圖索引失效。
點陣圖索引不包含任何列資料，並且不能用於任何型別的完整性檢查。
點陣圖索引不能被宣告為唯一索引。
點陣圖索引的最大長度為30 。
技巧： 不要在繁重的OLTP 環境中使用點陣圖索引
2.3 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 對於一些包含有序值的靜態資料非常有效。
技巧： HASH索引在有限制條件 ( 需要指定一個確定的值而不是一個值範圍 ) 的情況下非常有用。
2.4 索引組織表
索引組織表會把表的儲存結構改成B 樹結構，以表的主鍵進行排序。這種特殊的表和其他型別的表一樣，可以在表上執行所有的 DML 和 DDL 語句。 由於表的特殊結構，ROWID 並沒有被關聯到表的行上。
對於一些涉及精確匹配和範圍搜尋的語句，索引組織表提供了一種基於鍵的快速資料訪問機制。 基於主鍵值的UPDATE 和 DELETE 語句的效能也同樣得以提高， 這是因為行在物理上有序。由於鍵列的值在表和索引中都沒有重複， 儲存所需要的空間也隨之減少。
如果不會頻繁地根據主鍵列查詢資料，則需要在索引組織表中的其他列上建立二級索引。不會頻繁根據主鍵查詢表的應用程式不會了解到使用索引組織表的全部優點。 對於總是通過對主鍵的精確匹配或範圍掃描進行訪問的表，就需要考慮使用索引組織表。
技巧： 可以在索引組織表上建立二級索引。
2.5 反轉鍵索引
當載入一些有序資料時，索引肯定會碰到與I/O 相關的一些瓶頸。在資料載入期間，某部分索引和磁碟肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把 檔案物理分割在多個磁碟上的磁碟體系結構上 。
為了解決這個問題，Oracle 還提供了一種反轉鍵索引的方法。如果資料以反轉鍵索引儲存，這些資料的值就會與原先儲存的數值相反。這樣，資料 1234 、 1235 和 1236 就被儲存成 4321 、 5321 和 6321 。 結果就是索引會為每次新插入的行更新不同的索引塊。
技巧： 如果您的磁碟容量有限，同時還要執行大量的有序載入，就可以使用反轉鍵索引。
不可以將反轉鍵索引與點陣圖索引或索引組織表結合使用。 因為 不能對點陣圖索引和索引組織表進行反轉鍵處理。
2.6 基於函式的索引
可以在表中建立基於函式的索引。如果沒有基於函式的索引，任何在列上執行了函式的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用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 語句的執行就會花費越多的時間。
注意： 對於優化器所使用的基於函式的索引來說， 必須把初始引數QUERY _REWRITE _ ENABLED 設定為 TRUE 。
示例：
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes
create index ratio_idx1 on sample (ratio(balance, limit));
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!
2.7 分割槽索引
分割槽索引就是簡單地把一個索引分成多個片斷。通過把一個索引分成多個片斷，可以訪問更小的片斷( 也更快 ) ，並且可以把這些片斷分別存放在不同的磁碟驅動器上 ( 避免 I/O 問題 ) 。 B樹和點陣圖索引都可以被分割槽，而 HASH 索引不可以被分割槽 。可以有好幾種分割槽方法： 表被分割槽而索引未被分割槽 ； 表未被分割槽而索引被分割槽 ； 表和索引都被分割槽 。不管採用哪種方法， 都必須使用基於成本的優化器 。分割槽能夠提供更多可以提高效能和可維護性的可能性
有兩種型別的分割槽索引： 本地分割槽索引 和 全域性分割槽索引 。每個型別都有兩個子型別，有字首索引和無字首索引。表各列上的索引可以有各種型別索引的組合。 如果使用了點陣圖索引，就必須是本地索引 。把索引分割槽最主要的原因是可以減少所需讀取的索引的大小，另外把分割槽放在不同的表空間中可以提高分割槽的可用性和可靠性。
在使用分割槽後的表和索引時，Oracle 還支援並行查詢和並行 DML 。這樣就可以同時執行多個程式，從而加快處理這條語句。
2.7. 1.本地分割槽索引 ( 通常使用的索引 )
可以使用與表相同的分割槽鍵和範圍界限來對本地索引分割槽。每個本地索引的分割槽只包含了它所關聯的表分割槽的鍵和ROWID 。本地索引可以是 B 樹或點陣圖索引。如果是 B 樹索引，它可以是唯一或不唯一的索引。
這種型別的索引支援分割槽獨立性，這就意味著對於單獨的分割槽，可以進行增加、擷取、刪除、分割、離線等處理，而不用同時刪除或重建索引。 Oracle自動維護這些本地索引。 本地索引分割槽還可以被單獨重建，而其他分割槽不會受到影響。
2.7.1.1 有字首的索引
有字首的索引包含了來自分割槽鍵的鍵，並把它們作為索引的前導。例如，讓我們再次回顧participant 表。在建立該表後，使用 survey_id 和 survey_date 這兩個列進行範圍分割槽，然後在 survey_id 列上建立一個有字首的本地索引，如 下圖 所示。這個索引的所有分割槽都被等價劃分，就是說索引的分割槽都使用表的相同範圍界限來建立。
技巧： 本地的有字首索引可以讓Oracle 快速剔除一些不必要的分割槽。也就是說沒有包含 WHERE 條件子句中任何值的分割槽將不會被訪問，這樣也提高了語句的效能。
2.7.1.2 無字首的索引
無字首的索引並沒有把分割槽鍵的前導列作為索引的前導列。若使用有同樣分割槽鍵(survey_id 和 survey_date) 的相同分割槽表，建立在 survey_date 列上的索引就是一個本地的無字首索引，如 下圖 所示。可以在表的任一列上建立本地無字首索引，但索引的每個分割槽只包含表的相應分割槽的鍵值。
如果要把無字首的索引設為唯一索引，這個索引就必須包含分割槽鍵的子集。在這個例子中，我們必須把包含survey 和 ( 或 )survey_id 的列進行組合 ( 只要 survey_id 不是索引的第一列，它就是一個有字首的索引 ) 。
技巧： 對於一個唯一的無字首索引，它必須包含分割槽鍵的子集。
2.7. 2. 全域性分割槽索引
全域性分割槽索引在一個索引分割槽中包含來自多個表分割槽的鍵。一個全域性分割槽索引的分割槽鍵是分割槽表中不同的或指定一個範圍的值。在建立全域性分割槽索引時，必須定義分割槽鍵的範圍和值。 全域性索引只能是B 樹索引 。 Oracle在預設情況下不會維護全域性分割槽索引。如果一個分割槽被擷取、增加、分割、刪除等，就必須重建全域性分割槽索引 ，除非在修改表時指定ALTER TABLE 命令的 UPDATE GLOBAL INDEXES 子句。
2.7.2.1 有字首的索引
通常，全域性有字首索引在底層表中沒有經過對等分割槽。沒有什麼因素能限制索引的對等分割槽，但Oracle 在生成查詢計劃或執行分割槽維護操作時，並不會充分利用對等分割槽。如果索引被對等分割槽，就必須把它建立為一個本地索引，這樣 Oracle 可以維護這個索引，並使用它來刪除不必要的分割槽，如 下圖 所示。在該圖的3 個索引分割槽中，每個分割槽都包含指向多個表分割槽中行的索引條目。
分割槽的、全域性有字首索引
技巧 ： 如果一個全域性索引將被對等分割槽，就必須把它建立為一個本地索引，這樣Oracle 可以維護這個索引，並使用它來刪除不必要的分割槽。
2.7.2.2 無字首的索引
Oracle不支援無字首的全域性索引。
2.8 點陣圖連線索引
點陣圖連線索引是基於兩個表的連線的點陣圖索引，在資料倉儲環境中使用這種索引改進連線維度表和事實表的查詢的效能。建立點陣圖連線索引時，標準方法是連線索引中常用的維度表和事實表。當使用者在一次查詢中結合查詢事實表和維度表時，就不需要執行連線，因為在點陣圖連線索引中已經有可用的連線結果。通過壓縮點陣圖連線索引中的ROWID 進一步改進效能，並且減少訪問資料所需的 I/O 數量。
建立點陣圖連線索引時，指定涉及的兩個表。相應的語法應該遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;
點陣圖連線的語法比較特別，其中包含FROM 子句和 WHERE 子句，並且引用兩個單獨的表。索引列通常是維度表中的描述列 —— 就是說，如果維度是 CUSTOMER ，並且它的主鍵是 CUSTOMER_ID ，則通常索引 Customer_Name 這樣的列。如果事實表名為 SALES ，可以使用如下的命令建立索引：
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;
如果使用者接下來使用指定Customer_Name 列值的 WHERE 子句查詢 SALES 和 CUSTOMER 表，優化器就可以使用點陣圖連線索引快速返回匹配連線條件和 Customer_Name 條件的行。
點陣圖連線索引的使用一般會受到限制 ：
1） 只可以索引維度表中的列。
2） 用於連線的列必須是維度表中的主鍵或唯一約束；如果是複合主鍵，則必須使用連線中的每一列。
3） 不可以對索引組織表建立點陣圖連線索引，並且適用於常規點陣圖索引的限制也適用於點陣圖連線索引。
Oracle 索引的分類,分析與比較
邏輯上：
Single column 單行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based函式索引
Domain 域索引
物理上：
Partitioned 分割槽索引
NonPartitioned 非分割槽索引
B-tree：
Normal 正常型B樹
Rever Key 反轉型B樹
Bitmap 點陣圖索引
索引結構：
B-tree：
適合與大量的增、刪、改（OLTP）；
不能用包含OR操作符的查詢；
適合高基數的列（唯一值多）
典型的樹狀結構；
每個結點都是資料塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊資料是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的範圍；
Bitmap:
適合與決策支援系統；
做UPDATE代價非常高；
非常適合OR操作符的查詢；
基數比較少的時候才能建點陣圖索引；
樹型結構：
索引頭
開始ROWID，結束ROWID（先列出索引的最大範圍）
BITMAP
每一個BIT對應著一個ROWID，它的值是1還是0，如果是1，表示著BIT對應的ROWID有值；[@more@]
Oracle索引分析與比較
首先給出各種索引的簡要解釋：
b*tree index：幾乎所有的關係型資料庫中都有b*tree型別索引，也是被最多使用的。其樹結構與二叉樹比較類似，根據rid快速定位所訪問的行。
反向索引：反轉了b*tree索引碼中的位元組，是索引條目分配更均勻，多用於並行伺服器環境下，用於減少索引葉的競爭。
降序索引：8i中新出現的索引型別，針對逆向排序的查詢。
點陣圖索引：使用點陣圖來管理與資料行的對應關係，多用於OLAP系統。
函式索引：這種索引中儲存了資料列基於function返回的值，在select * from table where function(column)=value這種型別的語句中起作用。
2 各種索引的結構分析
2.1 B*Tree索引B*Tree索引是最常見的索引結構，預設建立的索引就是這種型別的索引。B*Tree索引在檢索高基數資料列（高基數資料列是指該列有很多不同的值）時提供了最好的效能。當取出的行數佔總行數比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當檢查的範圍超過表的10%時就不能提高取回資料的效能。B-Tree索引是基於二叉樹的，由分支塊（branch block）和葉塊（leaf block）組成。在樹結構中，位於最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應的rowid。在葉節點的上面是分支塊，用來導航結構，包含了索引列（關鍵字）範圍和另一索引塊的地址。
假設我們要找索引中值為80的行，從索引樹的最上層入口開始，定位到大於等於50，然後往左找，找到第2個分支塊，定位為75－100，最後再定位到葉塊上，找到80所對應的rowid，然後根據rowid去讀取資料塊獲取資料。如果查詢條件是範圍選擇的，比如where column >20 and column <80，那麼會先定位到第一個包含20的葉塊，然後橫向查詢其他的葉塊，直到找到包含80的塊為止，不用每次都從入口進去再重新定位。
2.2 反向索引
反向索引是B*Tree索引的一個分支，它的設計是為了運用在某些特定的環境下的。Oracle推出它的主要目的就是為了降低在並行伺服器（Oracle Parallel Server）環境下索引葉塊的爭用。當B*Tree索引中有一列是由遞增的序列號產生的話，那麼這些索引資訊基本上分佈在同一個葉塊，當使用者修改或訪問相似的列時，索引塊很容易產生爭用。反向索引中的索引碼將會被分佈到各個索引塊中，減少了爭用。反向索引反轉了索引碼中每列的位元組，通過dump（）函式我們可以清楚得看見它做了什麼。舉個例子：1，2，3三個連續的數，用dump()函式看它們在Oracle內部的表示方法。
SQL> select 'number',dump(1,16) from dual
2 union all select 'number',dump(2,16) from dual
3 union all select 'number',dump(3,16) from dual;
'NUMBE DUMP(1,16)
------ -----------------
number Typ=2 Len=2: c1,2 （1）
number Typ=2 Len=2: c1,3 （2）
number Typ=2 Len=2: c1,4 （3）
再對比一下反向以後的情況：
SQL> select 'number',dump(reverse(1),16) from dual
2 union all select 'number',dump(reverse(2),16) from dual
3 union all select 'number',dump(reverse(3),16) from dual;
'NUMBE DUMP(REVERSE(1),1
------ -----------------
number Typ=2 Len=2: 2,c1 （1）
number Typ=2 Len=2: 3,c1 （2）
number Typ=2 Len=2: 4,c1 （3）
我們發現索引碼的結構整個顛倒過來了，這樣1，2，3個索引碼基本上不會出現在同一個葉塊裡，所以減少了爭用。不過反向索引又一個缺點就是不能在所有使用常規索引的地方使用。在範圍搜尋中其不能被使用，例如，where column>value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜尋相鄰葉塊完成區域掃描。
2.3 降序索引
降序索引是8i裡面新出現的一種索引，是B*Tree的另一個衍生物,它的變化就是列在索引中的儲存方式從升序變成了降序，在某些場合下降序索引將會起作用。舉個例子，我們來查詢一張表並進行排序：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
這裡優化器首先選擇了一個索引範圍掃描,然後還有一個排序的步驟。如果使用了降序索引，排序的過程會被取消。
SQL> create index test.ind_desc on test.testrev(a desc，b asc);
索引已建立。
SQL> analyze index test.ind_desc compute statistics;
索引已分析
再來看下執行路徑：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan（SQL執行計劃，稍後會講解如何使用）。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
我們看到排序過程消失了，這是因為建立降序索引時Oracle已經把資料都按降序排好了。另外一個需要注意的地方是要設定init.ora裡面的compatible引數為8.1.0或以上，否則建立時desc關鍵字將被忽略。
2.4 點陣圖索引
點陣圖索引主要用於決策支援系統或靜態資料，不支援行級鎖定。點陣圖索引最好用於低cardinality列（即列的唯一值除以行數為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那麼3/3000000約等於0，這種情況下最適合用點陣圖索引。
點陣圖索引可以是簡單的（單列）也可以是連線的（多列），但在實踐中絕大多數是簡單的。在這些列上多點陣圖索引可以與AND或OR操作符結合使用。點陣圖索引使用點陣圖作為鍵值，對於表中的每一資料行點陣圖包含了TRUE（1）、FALSE（0）、或NULL值。點陣圖索引的點陣圖存放在B-Tree結構的頁節點中。B-Tree結構使查詢點陣圖非常方便和快速。另外，點陣圖以一種壓縮格式存放，因此佔用的磁碟空間比B-Tree索引要小得多。
如果搜尋where gender=’Male’,要統計性別是”Male”的列行數的話，Oracle很快就能從點陣圖中找到共3行即第1，9，10行是符合條件的；如果要搜尋where gender=’Male’ or gender=’Female’的列的行數的話，也很容易從點陣圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜尋表的值的話，那麼Oracle會用內部的轉換函式將點陣圖中的相關資訊轉換成rowid來訪問資料塊。
2.5 函式索引
基於函式的索引也是8i以來的新產物，它有索引計算列的能力，它易於使用並且提供計算好的值，在不修改應用程式的邏輯上提高了查詢效能。使用基於函式的索引有幾個先決條件：
（1）必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）許可權。
（2）必須使用基於成本的優化器，基於規則的優化器將被忽略。
（3）必須設定以下兩個系統引數：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通過alter system set,alter session set在系統級或執行緒級設定，也可以通過在init.ora新增實現。這裡舉一個基於函式的索引的例子：
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已建立。
SQL> insert into testindex values('a',2);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(優化器選擇了全表掃描)
--------------------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(使用了ind_fun索引)
3 各種索引的建立方法
（1）*Tree索引。
Create index indexname on tablename(columnname[columnname...])
（2）反向索引。
Create index indexname on tablename(columnname[columnname...]) reverse
（3）降序索引。
Create index indexname on tablename(columnname DESC[columnname...])
（4）點陣圖索引。
Create BITMAP index indexname on tablename(columnname[columnname...])
（5）函式索引。
Create index indexname on tablename(functionname(columnname))
注意：建立索引後分析要索引才能起作用。
analyze index indexname compute statistics;
4 各種索引使用場合及建議（1）B*Tree索引。
常規索引，多用於oltp系統，快速定位行，應建立於高cardinality列（即列的唯一值除以行數為一個很大的值，存在很少的相同值）。
（2）反向索引。
B*Tree的衍生產物，應用於特殊場合，在ops環境加序列增加的列上建立，不適合做區域掃描。
（3）降序索引。
B*Tree的衍生產物，應用於有降序排列的搜尋語句中，索引中儲存了降序排列的索引碼，提供了快速的降序搜尋。
（4）點陣圖索引。
點陣圖方式管理的索引，適用於OLAP（線上分析）和DSS（決策處理）系統，應建立於低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。
（5）函式索引。
B*Tree的衍生產物，應用於查詢語句條件列上包含函式的情況，索引中儲存了經過函式計算的索引碼值。可以在不修改應用程式的基礎上能提高查詢效率。
5 附表（索引什麼時候不工作）
首先要宣告兩個知識點：
（1）RBO&CBO。
Oracle有兩種執行優化器，一種是RBO(Rule Based Optimizer)基於規則的優化器，這種優化器是基於sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基於規則的優化器，這種優化器是Oracle根據統計分析資訊來選擇執行路徑，如果表和索引沒有進行分析，Oracle將會使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.
（2）AUTOTRACE。
要看索引是否被使用我們要藉助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎麼執行sql的，這是一個非常好的輔助工具，在sql調優裡廣泛被運用。我們來看一下怎麼運用AUTOTRACE：
① 由於AUTOTRACE自動為使用者指定了Execution Plan,因此該使用者使用AUTOTRACE前必須已經建立了PLAN_TABLE。如果沒有的話，請執行utlxplan.sql指令碼（它在$ORACLE_HOME/rdbms/admin目錄中）。
② AUTOTRACE可以通過執行plustrce.sql指令碼(它在$ORACLE_HOME/sqlplus/admin目錄中)來設定，用sys使用者登陸然後執行plustrce.sql後會建立一個PLUSTRACE角色，然後給相關使用者授予PLUSTRACE角色，然後這些使用者就可以使用AUTOTRACE功能了。
③ AUTOTRACE的預設使用方法是set autotrace on，但是這方法不總是適合各種場合，特別當返回行數很多的時候。Set autotrace traceonly提供了只檢視統計資訊而不查詢資料的功能。
SQL> set autotrace on
SQL> select * from test;
A
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
SQL> set autotrace traceonly
SQL> select * from test.test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
Hints是Oracle提供的一個輔助用法，按字面理解就是‘提示’的意思，確實它起得作用也是提示優化器按它所提供的關鍵字來選擇執行路徑，特別適用於sql調整的時候。使用方法如下：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
具體可參考Oracle SQL Reference。有了前面這些知識點，接下來讓我們來看一下什麼時候索引是不起作用的。以下列出幾種情況。
索引常見操作 改變索引：
SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);
索引建立後，感覺不合理，也可以對其引數進行修改。詳情檢視相關文件
調整索引的空間：
新增加空間
SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');
釋放空間
SQL> alter index oraers_id_idx deallocate unused;
索引在使用的過程中可能會出現空間不足或空間浪費的情況，這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關於空間的新增oracle可以自動幫助，如果瞭解資料庫的情況下手動增加可以提高效能。
重新建立索引：
所引是由oracle自動完成，當我們對資料庫頻繁的操作時，索引也會跟著進行修改，當我們在資料庫中刪除一條記錄時，對應的索引中並沒有把相應的索引只是做一個刪除標記，但它依然佔據著空間。除非一個塊中所有的標記全被刪除的時，整個塊的空間才會被釋放。這樣時間久了，索引的效能就會下降。這個時候可以重新建立一個乾淨的索引來提高效率。
SQL> alter index orders_region_id_idx rebuild tablespace index02;
通過上面的命令就可以重現建立一個索引，oracle重建立索引的過程：
1、鎖表，鎖表之後其他人就不能對錶做任何操作。
2、建立新的（乾淨的）臨時索引。
3、把老的索引刪除掉
4、把新的索引重新命名為老索引的名字
5、對錶進行解鎖。
移動所引：
其實，我們移動索引到其它表空間也同樣使用上面的命令，在指定表空間時指定不同的表空間。新的索引建立在別位置，把老的幹掉，就相當於移動了。
SQL> alter index orders_region_id_idx rebuild tablespace index03;
線上重新建立索引：
上面介紹，在建立索引的時候，表是被鎖定，不能被使用。對於一個大表，重新建立索引所需要的時間較長，為了滿足使用者對錶操作的需求，就產生的這種線上重新建立索引。
SQL> alter index orders_id_idx rebuild online;
建立過程：
1、鎖住表
2、建立立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中資料；on-gong DML也就是使用者所做的一些增刪改的操作。
3、對錶進行解鎖
4、從老的索引建立一個新的索引。
5、IOT表裡存放的是on-going DML資訊，IOT表的內容與新建立的索引合併。
6、鎖住表
7、再次將IOT表的內容更新到新索引中，把老的索引幹掉。
8、把新的索引重新命名為老索引的名字
9、對錶進行解鎖
整合索引碎片：
如上圖，在很多索引中有剩餘的空間，可以通過一個命令把剩餘空間整合到一起。
SQL> alter index orders_id_idx coalesce;
刪除索引：
SQL> drop index hr.departments_name_idx;
分析索引 檢查所引的有效果，前面介紹，索引用的時間久了會產生大量的碎片、垃圾資訊與浪費的剩餘空間了。可以通過重新建立索引來提高所引的效能。
可以通過一條命令來完成分析索引，分析的結果會存放在在index_stats表中。
檢視存放分析資料的表：
SQL> select count(*) from index_stats;
COUNT(*)
----------
0
執行分析索引命令：
SQL> analyze index my_bit_idx validate structure;
Index analyzed.
再次檢視 index_stats 已經有了一條資料
SQL> select count(*) from index_stats;
COUNT(*)
----------
1
把資料查詢出來：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
2 MY_BIT_IDX 1000 3 100
分析資料分析：
（HEIGHT）這個所引高度是2 ，（NAME）索引名為MY_BIT_IDX ，（LF_ROWS）所引表有1000行資料，（LF_BLKS）佔用3個塊，（DEL_LF_ROWS）刪除100條記錄。
這裡也驗證了前面所說的一個問題，刪除的100條資料只是標記為刪除，因為總的資料條數依然為1000條，佔用3個塊，那麼每個塊大於333條記錄，只有刪除的資料大於333條記錄，這時一個塊被清空，總的資料條數才會減少。
索引
索引是關聯式資料庫中用於存放每一條記錄的一種物件，主要目的是加快資料的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與資料庫結構一道考慮。應用系統的效能直接與索引的合理直接有關。下面給出建立索引的方法和要點。
§3.5.1 建立索引
1. CREATE INDEX命令語法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中：
schema ORACLE模式，預設即為當前帳戶
index 索引名
table 建立索引的基表名
column 基表中的列名，一個索引最多有16列，long列、long raw
列不能建索引列
DESC、ASC 預設為ASC即升序排序
CLUSTER 指定一個聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事務入口數
Tablespace 表空間名
STORAGE 儲存引數，同create table 中的storage.
PCTFREE 索引資料塊空閒空間的百分比(不能指定pctused)
NOSORT 不（能）排序（儲存時就已按升序，所以指出不再排序）
2.建立索引的目的：
建立索引的目的是：
l 提高對錶的查詢速度；
l 對錶有關列的取值進行檢查。
但是，對錶進行insert,update,delete處理時，由於要表的存放位置記錄到索引項中而會降低一些速度。
注意：一個基表不能建太多的索引；
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高30%左右。
Create index ename_in on emp (ename,sal);
例1：商場的商品庫表結構如下，我們為該表的商品程式碼建立一唯一索引，使得在前臺POS收款時提高查詢速度。
Create table good(good_id number(8) not null,/* 商品條碼 */
Good_desc varchar2(40), /* 商品描述 */
Unit_cost number(10,2) /* 單價 */
Good_unit varchar2(6), /* 單位 */
Unit_pric number(10,2) /* 零售價 */
)；
注：提高查詢速度的方法還有在表上建立主鍵，主鍵與唯一索引的差別
在於唯一索引可以空，主鍵為非空，比如：
Create table good(good_id number(8) primary key,
Good_desc Varchar2(40),
Unit_cost number(10,2),
Good_unit char(6),
Unit_pric number(10,2)
);
§3.5.2 修改索引
對於較早的Oracle版本，修改索引的主要任務是修改已存在索引的儲存引數適應增長的需要或者重新建立索引。而Oracle8I及以後的版本，可以對無用的空間進行合併。這些的工作主要是由管理員來完成。
簡要語法結構如下,更詳細的語法圖見電子文件《Oracle8i Reference 》 中的 Alter index.
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]
其中：
REBUILD 是 根據原來的索引結構重新建立索引，實際是刪除原來的索引後再重新建立。
提示：DBA經常用 REBUILD 來重建索引可以減少硬碟碎片和提高應用系統的效能。
例：
alter index pk_detno rebuild storage(initial 1m next 512k);
ALTER INDEX emp_ix REBUILD REVERSE;
Oracle8i 的新功能可以對索引的無用空間進行合併，它由下面命令完成：
ALTER INDEX . . . COALESCE;
例如：
ALTER INDEX ename_idx COALESCE;
§3.5.3 刪除索引
當不需要時可以將索引刪除以釋放出硬碟空間。命令如下：
DROP INDEX [schema.]indexname
例如：
sql> drop index pk_dept;
注：當表結構被刪除時，有其相關的所有索引也隨之被刪除。
§3.6 新索引型別
Oracle8i為了效能優化而提供新的建立新型別的索引。這些新索引在下面介紹：
§3.6.1 基於函式的索引
基於函式的索引就是儲存預先計算好的函式或表示式值的索引。這些表示式可以是算術運算表示式、SQL或PL/SQL函式、C呼叫等。值得注意的是，一般使用者要建立函式索引，必須具有GLOBAL QUERY REWRITE和CREATE ANY INDEX許可權。否則不能建立函式索引，看下面例子：
例1：為EMP表的ename 列建立大寫轉換函式的索引idx ：
CREATE INDEX idx ON emp ( UPPER(ename));
這樣就可以在查詢語句來使用：
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;
例2：為emp 的工資和獎金之和建立索引：
1) 檢視emp 的表結構：
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2)沒有授權就建立函式索引的提示：
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
create index sal_comm on emp ( (sal+comm)*12, sal,comm)
*
ERROR at line 1:
ORA-01031: insufficient privileges
3) 連線到DBA帳戶並授權：
SQL> connect sys/sys@ora816
Connected.
SQL> grant GLOBAL QUERY REWRITE to scott;
Grant succeeded.
SQL> grant CREATE ANY INDEX to scott;
Grant succeeded.
4）在連線到scott帳戶，建立基於函式的索引：
SQL> connect scott/tiger@ora816
Connected.
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
Index created.
1)在查詢中使用函式索引：
SQL> select ename,sal,comm from emp where (sal+comm)*12 >5000;
ENAME SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
趙元傑 1234.5 54321
§3.6.2 反向鍵索引
反向鍵索引通過反向鍵保持索引的所有葉子鍵上的插入分佈。有時，可用反向鍵索引來避免不平衡的索引。對於反向鍵索引可以進行下面操作：
l 通過在ALTER INDEX命令後加REBUILD NOREVERSE或REBUILD REVERSE子句來使索引邊為反向鍵索引或普通索引；
l 採用範圍掃描的查詢不能使用反向鍵索引；
l 點陣圖索引不能反向；
l 索引編排表不能反向。
例1：建立一個反向鍵索引：
CREATE INDEX i ON t (a,b,c) REVERSE;
例2：使一個索引變為反向鍵索引：
ALTER INDEX i REBUILD NOREVERSE;
§3.6.3 索引組織表
與普通的索引不一樣，索引組織表（Index_Organized Table）是根據表來儲存資料，即將索引和表儲存在一起。這樣的索引結構表（Index_organized table—IOT）的特點是：對錶資料的改變，如插入一新行、刪除某行都引起索引的更新。
索引組織表就象帶一個或多個列所有的普通表一樣，但索引組織表在B-樹索引結構的葉節點上儲存行資料。通過在索引結構中儲存資料，索引組織表減少了總的儲存量，此外，索引組織表也改善訪問效能。
由於表中的行與B_樹索引存放在一起，每個行都沒有ROWID，而是用主鍵來標識。但是Oracle會“猜”這些行的位置併為每個行分配邏輯的ROWID。此外，你可以為這樣的表建立第二個索引。
建立索引結構表也是用CREATE TABLE 命令加ORGANIZATION INDEX關鍵字來實現。但是，這樣的表在建立完後，你還必須為該表建立一個主鍵。
例子：
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引組織表有些限制：
l 不能使用唯一約束；
l 必須具有一個主鍵；
l 不能建立簇；
l 不能包含LONG型別列；
l 不支援分佈和複製。
提示：如果建立了索引組織表，則會在DBA_TABLES中的IOT_TYPE和IOT_NAME列上記錄有索引組織表的資訊。
例1．修改索引結構表 docindex 的索引段的INITRANS引數：
ALTER TABLE docindex INITRANS 4;
例2．下面語句加一個的溢位資料段到索引組織表 docindex中：
ALTER TABLE docindex ADD OVERFLOW;
例3．下面語句為索引組織表 docindex的溢位資料段修改INITRANS引數：
ALTER TABLE docindex OVERFLOW INITRANS 4;
============================================================================================================
適當的使用索引可以提高資料檢索速度，可以給經常需要進行查詢的欄位建立索引
oracle的索引分為5種:唯一索引，組合索引，反向鍵索引，點陣圖索引，基於函式的索引
建立索引的標準語法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空間名;
建立反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空間名;
檢視文章
oracle 檢視索引類別以及檢視索引欄位被引用的欄位方法2008年01月04日 星期五 13:20檢視索引個數和類別
select * from user_indexes where table='表名' ;
檢視索引被索引的欄位
SQL>select * from user_ind_columns where index_name=upper('&index_name');
PS:
檢視某表的約束條件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
檢視檢視的名稱
SQL>select view_name from user_views;
Oracle索引index建立刪除
在查中文字典的時候，我們之所以能夠很快的查到所要查詢的字是因為字典建立了索引。同樣，為了高效的獲取資料，在資料量比較大的時候，我們需要給表中的某一列建立索引。
建立索引的作用是明顯的，主要有：
1、快速存取資料
2、改善資料庫效能，保證列值的唯一性
3、在使用order
按照儲存方法可以將索引分為B*樹索引和點陣圖索引。
B*樹索引：顧名思義其儲存型別就是一棵樹，有分支和葉，分支相當於書的大目錄，葉則相當於具體的書頁。Oracle用B*樹機制儲存索引條目，可以保證用最短路徑訪問鍵值。預設情況下大多使用B*樹索引。
點陣圖索引：點陣圖索引儲存主要用於節省空間，減少Oracle對資料塊的訪問，它採用點陣圖偏移方式來與表的行id號對應，採用點陣圖索引一般是重複值太多的表欄位。點陣圖索引在實際密集型OLTP(資料事務處理)中用得比較少，因為OLTP會對錶進行大量的刪除、修改、新增操作，Oracle每次進行這些操作的時候都會對要操作的資料塊加鎖，以防止多人操作容易產生的資料塊鎖等待甚至死鎖現象。而在OLAP(資料分析處理)中應用點陣圖有優勢，因為OLAP中大部分是對資料庫的查詢操作，而且一般採用資料倉儲技術，所以大量資料採用點陣圖索引節省空間比較明顯。
建立索引：
語法格式：
Sql程式碼
create [unique | bitmap] index [schema.]indexName
on [schema.]tableName(columnName[ASC | DESC],...n)
[tablespace tablespaceName] [nosort] [reverse]
其中，unique指定索引所基於的列值必須唯一。預設的索引是非唯一索引。Oracle建議不要在表上顯示的定義unique索引。BITMAP指定建立點陣圖索引。
刪除索引：
Sql程式碼
drop index indexName;
Oracle中的索引詳解
本頁包含5張圖片，預設未載入，顯示所有圖片
crazy
一、 ROWID的概念
儲存了row在資料檔案中的具體位置：64位 編碼的資料，A-Z, a-z, 0-9, +, 和 /，
row在資料塊中的儲存方式
SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;
比 如：OOOOOOFFFBBBBBBRRR
OOOOOO：data object number, 對應dba_objects.data_object_id
FFF：file#, 對應v$datafile.file#
BBBBBB：block#
RRR：row#
Dbms_rowid包
SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;
具 體到特定的物理檔案
二、 索引的概念
1、 類似書的目錄結構
2、 Oracle 的“索引”物件，與表關聯的可選物件，提高SQL查詢語句的速度
3、 索引直接指向包含所查詢值的行的位置，減少磁碟I/O
4、 與所索引的表是相互獨立的物理結構
5、 Oracle 自動使用並維護索引，插入、刪除、更新表後，自動更新索引
6、 語法：CREATE INDEX index ON table (column[, column]...);
7、 B-tree結構(非bitmap)：
[一]瞭解索引的工作原理：
表：emp
點選載入圖片
目標：查詢Frank的工資salary
建立索 引：create index emp_name_idx on emp(name);
點選載入圖片
點選載入圖片
[試驗]測試索引的作用：
1. 執行/rdbms/admin/utlxplan 指令碼
2. 建立測試表
create table t as select * from dba_objects;
insert into t select * from t;
create table indextable
as select rownum id,owner,object_name,subobject_name,
object_id,data_object_id,object_type,created
from t;
3. set autotrace trace explain
4. set timing on
5. 分析表，可以得到cost
6. 查詢 object_name=’DBA_INDEXES’
7. 在object_name列上建立索引
8. 再查詢
[思考]索引的代價：
插入，更新
三、 唯一索引
1、 何時建立：當某列任意兩行的值都不相同
2、 當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時，唯一索引將被自動建立
3、 語法：CREATE UNIQUE INDEX index ON table (column);
4、 演示
四、 組合索引
1、 何時建立：當兩個或多個列經常一起出現在where條件中時，則在這些列上同時建立組合索引
2、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的最前面
3、 演示(組合列，單獨列)
五、 點陣圖索引
1、 何時建立：
列中有非常多的重複的值時候。例如某列儲存了 “性別”資訊。
Where 條件中包含了很多OR操作符。
較少的update操作，因為要相應的跟新所有的bitmap
2、 結構：點陣圖索引使用點陣圖作為鍵值，對於表中的每一資料行點陣圖包含了TRUE(1)、FALSE(0)、或NULL值。
3、 優點：點陣圖以一種壓縮格式存放，因此佔用的磁碟空間比標準索引要小得多
4、 語法：CREATE BITMAP INDEX index ON table (column[, column]...);
5、 掩飾：
create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');
分析，查詢，建立索引，查詢
六、 基於函式的索引
1、 何時建立：在WHERE條件語句中包含函式或者表示式時
2、 函式包括：算數表示式、PL/SQL函式、程式包函式、SQL函式、使用者自定義函式。
3、 語法：CREATE INDEX index ON table (FUNCTION(column));
4、 演示
必須要分析表，並且 query_rewrite_enabled=TRUE
或者使用提示/*+ INDEX(ic_index)*/
七、 反向鍵索引
目的：比如索引值是一個自動增長的列：
點選載入圖片
多個使用者對集中在少數塊上的索引行進行修改，容易引起資源的爭用，比如對資料塊的等待。此時建立反向索 引。
效能問題：
語法：
重建為標準索引：反之不行
八、 鍵壓縮索引
比如表landscp的資料如下：
site feature job
Britten Park, Rose Bed 1, Prune
Britten Park, Rose Bed 1, Mulch
Britten Park, Rose Bed 1,Spray
Britten Park, Shrub Bed 1, Mulch
Britten Park, Shrub Bed 1, Weed
Britten Park, Shrub Bed 1, Hoe
……
查詢時，以上3列均在where條件中同時出現，所以建立基於以上3列的組合索引。但是發現重複值很多，所以考慮壓縮特性。
Create index zip_idx
on landscp(site, feature, job)
compress 2;
將索引項分成字首(prefix)和字尾(postfix)兩部分。前兩項被放置到字首部分。
Prefix 0: Britten Park, Rose Bed 1
Prefix 1: Britten Park, Shrub Bed 1
實際所以的結構為：
0 Prune
0 Mulch
0 Spray
1 Mulch
1 Weed
1 Hoe
特點：組合索引的字首部分具 有非選擇性時，考慮使用壓縮。減少I/O,增加效能。
九、 索引組織表(IOT)
將表中的資料按照索 引的結構儲存在索引中，提高查詢速度。
犧牲插入更新的效能，換取查詢 效能。通常用於資料倉儲，提供大量的查詢，極少的插入修改工作。
必須指定主鍵。插入資料時，會根據主鍵列進行B樹索引排序，寫入磁碟。
十、 分割槽索引
簇:
A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
oracle中最常用的索引就兩種：B樹索引和點陣圖索引，這裡就來簡單說下這兩種索引的使用。
B-樹索引在Oracle中是一個通用的索引，在建立索引時它就是預設的索引型別。最多可以包括32列。
點陣圖索引Oracle為每個唯一鍵建立一個點陣圖，然後把與鍵值所關聯的ROWID儲存為點陣圖。最多可以包括30列。
一般情況下，大多數使用者都只建立TYPE為NORMAL的B-樹索引，所以對於較低基數的列我們都是不建立索引的，因為B-樹索引對查詢速度提升不一定會有改善，甚至會增加Insert、Update、Delete命令所消耗的時間。
點陣圖索引在載入表（插入操作）時通常要比B-樹索引做得好。通常，點陣圖索引要比一個低基數（很少不同值）上的B-樹索引要快3~4倍，但如果新增的值佔插入行的70%以上時，B-樹索引通常會更快一些。當每條記錄都增加一個新值時，B-樹索引要比點陣圖索引快3倍。
建議不要在一些聯機事務處理（OLTP）應用程式中使用點陣圖索引。B-樹索引的索引值中包含ROWID，這樣Oracle就可以在行級別上鎖定索引。點陣圖索引被儲存為壓縮的索引值，其中包含了一個範圍內的ROWID，因此ORACLE必須針對一個給定值鎖定所有範圍內的ROWID。這種鎖定可能自阿某些DML語句中造成死鎖。SELECT語句不會受到這種鎖定問題的影響。
點陣圖索引有很多限制：
1、 基於規則的優化器不會考慮點陣圖索引
2、 當執行ATLER TABLE語句，並修改包含有點陣圖索引的列時，會使點陣圖索引實效
3、 點陣圖索引在索引塊中儲存了索引鍵的值；然而，他們並不能使用者任何型別的完整性檢查
4、 點陣圖索引不能被申明為唯一索引
以上是援引的一些簡單概念，下面是我實際工作中總結出來的：
我要做一個查詢，涉及兩個表t_sym_dict,t_sym_operlog，表結構分別如下：
X
其中t_sym_operlog的索引如下：
上面基數比較小的三列建立了點陣圖索引
t_sym_dict的索引如下：
查詢語句如下：
select (select c.dict_name
from t_sym_dict c
where c.dict_typeid = 'SYM_CITYINFO'
and c.dict_id = t.memo) 分公司,
t.staff_id 工號
from t_sym_operlog t
where t.operlog_subtype = '103'
and t.obj_type = 'CUSTLINKINFO'
and t.memo = '200'
-- and t.extsys_code = ''
-- and t.staff_id = ''
and t.oper_date >= to_date('20110501000000', 'yyyymmddhh24miss')
and t.oper_date <= to_date('20110530000000', 'yyyymmddhh24miss')
-- order byt.memo, t.oper_date
然後就出現瞭如下奇怪的現象（索引的建立沒有問題）
1、索引使用正常
2、下面的看不到oper_date索引的使用
3、下面的看不到t_sym_operlog表的索引使用
嘗試的解決的辦法：
對資料表做採集，就是analysis
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'CSID',tabname => 't_sym_operlog_back');
end;
猜測的導致原因：
當你運用SQL語言，向資料庫釋出一條查詢語句時，ORACLE將伴隨產生一個“執行計劃”，也就是該語句將通過何種資料搜尋方案執行，是通過全表掃描、還是通過索引搜尋等其它方式。搜尋方案的選用與ORACLE的優化器息息相關。
SQL語句的執行步驟
一條SQL語句的處理過程要經過以下幾個步驟。
1 語法分析 分析語句的語法是否符合規範，衡量語句中各表示式的意義。
2 語義分析 檢查語句中涉及的所有資料庫物件是否存在，且使用者有相應的許可權。
3 檢視轉換 將涉及檢視的查詢語句轉換為相應的對基表查詢語句。
4 表示式轉換 將複雜的SQL表示式轉換為較簡單的等效連線表示式。
5 選擇優化器 不同的優化器一般產生不同的“執行計劃”
6 選擇連線方式 ORACLE有三種連線方式，對多表連線ORACLE可選擇適當的連線方式。
7 選擇連線順序 對多表連線ORACLE選擇哪一對錶先連線，選擇這兩表中哪個表做為源資料表。
8 選擇資料的搜尋路徑 根據以上條件選擇合適的資料搜尋路徑，如是選用全表搜尋還是利用索引或是其他的方式。
9 執行“執行計劃”
分析：
oracle優化器CBO存在的典型問題：
有時，表明明建有索引，但查詢過程顯然沒有用到相關的索引，導致查詢過程耗時漫長，佔用資源巨大，問題到底出在哪兒呢？按照以下順序查詢，基本上能發現原因所在。
查詢原因的步驟
首先，我們要確定資料庫執行在何種優化模式下，相應的引數是：optimizer_mode。可在svrmgrl中執行“show parameter optimizer_mode'來檢視。ORACLE V7以來預設的設定應是'choose'，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。如果該引數設為“rule”，則不論表是否分析過，一概選用RBO，除非在語句中用hint強制。
其次，檢查被索引的列或組合索引的首列是否出現在PL/SQL語句的WHERE子句中，這是“執行計劃”能用到相關索引的必要條件。
第三，看採用了哪種型別的連線方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在兩張表連線，且內表的目標列上建有索引時，只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引，最多隻能因索引的存在，避免資料排序過程。HJ由於須做HASH運算，索引的存在對資料查詢速度幾乎沒有影響。
第四，看連線順序是否允許使用相關索引。假設表emp的deptno列上有索引，表dept的列deptno上無索引，WHERE語句有emp.deptno=dept.deptno條件。在做NL連線時，emp做為外表，先被訪問，由於連線機制原因，外表的資料訪問方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。
第五，是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過，可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析，否則可能導致死鎖，或系統效能下降。
第六，索引列是否函式的引數。如是，索引在查詢時用不上。
第七，是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較，ORACLE會自動將字元型用to_number()函式進行轉換，從而導致第六種現象的發生。
第八，是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析，可用SQL語句“analyze table xxxx compute statistics for all indexes;'。ORACLE掌握了充分反映實際的統計資料，才有可能做出正確的選擇。
第九，索引列的選擇性不高。
我們假設典型情況，有表emp，共有一百萬行資料，但其中的emp.deptno列，資料只有4種不同的值，如10、20、30、40。雖然emp資料行有很多，ORACLE預設認定表中列的值是在所有資料行均勻分佈的，也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10，利用deptno列上的索引進行資料搜尋效率，往往不比全表掃描的高，ORACLE理所當然對索引“視而不見”，認為該索引的選擇性不高。
但我們考慮另一種情況，如果一百萬資料行實際不是在4種deptno值間平均分配，其中有99萬行對應著值10，5000行對應值20，3000行對應值30，2000行對應值40。在這種資料分佈圖案中對除值為10外的其它deptno值搜尋時，毫無疑問，如果索引能被應用，那麼效率會高出很多。我們可以採用對該索引列進行單獨分析，或用analyze語句對該列建立直方圖，對該列蒐集足夠的統計資料，使ORACLE在搜尋選擇性較高的值能用上索引。
第十，索引列值是否可為空（NULL）。如果索引列值可以是空值，在SQL語句中那些需要返回NULL值的操作，將不會用到索引，如COUNT（*），而是用全表掃描。這是因為索引中儲存值不能為全空。
第十一，看是否有用到並行查詢（PQO）。並行查詢將不會用到索引。
第十二，看PL/SQL語句中是否有用到bind變數。由於資料庫不知道bind變數具體是什麼值，在做非相等連線時，如“<”，“>”,“like”等。ORACLE將引用預設值，在某些情況下會對執行計劃造成影響。
如果從以上幾個方面都查不出原因的話，我們只好用採用在語句中加hint的方式強制ORACLE使用最優的“執行計劃”。
hint採用註釋的方式，有行註釋和段註釋兩種方式。
如我們想要用到A表的IND_COL1索引的話，可採用以下方式：
“SELECT * FROM A WHERE COL1 = XXX;'
注意，註釋符必須跟在SELECT之後，且註釋中的“+”要緊跟著註釋起始符“/*”或“--”，否則hint就被認為是一般註釋，對PL/SQL語句的執行不產生任何影響。
兩種有效的跟蹤除錯方法
ORACLE提供了兩種有效的工具來跟蹤除錯PL/SQL語句的執行計劃。
一種是EXPLAIN TABLE方式。使用者必須首先在自己的模式（SCHEMA）下，建立PLAN_TABLE表，執行計劃的每一步驟都將記錄在該表中，建表SQL指令碼為在${ORACLE_HOME}/rdbms/admin/下的utlxplan.sql。
開啟SQL*PLUS，輸入“SET AUTOTRACE ON”，然後執行待除錯的SQL語句。在給出查詢結果後，ORACLE將顯示相應的“執行計劃”，包括優化器型別、執行代價、連線方式、連線順序、資料搜尋路徑以及相應的連續讀、物理讀等資源代價。
如果我們不能確定需要跟蹤的具體SQL語句，比如某個應用使用一段時間後，響應速度忽然變慢。我們這時可以利用ORACLE提供的另一個有力工具TKPROF，對應用的執行過程全程跟蹤。
我們要先在系統檢視V$SESSION中，可根據USERID或MACHINE，查出相應的SID和SERIAL#。
以SYS或其他有執行DBMS_SYSTEM程式包的使用者連線資料庫，執行“EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION（SID，SERIAL#，TRUE）；”。
然後執行應用程式，這時在伺服器端，資料庫引數“USER_DUMP_DEST”指示的目錄下，會生成ora__xxxx.trc檔案，其中xxxx為被跟蹤應用的作業系統程式號。
應用程式執行完成後，用命令tkprof對該檔案進行分析。命令示例：“tkprof tracefile outputfile explain=useri
Oracle 索引 詳解
建立Oracle索引的標準語法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空間名;
建立反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空間名;
1、明確地建立索引
create index index_name on table_name(field_name)
tablespace tablespace_name
pctfree 5
initrans 2
maxtrans 255
storage
(
minextents 1
maxextents 16382
pctincrease 0
);
2、建立基於函式的索引
常用與UPPER、LOWER、TO_CHAR(date)等函式分類上，例：
create index idx_func on emp(UPPER(ename)) tablespace tablespace_name;
3、建立點陣圖索引
對基數較小，且基數相對穩定的列建立索引時，首先應該考慮點陣圖索引，例：
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
4、明確地建立唯一索引
可以用create unique index語句來建立唯一索引，例：
create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
5、建立與約束相關的索引
可以用using index字句，為與unique和primary key約束相關的索引，例：
alter table table_name
add constraint PK_primary_keyname primary key(field_name)
using index tablespace tablespace_name;
如何建立區域性區索引？
1)基礎表必須是分割槽表
2)分割槽數量與基礎表相同
3)每個索引分割槽的子分割槽數量與相應的基礎表分割槽相同
4)基礎表的自分割槽中的行的索引項，被儲存在該索引的相應的自分割槽中，例如
create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID)
Pctfree 5
Tablespace TBS_AK01_IDX
Storage(
MaxExtents 32768
PctIncrease 0
FreeLists 1
FreeList Groups 1
)
local
/
如何建立範圍分割槽的全域性索引？
基礎表可以是全域性表和分割槽表
create index idx_start_date on tg_cdr01(start_date)
global partition by range(start_date)
(partition p01_idx vlaues less than ('0106')
partition p01_idx vlaues less than ('0111')
...
partition p01_idx vlaues less than ('0401'))
/
如何重建現存的索引？
重建現存的索引的當前時刻不會影響查詢
重建索引可以刪除額外的資料塊
提高索引查詢效率
alter index idx_name rebuild nologging;
對於分割槽索引
alter index idx_name rebuild partition partition_name nologging;
刪除索引的原因？
1)不再需要的索引
2)索引沒有針對其相關的表所釋出的查詢提供所期望的效能改善
3)應用沒有用該索引來查詢資料
4)該索引無效，必須在重建之前刪除該索引
5)該索引已經變的太碎了，必須在重建之前刪除該索引
語句：
drop index idx_name;
drop index idx_name partition partition_name;
建立索引的代價？
基礎表維護時，系統要同時維護索引，不合理的索引將嚴重影響系統資源，
主要表現在CPU和I/O上。
插入、更新、刪除資料產生大量db file sequential read鎖等待。
1.重建索引
alter index index_name1 rebuild;
2.重建索引並轉移到別的表空間
alter index index_name1 rebuild tablespace other_tablespace; --這個表空間要首先建立
3.修改索引的一些引數設定
alter index index_name rebuild pctfree 30 storage(next 100k);
4.自動為索引分配空間
alter index index_name allocate extent;
5.整合索引碎片
alter index index_name coalesce;
6.刪除一個索引
drop index index_name;
7.開啟對索引的監控,然後分析索引
--首先開啟對索引的監控
alter index index_name monitoring usage;
--然後執行一個與索引有關的查詢
--接著關閉對索引的監控
alter index index_name nomonitoring usage;
--然後查詢v$object_usage檢視
select index_name,start_monitoring,end_monitoring
from v$object_usage
where index_name = '........'
一、重建索引的前提
1、表上頻繁發生update,delete操作；
2、表上發生了alter table ..move操作（move操作導致了rowid變化）。
二、重建索引的標準
1、索引重建是否有必要，一般看索引是否傾斜的嚴重，是否浪費了空間， 那應該如何才可以判斷索引是否傾斜的嚴重，是否浪費了空間， 對索引進行結構分析（如下）：
SQL>Analyze index index_name validate structure;
2、在執行步驟1的session中查詢index_stats表，不要到別的session去查詢。
SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats;
說明：當 查詢出來的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的場合 ， 該索引考慮重建 。
舉例： (t_gl_assistbalance 26 萬多條資訊 )
SQL> select count(*) from t_gl_assistbalance ;
輸出結果：
COUNT(*)
----------
265788
SQL> Analyze index IX_GL_ASSTBAL_1 validate structure;
Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
輸出結果：
HEIGHT DEL_LF_ROWS/LF_ROWS
---------- -------------------
4 1
三、重建索引的方式
1、drop 原來的索引，然後再建立索引;
舉例：
刪除索引：drop index IX_PM_USERGROUP;
建立索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid);
說明：此方式耗時間，無法在24*7環境中實現，不建議使用。
2 、直接重建：
舉例：
alter index indexname rebuild; 或alter index indexname rebuild online;
說明：此方式比較快，可以在24*7環境中實現，建議使用此方式。
四、alter index rebuild 內部過程和注意點
alter index rebuild 和alter index rebuil online的區別
1、掃描方式不同
1.1、Rebuild以index fast full scan（or table full scan） 方式讀取原索引中的資料來構建一個新的索引，有排序的操作;
1.2、rebuild online 執行表掃描獲取資料，有排序的操作;
說明：Rebuild 方式 (index fast full scan or table full scan 取決於統計資訊的cost)
舉例1
SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |
| 2 | SORT CREATE INDEX | | 999K| 4882K| |
| 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| |
---------------------------------------------------------------------
舉例2
SQL> explain plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | |
| 2 | SORT CREATE INDEX | | 2072K| 9M| |
| 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 |
舉例3 ( 注意和 舉例1 比較 )
Rebuil online 方式 ：
SQL> explain plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |
| 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |
| 2 | SORT CREATE INDEX | | 999K| 4882K| |
| 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 |
2 、rebuild 會阻塞 dml 操作 ，rebuild online 不會阻塞 dml 操作 ；
3 、rebuild online 時系統會產生一個 SYS_JOURNAL_xxx 的 IOT 型別的系統臨時日誌表 ， 所有 rebuild online 時索引的變化都記錄在這個表中 ， 當新的索引建立完成後 ， 把這個表的記錄維護到新的索引中去 ， 然後 drop 掉舊的索引 ，rebuild online 就完成了。
注意點：
1、 執行rebuild操作時，需要檢查表空間是否足夠；
2、雖然說rebuild online操作允許dml操作，但是還是建議在業務不繁忙時間段進行；
Rebuild操作會產生大量redo log ；
五、重建分割槽表上的分割槽索引
重建分割槽索引方法：
Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
Partition name 可以從user_ind_partitions查詢
Tablepace 引數允許alter index操作更改索引的儲存空間；
六、索引狀態描述
在資料字典中檢視索引狀態，發現有三種：
valid:當前索引有效
N/A :分割槽索引 有效
unusable:索引失效
七、術語
1、高基數：簡單理解就是表中列的不同值多。
2、低基數：建單理解就是表中的列的不同值少。
3、以刪除的葉節點數量：指得是資料行的delete操作從邏輯上刪除的索引節點 的數量，要記住oracle在刪除資料行後，將 “ 死 “ 節點保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除資料行後可以不必重新平衡索引。
4、索引高度：索引高度是指由於資料行的插入操作而產生的索引層數，當表中新增大量資料時，oracle將生成索引的新層次以適應加入的資料行，因此，oracle索引可能有4層，但是這隻會出現在索引數中產生大量插入操作的區域。Oracle索引的三層結構可以支援數百萬的專案，而具備4層或是更多層的需要重建。
5、每次索引訪問的讀取數：是指利用索引讀取一資料行時所需要的邏輯I/O運算元，邏輯讀取不必是物理讀取，因為索引的許多內容已經儲存在資料緩衝區，然而，任何資料大於10的索引都需要重建。
6、什麼時候重建呢？
察看 dba_indexes 中的 blevel 。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大於等於4。則需要重建，
如下 ：Select index_name,blevel from dba_indexes where blevel>=4.
另一個從重建中受益的指標顯然是當該索引中的被刪除項佔總的項數的百分比。如果在20%以上時，也應當重建，如下
SQL>analyze index index_name validate structure
SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’
就能看到是否這個索引被刪除的百分比。
7、什麼樣的重建方式更好？
（1）、建索引的辦法：
1.1、刪除並從頭開始建立索引。
1.2 、 使用 alter index index_name rebuild 命令重建索引。
1.3 、 使用 alter index index_name coalesce 命令重建索引。
（2）、下面討論一下這三種方法的優缺點：
2.1、刪除並從頭開始建索引：方法是最慢的，最耗時的。一般不建議。
2.2、Alter index index_name rebuild
快速重建索引的一種有效的辦法，因為使用現有索引項來重建新索引，如果客戶操作時有其他使用者在對這個表操作，儘量使用帶online引數來最大限度的減少索引重建時將會出現的任何加鎖問題，alter index index_name rebuild online。
但是,由於新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁碟空間可臨時使用，當索引建完後把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引移到新的表空間。
Alter index index_name rebuild tablespace tablespace_name 。
這個命令的執行步驟如下：
首先，逐一讀取現有索引，以獲取索引的關鍵字。
其次，按新的結構填寫臨時資料段。
最後，一旦操作成功，刪除原有索引樹，降臨時資料段重新命名為新的索引。
需要注意的是alter index index_name rebuild 命令中必須使用tablespace字句，以保證重建工作是在現有索引相同的表空間進行。
2.3、alter index index_name coalesce
使用帶有coalesce引數時重建期間不需要額外空間，它只是在重建索引時將處於同一個索引分支內的葉塊拼合起來，這最大限度的減少了與查詢過程中相關的潛在的加鎖問題，但是，coalesce選項不能用來將一個索引轉移到其他表空間。
八、其他
1、truncate 分割槽操作和truncate 普通表的區別？
1.1、Truncate 分割槽操作會導致全域性索引失效; truncate 普通表對索引沒有影響；
1.2、Truncate 分割槽操作不會釋放全域性索引中的空間，而truncate 普通表會釋放索引所佔空間；
2、rename 表名操作對索引沒有影響，因為rename操作只是更改了資料字典，表中資料行的rowid並沒有發生變化
總結：
1、判斷是否需要重建索引：
SQL>analyze index index_name validate structure;
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
（ 或 Select index_name,blevel from dba_indexes where blevel>=4 ）;
說明 ： 當查詢出來的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的場合 ， 該索引考慮重建 ；
2 、重建索引方法 ：
方法一、
Alter index index_name rebuild tablespace tablespace_name;
優點：是快速重建索引的一種有效的辦法，可以用來將一個索引移到新的表空間。
缺點：重建期間需要額外空間。
方法二、
alter index index_name coalesce;
優點：重建期間不需要額外空間。
缺點：coalesce選項不能用來將一個索引轉移到其他表空間。
索引的三大特點：
1.索引數的高度一般較低
2.索引由索引列儲存的值及rowid組成
索引SELECT * FROM T WHERE ID = 1會導致索引回表的產生，若不需要看全部資料可用SELECT ID FROM T WHERE ID= 1或可建多列的複核索引，但是複核索引最好不要超過3列的複核。在更新操作不頻繁的情況下可考慮使用索引組織表
3.索引本身是有序的
減少ORDER BY、DISTINCT排序所浪費的COST
聚合因子：
建立索引的列的順序與索引自動排序的列的順序的不對應度被稱為聚合因子，聚合因子越大索引回表讀越浪費時間(索引回表讀不可避免的情況下)
Oracle執行計劃分類：
1、TABLE ACCESS FULL 全表掃描
2、INDEX FAST FULL SCAN 索引快速掃描 (不考慮排序COUNT(ID), SUM(ID), AVG(ID)，列必須非空或IS NOT NULL)
3、INDEX FULL SCAN 索引全掃 (SELECT ID FROM T WHERE ID = 120)
4、INDEX FULL SCAN(MIN/MAX) 最大值最小值索引全掃 (SELECT MAX(ID) FROM T)
5、TABLE ACCESS BY INDEX ROWID 索引回表讀 (SELECT * FROM T WHERE ID = 1)
6、INDEX RANGE SCAN 索引範圍掃描 (SELECT * FROM T WHERE ID < 100)
7、BITMAP INDEX FAST FULL SCAN 點陣圖索引快速掃描 (不考慮排序COUNT(ID), SUM(ID), AVG(ID)，點陣圖快速索引掃描速度非常快將近是普通索引的百倍.列不必非空)
btree索引優化簡介
T表 欄位：OBJECT_ID, OBJECT_NAME
單列索引：
CREATE INDEX IDX1_OBJECT_ID ON T(OBJECT_ID);
COUNT(*)、SUM、AVG優化：
改為COUNT(OBJECT_ID)查詢，但是必須保證OBJECT_ID列非空。
可用如下方法告知oracle可走IDX1_OBJECT_ID索引：
1、SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_ID IS NOT NULL;
2、設定欄位非空
MAX/MIN優化：
根據第三個特性有序排列，所以MAX/MIN的查詢代價會非常小。
SELECT MAX(OBJECT_ID) FROM T; 不需加上IS NOT NULL; 使用執行計劃：INDEX FULL SCAN(MIN/MAX);
ORDER BY、DISTINCT排序優化：
SELECT * FROM T WHERE OBJECT_ID < 100 ORDER BY OBJECT_ID;
未建立索引的情況下會進行排序產生TEMPSPC;
建立索引的情況下不需要產生排序 會使用IDX1_OBJECT_ID索引
點陣圖索引
建立語法：
CREATE BITMAP INDEX IDX_BITM_T_STATUS ON T(STATUS);
適用在更新非常少的表，建立在重複度較高的列(性別)
儲存結構：
點陣圖索引儲存的是位元位值
函式索引：
CREATE TABLE T AS SELECT * FROM DBA_OBJECT;
CREATE INDEX IDX_OBJECT_ID ON T(OBJECT_ID);
CREATE INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME);
CREATE INDEX IDX_CREATED ON T(CREATED);
SELECT * FROM T WHERE UPPER(OBJECT_NAME) = 'T';
普通的BTREE索引，如果在對列做運算的條件下是無法使用索引查詢的，會使用TABLE ACCESS FULL;
建立語句：
CREATE INDEX IDX_UPPER_OBJ_NAME ON(UPPER(OBJECT_NAME));
SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'T';
函式索引的TYPE是：FUNCTION-BASED NORMAL;
函式索引的cost比全表掃描要小，但是比普通的索引要大的多。
SELECT * FROM T WHERE OBJECT_ID - 10<30;
這時候如果在object_id列建立普通索引時無法使用的。oracle會預設使用全表掃描的方式進行查詢。可有以下兩個思路進行優化：
1、SELECT * FROM T WHERE OBEJCT_ID < 40;
2、在OBJECT_ID - 10上建立函式索引
寫sql時要注意規範，很多語句是等價的。
SELECT * FROM T WHERE SUBSTR(OBJECT_NAME,1，4) = 'CLUS' = SELECT * FROM T WHERE OBJECT_NAME LIKE 'CLUS%';
SELECT * FROM T WHERE TRUNC(CREATED) >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND TRUNC(CREATED) <= TO_DATE('2012-10-03','YYYY-MM-DD')
=
SELECT * FROM T WHERE CREATED >= TO_DATE('2012-10-02', 'YYYY-MM-DD') AND CREATED < TO_DATE('2012-10-03','YYYY-MM-DD')+1;
Oracle 索引 詳解
一． 索引介紹
1.1 索引的建立 語法 ：
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>
相關說明
1） UNIQUE | BITMAP ：指定UNIQUE 為唯一值索引， BITMAP 為點陣圖索引， 省略為B-Tree 索引。
2） <column_name> | <expression> ASC | DESC ：可以對多列進行聯合索引，當為expression 時即 “ 基於函式的索引 ”
3） TABLESPACE ：指定存放索引的表空間 (索引和原表不在一個表空間時效率更高 )
4） STORAGE ：可進一步設定表空間的儲存引數
5） LOGGING | NOLOGGING ：是否對索引產生重做日誌( 對大表儘量使用 NOLOGGING 來減少佔用空間並提高效率 )
6） COMPUTE STATISTICS ：建立新索引時收集統計資訊
7） NOCOMPRESS | COMPRESS<nn> ：是否使用“ 鍵壓縮 ”( 使用鍵壓縮可以刪除一個鍵列中出現的重複值 )
8） NOSORT | REVERSE ：NOSORT 表示與表中相同的順序建立索引， REVERSE 表示相反順序儲存索引值
9） PARTITION | NOPARTITION ：可以在 分割槽表 和未分割槽表上對建立的索引進行分割槽
1. 2 索引特點：
第一 ，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二 ，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
第三 ，可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
第四 ，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五 ，通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。
1. 3 索引不足：
第一 ，建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
第二 ，索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。
第三 ，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。
1. 4 應該建索引列的特點：
1） 在經常需要搜尋的列上，可以加快搜尋的速度；
2） 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
3） 在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；
4） 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
5） 在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
6） 在經常使用在WHERE 子句中的列上面建立索引，加快條件的判斷速度。
1. 5 不應該建索引列的特點：
第一 ，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。
第二 ，對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
第三 ，對於那些定義為 blob 資料型別的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。
第四 ，當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。
1.6 限制索引
限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL 中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：
1.6.1 使用不等於操作符（<> 、 != ）
下面的查詢即使在cust_rating 列有一個索引，查詢語句仍然執行一次全表掃描。
select cust_Id,cust_name from customers where cust_rating <> 'aa';
把上面的語句改成如下的查詢語句，這樣，在採用基於規則的優化器而不是基於代價的優化器（更智慧）時，將會使用索引。
select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';
特別注意：通過把不等於操作符改成 OR 條件，就可以使用索引，以避免全表掃描。
1.6. 2 使用 IS NULL 或 IS NOT NULL
使用 IS NULL 或 IS NOT NULL 同樣會限制索引的使用 。因為 NULL 值並沒有被定義。在 SQL 語句中使用 NULL 會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成 NOT NULL 。如果被索引的列在某些行中存在 NULL 值，就不會使用這個索引（除非索引是一個點陣圖索引，關於點陣圖索引在稍後在詳細討論）。
1.6 .3 使用函式
如果不使用基於函式的索引，那麼在SQL 語句的 WHERE 子句中對存在索引的列使用函式時，會使優化器忽略掉這些索引。
下面的查詢不會使用索引（只要它不是基於函式的索引）
select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
把上面的語句改成下面的語句，這樣就可以通過索引進行查詢。
select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);
1.6 .4 比較不匹配的資料型別
也是比較難於發現的效能問題之一。 注意下面查詢的例子，account_number 是一個 VARCHAR2 型別 , 在 account_number 欄位上有索引。
下面的語句將執行全表掃描 ：
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自動把 where 子句變成 to_number(account_number)=990354 ，這樣就限制了索引 的使用 , 改成下面的查詢就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number ='990354';
特別注意： 不匹配的資料型別之間比較會讓Oracle 自動限制索引的使用 , 即便對這個查詢執行 Explain Plan 也不能讓您明白為什麼做了一次 “ 全表掃描 ” 。
1. 7 查詢 索引
查詢DBA_INDEXES 檢視可得到表中所有索引的列表，注意只能通過 USER_INDEXES 的方法來檢索模式 (schema) 的索引。訪問 USER_IND_COLUMNS 檢視可得到一個給定表中被索引的特定列。
1. 8 組合索引
當某個索引包含有多個已索引的列時，稱這個索引為 組合（concatented ）索引 。在 Oracle9i 引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表 emp 有一個組合索引鍵，該索引包含了 empno 、 ename 和 deptno 。在 Oracle9i 之前除非在 where 之句中對第一列（ empno ）指定一個值，否則就不能使用這個索引鍵進行一次範圍掃描。
特別注意：在Oracle9i 之前，只有在使用到索引的前導索引時才可以使用組合索引！
1. 9 ORACLE ROWID
通過每個行的ROWID ，索引 Oracle 提供了訪問單行資料的能力。 ROWID 其實就是直接指向單獨行的線路圖。如果想檢查重複值或是其他對 ROWID 本身的引用，可以在任何表中使用和指定 rowid 列。
1.10 選擇性
使用USER_INDEXES 檢視，該檢視中顯示了一個 distinct_keys 列。比較一下唯一鍵的數量和表中的行數，就可以判斷索引的選擇性。選擇性越高，索引返回的資料就越少。
1.11 群集因子(Clustering Factor)
Clustering Factor位於 USER_INDEXES 檢視中。該列反映了資料相對於已 建 索引的列是否顯得有序。如果Clustering Factor 列的值接近於索引中的樹葉塊 (leaf block) 的數目，表中的資料就越有序。如果它的值接近於表中的行數，則表中的資料就不是很有序。
1.12 二元高度(Binary height)
索引的二元高度對把ROWID 返回給使用者程式時所要求的 I/O 量起到關鍵作用。在對一個索引進行分析後，可以通過查詢 DBA_INDEXES 的 B- level 列檢視它的二元高度 。二元高度主要隨著表的大小以及被索引的列中值的範圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會增加。更新索引列也類似於刪除操作，因為它增加了已刪除鍵的數目。 重建索引可能會降低二元高度 。
1.13 快速全域性掃描
從 Oracle7.3後就可以使用快速全域性掃描 (Fast Full Scan) 這個選項。這個選項允許 Oracle 執行一個全域性索引掃描操作。快速全域性掃描讀取 B- 樹索引上所有樹葉塊。初始化檔案中的 DB_FILE_MULTIBLOCK_READ_COUNT 引數可以控制同時被讀取的塊的數目。
1.14 跳躍式掃描
從Oracle9i 開始，索引跳躍式掃描特性可以允許優化器使用組合索引，即便索引的前導列沒有出現在 WHERE 子句中。索引跳躍式掃描比全索引掃描 要快的多。
下面的 比較他們的區別 ：
SQL> set timing on
SQL> create index TT_index on TT(teamid,areacode);
索引已建立。
已用時間: 00: 02: 03.93
SQL> select count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時間: 00: 00: 08.31
SQL> select /*+ index(tt TT_index )*/ count(areacode) from tt;
COUNT(AREACODE)
---------------
7230369
已用時間: 00: 00: 07.37
1.15 索引的型別
B-樹索引
點陣圖索引
HASH索引
索引編排表
反轉鍵索引
基於函式的索引
分割槽索引
本地和全域性索引
二． 索引分類
Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對於一個應用程式的效能來說非常重要。一個錯誤的選擇可能會引發死鎖，並導致資料庫效能急劇下降或程式終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經執行了幾個小時甚至幾天的程式在幾分鐘得以完成，這樣會使您立刻成為一位英雄。 下面 就將簡單的討論每個索引選項。
下面討論的索引型別：
B樹索引 (預設型別 )
點陣圖索引
HASH索引
索引組織表索引
反轉鍵(reverse key) 索引
基於函式的索引
分割槽索引( 本地和全域性索引 )
點陣圖連線索引
2.1 B樹索引 (預設型別 )
B樹索引在 Oracle 中是一個通用索引。在建立索引時它就是預設的索引型別 。 B 樹索引可以是一個列的 ( 簡單 ) 索引，也可以是組合 / 複合 ( 多個列 ) 的索引。 B 樹索引最多可以包括 32 列 。
在 下圖 的例子中，B 樹索引位於僱員表的 last_name 列上。這個索引的二元高度為 3 ；接下來， Oracle 會穿過兩個樹枝塊 (branch block) ，到達包含有 ROWID 的樹葉塊。在每個樹枝塊中，樹枝行包含鏈中下一個塊的 ID 號。
樹葉塊包含 了 索引值 、 ROWID ，以及指向前一個和後一個樹葉塊的 指標 。Oracle 可以從兩個方向遍歷這個二叉樹。 B 樹索引儲存了在索引列上有值的每個資料行的 ROWID 值。 Oracle不會對索引列上包含 NULL 值的行進行索引 。如果索引是多個列的組合索引，而其中列上包含NULL 值，這一行就會處於包含 NULL 值的索引列中，且將被處理為空 ( 視為 NULL) 。
技巧 ： 索引列的值都儲存在索引中。因此，可以建立一個組合 ( 複合 ) 索引，這些索引可以直接滿足查詢，而不用訪問表。這就不用從表中檢索資料，從而減少了 I/O 量。
B-tree 特點 ：
適合與大量的增、刪、改（OLTP ）
不能用包含OR 操作符的查詢；
適合高基數的列（唯一值多）
典型的樹狀結構；
每個結點都是資料塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊資料是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的範圍；
2.2 點陣圖索引
點陣圖索引非常適合於決策支援系統(Decision Support System ， DSS) 和資料倉儲 ，它們不應該用於通過事務處理應用程式訪問的表。它們可以使用較少到中等基數( 不同值的數量 ) 的列訪問非常大的表。儘管點陣圖索引最多可達 30 個列，但通常它們都只用於少量的列。
例如，您的表可能包含一個稱為Sex 的列，它有兩個可能值：男和女。這個基數只為 2 ，如果使用者頻繁地根據 Sex 列的值查詢該表，這就是點陣圖索引的基列。當一個表內包含了多個點陣圖索引時，您可以體會到點陣圖索引的真正威力。如果有多個可用的點陣圖索引， Oracle 就可以合併從每個點陣圖索引得到的結果集，快速刪除不必要的資料。
Bitmap t 特點 ：
適合與決策支援系統；
做UPDATE 代價非常高；
非常適合OR 操作符的查詢；
基數比較少的時候才能建點陣圖索引；
技巧： 對於有較低基數的列需要使用點陣圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女( 基數僅為 2) 。 點陣圖對於低基數( 少量的不同值 ) 列來說非常快 ，這是因為索引的尺寸相對於B 樹索引來說小了很多。因為這些索引是低基數的 B 樹索引，所以非常小，因此您可以經常檢索表中超過半數的行，並且仍使用點陣圖索引。
當大多數條目不會向點陣圖新增新的值時，點陣圖索引在批處理( 單使用者 ) 操作中載入表 ( 插入操作 ) 方面通常要比 B 樹做得好。當多個會話同時向表中插入行時不應該使用點陣圖索引，在大多數事務處理應用程式中都會發生這種情況。
示例
下面來看一個示例表PARTICIPANT ，該表包含了來自個人的調查資料。列 Age_Code 、 Income_Level 、 Education_Level 和 Marital_Status 都包括了各自的點陣圖索引。 下圖 顯示了每個直方圖中的資料平衡情況，以及對訪問每個點陣圖索引的查詢的執行路徑。圖中的執行路徑顯示了有多少個點陣圖索引被合併，可以看出效能得到了顯著的提高。
如 上圖 圖所示，優化器依次使用4 個單獨的點陣圖索引，這些索引的列在 WHERE 子句中被引用。每個點陣圖記錄指標 ( 例如 0 或 1) ，用於指示表中的哪些行包含點陣圖中的已知值。有了這些資訊後， Oracle 就執行 BITMAP AND 操作以查詢將從所有 4 個點陣圖中返回哪些行。該值然後被轉換為 ROWID 值，並且查詢繼續完成剩餘的處理工作。 注意，所有4 個列都有非常低的基數，使用索引可以非常快速地返回匹配的行。
技巧： 在一個查詢中合併多個點陣圖索引後，可以使效能顯著提高。點陣圖索引使用固定長度的資料型別要比可變長度的資料型別好。較大尺寸的塊也會提高對點陣圖索引的儲存和讀取效能。
下面的查詢可顯示索引型別。
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ----------------------
TT_INDEX NORMAL
IX_CUSTADDR_TP NORMAL
B樹索引 作為NORMAL 列出；而 點陣圖索引 的型別值為 BITMAP 。
技巧： 如果要查詢點陣圖索引列表，可以在USER _INDEXES 檢視中查詢 index_type 列。
建議不要在一些聯機事務處理(OLTP) 應用程式中使用點陣圖索引 。B 樹索引的索引值中包含 ROWID ，這樣 Oracle 就可以在行級別上鎖定索引。點陣圖索引儲存為壓縮的索引值，其中包含了一定範圍的 ROWID ，因此 Oracle 必須針對一個給定值鎖定所有範圍內的 ROWID 。這種鎖定型別可能在某些 DML 語句中造成死鎖。 SELECT 語句不會受到這種鎖定問題的影響。
點陣圖索引 的使用 限制 ：
基於規則的優化器不會考慮點陣圖索引。
當執行ALTER TABLE 語句並修改包含有點陣圖索引的列時，會使點陣圖索引失效。
點陣圖索引不包含任何列資料，並且不能用於任何型別的完整性檢查。
點陣圖索引不能被宣告為唯一索引。
點陣圖索引的最大長度為30 。
技巧： 不要在繁重的OLTP 環境中使用點陣圖索引
2.3 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 對於一些包含有序值的靜態資料非常有效。
技巧： HASH索引在有限制條件 ( 需要指定一個確定的值而不是一個值範圍 ) 的情況下非常有用。
2.4 索引組織表
索引組織表會把表的儲存結構改成B 樹結構，以表的主鍵進行排序。這種特殊的表和其他型別的表一樣，可以在表上執行所有的 DML 和 DDL 語句。 由於表的特殊結構，ROWID 並沒有被關聯到表的行上。
對於一些涉及精確匹配和範圍搜尋的語句，索引組織表提供了一種基於鍵的快速資料訪問機制。 基於主鍵值的UPDATE 和 DELETE 語句的效能也同樣得以提高， 這是因為行在物理上有序。由於鍵列的值在表和索引中都沒有重複， 儲存所需要的空間也隨之減少。
如果不會頻繁地根據主鍵列查詢資料，則需要在索引組織表中的其他列上建立二級索引。不會頻繁根據主鍵查詢表的應用程式不會了解到使用索引組織表的全部優點。 對於總是通過對主鍵的精確匹配或範圍掃描進行訪問的表，就需要考慮使用索引組織表。
技巧： 可以在索引組織表上建立二級索引。
2.5 反轉鍵索引
當載入一些有序資料時，索引肯定會碰到與I/O 相關的一些瓶頸。在資料載入期間，某部分索引和磁碟肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把 檔案物理分割在多個磁碟上的磁碟體系結構上 。
為了解決這個問題，Oracle 還提供了一種反轉鍵索引的方法。如果資料以反轉鍵索引儲存，這些資料的值就會與原先儲存的數值相反。這樣，資料 1234 、 1235 和 1236 就被儲存成 4321 、 5321 和 6321 。 結果就是索引會為每次新插入的行更新不同的索引塊。
技巧： 如果您的磁碟容量有限，同時還要執行大量的有序載入，就可以使用反轉鍵索引。
不可以將反轉鍵索引與點陣圖索引或索引組織表結合使用。 因為 不能對點陣圖索引和索引組織表進行反轉鍵處理。
2.6 基於函式的索引
可以在表中建立基於函式的索引。如果沒有基於函式的索引，任何在列上執行了函式的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用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 語句的執行就會花費越多的時間。
注意： 對於優化器所使用的基於函式的索引來說， 必須把初始引數QUERY _REWRITE _ ENABLED 設定為 TRUE 。
示例：
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes
create index ratio_idx1 on sample (ratio(balance, limit));
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!
2.7 分割槽索引
分割槽索引就是簡單地把一個索引分成多個片斷。通過把一個索引分成多個片斷，可以訪問更小的片斷( 也更快 ) ，並且可以把這些片斷分別存放在不同的磁碟驅動器上 ( 避免 I/O 問題 ) 。 B樹和點陣圖索引都可以被分割槽，而 HASH 索引不可以被分割槽 。可以有好幾種分割槽方法： 表被分割槽而索引未被分割槽 ； 表未被分割槽而索引被分割槽 ； 表和索引都被分割槽 。不管採用哪種方法， 都必須使用基於成本的優化器 。分割槽能夠提供更多可以提高效能和可維護性的可能性
有兩種型別的分割槽索引： 本地分割槽索引 和 全域性分割槽索引 。每個型別都有兩個子型別，有字首索引和無字首索引。表各列上的索引可以有各種型別索引的組合。 如果使用了點陣圖索引，就必須是本地索引 。把索引分割槽最主要的原因是可以減少所需讀取的索引的大小，另外把分割槽放在不同的表空間中可以提高分割槽的可用性和可靠性。
在使用分割槽後的表和索引時，Oracle 還支援並行查詢和並行 DML 。這樣就可以同時執行多個程式，從而加快處理這條語句。
2.7. 1.本地分割槽索引 ( 通常使用的索引 )
可以使用與表相同的分割槽鍵和範圍界限來對本地索引分割槽。每個本地索引的分割槽只包含了它所關聯的表分割槽的鍵和ROWID 。本地索引可以是 B 樹或點陣圖索引。如果是 B 樹索引，它可以是唯一或不唯一的索引。
這種型別的索引支援分割槽獨立性，這就意味著對於單獨的分割槽，可以進行增加、擷取、刪除、分割、離線等處理，而不用同時刪除或重建索引。 Oracle自動維護這些本地索引。 本地索引分割槽還可以被單獨重建，而其他分割槽不會受到影響。
2.7.1.1 有字首的索引
有字首的索引包含了來自分割槽鍵的鍵，並把它們作為索引的前導。例如，讓我們再次回顧participant 表。在建立該表後，使用 survey_id 和 survey_date 這兩個列進行範圍分割槽，然後在 survey_id 列上建立一個有字首的本地索引，如 下圖 所示。這個索引的所有分割槽都被等價劃分，就是說索引的分割槽都使用表的相同範圍界限來建立。
技巧： 本地的有字首索引可以讓Oracle 快速剔除一些不必要的分割槽。也就是說沒有包含 WHERE 條件子句中任何值的分割槽將不會被訪問，這樣也提高了語句的效能。
2.7.1.2 無字首的索引
無字首的索引並沒有把分割槽鍵的前導列作為索引的前導列。若使用有同樣分割槽鍵(survey_id 和 survey_date) 的相同分割槽表，建立在 survey_date 列上的索引就是一個本地的無字首索引，如 下圖 所示。可以在表的任一列上建立本地無字首索引，但索引的每個分割槽只包含表的相應分割槽的鍵值。
如果要把無字首的索引設為唯一索引，這個索引就必須包含分割槽鍵的子集。在這個例子中，我們必須把包含survey 和 ( 或 )survey_id 的列進行組合 ( 只要 survey_id 不是索引的第一列，它就是一個有字首的索引 ) 。
技巧： 對於一個唯一的無字首索引，它必須包含分割槽鍵的子集。
2.7. 2. 全域性分割槽索引
全域性分割槽索引在一個索引分割槽中包含來自多個表分割槽的鍵。一個全域性分割槽索引的分割槽鍵是分割槽表中不同的或指定一個範圍的值。在建立全域性分割槽索引時，必須定義分割槽鍵的範圍和值。 全域性索引只能是B 樹索引 。 Oracle在預設情況下不會維護全域性分割槽索引。如果一個分割槽被擷取、增加、分割、刪除等，就必須重建全域性分割槽索引 ，除非在修改表時指定ALTER TABLE 命令的 UPDATE GLOBAL INDEXES 子句。
2.7.2.1 有字首的索引
通常，全域性有字首索引在底層表中沒有經過對等分割槽。沒有什麼因素能限制索引的對等分割槽，但Oracle 在生成查詢計劃或執行分割槽維護操作時，並不會充分利用對等分割槽。如果索引被對等分割槽，就必須把它建立為一個本地索引，這樣 Oracle 可以維護這個索引，並使用它來刪除不必要的分割槽，如 下圖 所示。在該圖的3 個索引分割槽中，每個分割槽都包含指向多個表分割槽中行的索引條目。
分割槽的、全域性有字首索引
技巧 ： 如果一個全域性索引將被對等分割槽，就必須把它建立為一個本地索引，這樣Oracle 可以維護這個索引，並使用它來刪除不必要的分割槽。
2.7.2.2 無字首的索引
Oracle不支援無字首的全域性索引。
2.8 點陣圖連線索引
點陣圖連線索引是基於兩個表的連線的點陣圖索引，在資料倉儲環境中使用這種索引改進連線維度表和事實表的查詢的效能。建立點陣圖連線索引時，標準方法是連線索引中常用的維度表和事實表。當使用者在一次查詢中結合查詢事實表和維度表時，就不需要執行連線，因為在點陣圖連線索引中已經有可用的連線結果。通過壓縮點陣圖連線索引中的ROWID 進一步改進效能，並且減少訪問資料所需的 I/O 數量。
建立點陣圖連線索引時，指定涉及的兩個表。相應的語法應該遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;
點陣圖連線的語法比較特別，其中包含FROM 子句和 WHERE 子句，並且引用兩個單獨的表。索引列通常是維度表中的描述列 —— 就是說，如果維度是 CUSTOMER ，並且它的主鍵是 CUSTOMER_ID ，則通常索引 Customer_Name 這樣的列。如果事實表名為 SALES ，可以使用如下的命令建立索引：
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;
如果使用者接下來使用指定Customer_Name 列值的 WHERE 子句查詢 SALES 和 CUSTOMER 表，優化器就可以使用點陣圖連線索引快速返回匹配連線條件和 Customer_Name 條件的行。
點陣圖連線索引的使用一般會受到限制 ：
1） 只可以索引維度表中的列。
2） 用於連線的列必須是維度表中的主鍵或唯一約束；如果是複合主鍵，則必須使用連線中的每一列。
3） 不可以對索引組織表建立點陣圖連線索引，並且適用於常規點陣圖索引的限制也適用於點陣圖連線索引。
Oracle 索引的分類,分析與比較
邏輯上：
Single column 單行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based函式索引
Domain 域索引
物理上：
Partitioned 分割槽索引
NonPartitioned 非分割槽索引
B-tree：
Normal 正常型B樹
Rever Key 反轉型B樹
Bitmap 點陣圖索引
索引結構：
B-tree：
適合與大量的增、刪、改（OLTP）；
不能用包含OR操作符的查詢；
適合高基數的列（唯一值多）
典型的樹狀結構；
每個結點都是資料塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊資料是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的範圍；
Bitmap:
適合與決策支援系統；
做UPDATE代價非常高；
非常適合OR操作符的查詢；
基數比較少的時候才能建點陣圖索引；
樹型結構：
索引頭
開始ROWID，結束ROWID（先列出索引的最大範圍）
BITMAP
每一個BIT對應著一個ROWID，它的值是1還是0，如果是1，表示著BIT對應的ROWID有值；[@more@]
Oracle索引分析與比較
首先給出各種索引的簡要解釋：
b*tree index：幾乎所有的關係型資料庫中都有b*tree型別索引，也是被最多使用的。其樹結構與二叉樹比較類似，根據rid快速定位所訪問的行。
反向索引：反轉了b*tree索引碼中的位元組，是索引條目分配更均勻，多用於並行伺服器環境下，用於減少索引葉的競爭。
降序索引：8i中新出現的索引型別，針對逆向排序的查詢。
點陣圖索引：使用點陣圖來管理與資料行的對應關係，多用於OLAP系統。
函式索引：這種索引中儲存了資料列基於function返回的值，在select * from table where function(column)=value這種型別的語句中起作用。
2 各種索引的結構分析
2.1 B*Tree索引B*Tree索引是最常見的索引結構，預設建立的索引就是這種型別的索引。B*Tree索引在檢索高基數資料列（高基數資料列是指該列有很多不同的值）時提供了最好的效能。當取出的行數佔總行數比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當檢查的範圍超過表的10%時就不能提高取回資料的效能。B-Tree索引是基於二叉樹的，由分支塊（branch block）和葉塊（leaf block）組成。在樹結構中，位於最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應的rowid。在葉節點的上面是分支塊，用來導航結構，包含了索引列（關鍵字）範圍和另一索引塊的地址。
假設我們要找索引中值為80的行，從索引樹的最上層入口開始，定位到大於等於50，然後往左找，找到第2個分支塊，定位為75－100，最後再定位到葉塊上，找到80所對應的rowid，然後根據rowid去讀取資料塊獲取資料。如果查詢條件是範圍選擇的，比如where column >20 and column <80，那麼會先定位到第一個包含20的葉塊，然後橫向查詢其他的葉塊，直到找到包含80的塊為止，不用每次都從入口進去再重新定位。
2.2 反向索引
反向索引是B*Tree索引的一個分支，它的設計是為了運用在某些特定的環境下的。Oracle推出它的主要目的就是為了降低在並行伺服器（Oracle Parallel Server）環境下索引葉塊的爭用。當B*Tree索引中有一列是由遞增的序列號產生的話，那麼這些索引資訊基本上分佈在同一個葉塊，當使用者修改或訪問相似的列時，索引塊很容易產生爭用。反向索引中的索引碼將會被分佈到各個索引塊中，減少了爭用。反向索引反轉了索引碼中每列的位元組，通過dump（）函式我們可以清楚得看見它做了什麼。舉個例子：1，2，3三個連續的數，用dump()函式看它們在Oracle內部的表示方法。
SQL> select 'number',dump(1,16) from dual
2 union all select 'number',dump(2,16) from dual
3 union all select 'number',dump(3,16) from dual;
'NUMBE DUMP(1,16)
------ -----------------
number Typ=2 Len=2: c1,2 （1）
number Typ=2 Len=2: c1,3 （2）
number Typ=2 Len=2: c1,4 （3）
再對比一下反向以後的情況：
SQL> select 'number',dump(reverse(1),16) from dual
2 union all select 'number',dump(reverse(2),16) from dual
3 union all select 'number',dump(reverse(3),16) from dual;
'NUMBE DUMP(REVERSE(1),1
------ -----------------
number Typ=2 Len=2: 2,c1 （1）
number Typ=2 Len=2: 3,c1 （2）
number Typ=2 Len=2: 4,c1 （3）
我們發現索引碼的結構整個顛倒過來了，這樣1，2，3個索引碼基本上不會出現在同一個葉塊裡，所以減少了爭用。不過反向索引又一個缺點就是不能在所有使用常規索引的地方使用。在範圍搜尋中其不能被使用，例如，where column>value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜尋相鄰葉塊完成區域掃描。
2.3 降序索引
降序索引是8i裡面新出現的一種索引，是B*Tree的另一個衍生物,它的變化就是列在索引中的儲存方式從升序變成了降序，在某些場合下降序索引將會起作用。舉個例子，我們來查詢一張表並進行排序：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
這裡優化器首先選擇了一個索引範圍掃描,然後還有一個排序的步驟。如果使用了降序索引，排序的過程會被取消。
SQL> create index test.ind_desc on test.testrev(a desc，b asc);
索引已建立。
SQL> analyze index test.ind_desc compute statistics;
索引已分析
再來看下執行路徑：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan（SQL執行計劃，稍後會講解如何使用）。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
我們看到排序過程消失了，這是因為建立降序索引時Oracle已經把資料都按降序排好了。另外一個需要注意的地方是要設定init.ora裡面的compatible引數為8.1.0或以上，否則建立時desc關鍵字將被忽略。
2.4 點陣圖索引
點陣圖索引主要用於決策支援系統或靜態資料，不支援行級鎖定。點陣圖索引最好用於低cardinality列（即列的唯一值除以行數為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那麼3/3000000約等於0，這種情況下最適合用點陣圖索引。
點陣圖索引可以是簡單的（單列）也可以是連線的（多列），但在實踐中絕大多數是簡單的。在這些列上多點陣圖索引可以與AND或OR操作符結合使用。點陣圖索引使用點陣圖作為鍵值，對於表中的每一資料行點陣圖包含了TRUE（1）、FALSE（0）、或NULL值。點陣圖索引的點陣圖存放在B-Tree結構的頁節點中。B-Tree結構使查詢點陣圖非常方便和快速。另外，點陣圖以一種壓縮格式存放，因此佔用的磁碟空間比B-Tree索引要小得多。
如果搜尋where gender=’Male’,要統計性別是”Male”的列行數的話，Oracle很快就能從點陣圖中找到共3行即第1，9，10行是符合條件的；如果要搜尋where gender=’Male’ or gender=’Female’的列的行數的話，也很容易從點陣圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜尋表的值的話，那麼Oracle會用內部的轉換函式將點陣圖中的相關資訊轉換成rowid來訪問資料塊。
2.5 函式索引
基於函式的索引也是8i以來的新產物，它有索引計算列的能力，它易於使用並且提供計算好的值，在不修改應用程式的邏輯上提高了查詢效能。使用基於函式的索引有幾個先決條件：
（1）必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）許可權。
（2）必須使用基於成本的優化器，基於規則的優化器將被忽略。
（3）必須設定以下兩個系統引數：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通過alter system set,alter session set在系統級或執行緒級設定，也可以通過在init.ora新增實現。這裡舉一個基於函式的索引的例子：
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已建立。
SQL> insert into testindex values('a',2);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(優化器選擇了全表掃描)
--------------------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(使用了ind_fun索引)
3 各種索引的建立方法
（1）*Tree索引。
Create index indexname on tablename(columnname[columnname...])
（2）反向索引。
Create index indexname on tablename(columnname[columnname...]) reverse
（3）降序索引。
Create index indexname on tablename(columnname DESC[columnname...])
（4）點陣圖索引。
Create BITMAP index indexname on tablename(columnname[columnname...])
（5）函式索引。
Create index indexname on tablename(functionname(columnname))
注意：建立索引後分析要索引才能起作用。
analyze index indexname compute statistics;
4 各種索引使用場合及建議（1）B*Tree索引。
常規索引，多用於oltp系統，快速定位行，應建立於高cardinality列（即列的唯一值除以行數為一個很大的值，存在很少的相同值）。
（2）反向索引。
B*Tree的衍生產物，應用於特殊場合，在ops環境加序列增加的列上建立，不適合做區域掃描。
（3）降序索引。
B*Tree的衍生產物，應用於有降序排列的搜尋語句中，索引中儲存了降序排列的索引碼，提供了快速的降序搜尋。
（4）點陣圖索引。
點陣圖方式管理的索引，適用於OLAP（線上分析）和DSS（決策處理）系統，應建立於低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。
（5）函式索引。
B*Tree的衍生產物，應用於查詢語句條件列上包含函式的情況，索引中儲存了經過函式計算的索引碼值。可以在不修改應用程式的基礎上能提高查詢效率。
5 附表（索引什麼時候不工作）
首先要宣告兩個知識點：
（1）RBO&CBO。
Oracle有兩種執行優化器，一種是RBO(Rule Based Optimizer)基於規則的優化器，這種優化器是基於sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基於規則的優化器，這種優化器是Oracle根據統計分析資訊來選擇執行路徑，如果表和索引沒有進行分析，Oracle將會使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.
（2）AUTOTRACE。
要看索引是否被使用我們要藉助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎麼執行sql的，這是一個非常好的輔助工具，在sql調優裡廣泛被運用。我們來看一下怎麼運用AUTOTRACE：
① 由於AUTOTRACE自動為使用者指定了Execution Plan,因此該使用者使用AUTOTRACE前必須已經建立了PLAN_TABLE。如果沒有的話，請執行utlxplan.sql指令碼（它在$ORACLE_HOME/rdbms/admin目錄中）。
② AUTOTRACE可以通過執行plustrce.sql指令碼(它在$ORACLE_HOME/sqlplus/admin目錄中)來設定，用sys使用者登陸然後執行plustrce.sql後會建立一個PLUSTRACE角色，然後給相關使用者授予PLUSTRACE角色，然後這些使用者就可以使用AUTOTRACE功能了。
③ AUTOTRACE的預設使用方法是set autotrace on，但是這方法不總是適合各種場合，特別當返回行數很多的時候。Set autotrace traceonly提供了只檢視統計資訊而不查詢資料的功能。
SQL> set autotrace on
SQL> select * from test;
A
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
SQL> set autotrace traceonly
SQL> select * from test.test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
Hints是Oracle提供的一個輔助用法，按字面理解就是‘提示’的意思，確實它起得作用也是提示優化器按它所提供的關鍵字來選擇執行路徑，特別適用於sql調整的時候。使用方法如下：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
具體可參考Oracle SQL Reference。有了前面這些知識點，接下來讓我們來看一下什麼時候索引是不起作用的。以下列出幾種情況。
索引常見操作 改變索引：
SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);
索引建立後，感覺不合理，也可以對其引數進行修改。詳情檢視相關文件
調整索引的空間：
新增加空間
SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');
釋放空間
SQL> alter index oraers_id_idx deallocate unused;
索引在使用的過程中可能會出現空間不足或空間浪費的情況，這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關於空間的新增oracle可以自動幫助，如果瞭解資料庫的情況下手動增加可以提高效能。
重新建立索引：
所引是由oracle自動完成，當我們對資料庫頻繁的操作時，索引也會跟著進行修改，當我們在資料庫中刪除一條記錄時，對應的索引中並沒有把相應的索引只是做一個刪除標記，但它依然佔據著空間。除非一個塊中所有的標記全被刪除的時，整個塊的空間才會被釋放。這樣時間久了，索引的效能就會下降。這個時候可以重新建立一個乾淨的索引來提高效率。
SQL> alter index orders_region_id_idx rebuild tablespace index02;
通過上面的命令就可以重現建立一個索引，oracle重建立索引的過程：
1、鎖表，鎖表之後其他人就不能對錶做任何操作。
2、建立新的（乾淨的）臨時索引。
3、把老的索引刪除掉
4、把新的索引重新命名為老索引的名字
5、對錶進行解鎖。
移動所引：
其實，我們移動索引到其它表空間也同樣使用上面的命令，在指定表空間時指定不同的表空間。新的索引建立在別位置，把老的幹掉，就相當於移動了。
SQL> alter index orders_region_id_idx rebuild tablespace index03;
線上重新建立索引：
上面介紹，在建立索引的時候，表是被鎖定，不能被使用。對於一個大表，重新建立索引所需要的時間較長，為了滿足使用者對錶操作的需求，就產生的這種線上重新建立索引。
SQL> alter index orders_id_idx rebuild online;
建立過程：
1、鎖住表
2、建立立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中資料；on-gong DML也就是使用者所做的一些增刪改的操作。
3、對錶進行解鎖
4、從老的索引建立一個新的索引。
5、IOT表裡存放的是on-going DML資訊，IOT表的內容與新建立的索引合併。
6、鎖住表
7、再次將IOT表的內容更新到新索引中，把老的索引幹掉。
8、把新的索引重新命名為老索引的名字
9、對錶進行解鎖
整合索引碎片：
如上圖，在很多索引中有剩餘的空間，可以通過一個命令把剩餘空間整合到一起。
SQL> alter index orders_id_idx coalesce;
刪除索引：
SQL> drop index hr.departments_name_idx;
分析索引 檢查所引的有效果，前面介紹，索引用的時間久了會產生大量的碎片、垃圾資訊與浪費的剩餘空間了。可以通過重新建立索引來提高所引的效能。
可以通過一條命令來完成分析索引，分析的結果會存放在在index_stats表中。
檢視存放分析資料的表：
SQL> select count(*) from index_stats;
COUNT(*)
----------
0
執行分析索引命令：
SQL> analyze index my_bit_idx validate structure;
Index analyzed.
再次檢視 index_stats 已經有了一條資料
SQL> select count(*) from index_stats;
COUNT(*)
----------
1
把資料查詢出來：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
2 MY_BIT_IDX 1000 3 100
分析資料分析：
（HEIGHT）這個所引高度是2 ，（NAME）索引名為MY_BIT_IDX ，（LF_ROWS）所引表有1000行資料，（LF_BLKS）佔用3個塊，（DEL_LF_ROWS）刪除100條記錄。
這裡也驗證了前面所說的一個問題，刪除的100條資料只是標記為刪除，因為總的資料條數依然為1000條，佔用3個塊，那麼每個塊大於333條記錄，只有刪除的資料大於333條記錄，這時一個塊被清空，總的資料條數才會減少。
索引
索引是關聯式資料庫中用於存放每一條記錄的一種物件，主要目的是加快資料的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與資料庫結構一道考慮。應用系統的效能直接與索引的合理直接有關。下面給出建立索引的方法和要點。
§3.5.1 建立索引
1. CREATE INDEX命令語法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中：
schema ORACLE模式，預設即為當前帳戶
index 索引名
table 建立索引的基表名
column 基表中的列名，一個索引最多有16列，long列、long raw
列不能建索引列
DESC、ASC 預設為ASC即升序排序
CLUSTER 指定一個聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事務入口數
Tablespace 表空間名
STORAGE 儲存引數，同create table 中的storage.
PCTFREE 索引資料塊空閒空間的百分比(不能指定pctused)
NOSORT 不（能）排序（儲存時就已按升序，所以指出不再排序）
2.建立索引的目的：
建立索引的目的是：
l 提高對錶的查詢速度；
l 對錶有關列的取值進行檢查。
但是，對錶進行insert,update,delete處理時，由於要表的存放位置記錄到索引項中而會降低一些速度。
注意：一個基表不能建太多的索引；
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高30%左右。
Create index ename_in on emp (ename,sal);
例1：商場的商品庫表結構如下，我們為該表的商品程式碼建立一唯一索引，使得在前臺POS收款時提高查詢速度。
Create table good(good_id number(8) not null,/* 商品條碼 */
Good_desc varchar2(40), /* 商品描述 */
Unit_cost number(10,2) /* 單價 */
Good_unit varchar2(6), /* 單位 */
Unit_pric number(10,2) /* 零售價 */
)；
注：提高查詢速度的方法還有在表上建立主鍵，主鍵與唯一索引的差別
在於唯一索引可以空，主鍵為非空，比如：
Create table good(good_id number(8) primary key,
Good_desc Varchar2(40),
Unit_cost number(10,2),
Good_unit char(6),
Unit_pric number(10,2)
);
§3.5.2 修改索引
對於較早的Oracle版本，修改索引的主要任務是修改已存在索引的儲存引數適應增長的需要或者重新建立索引。而Oracle8I及以後的版本，可以對無用的空間進行合併。這些的工作主要是由管理員來完成。
簡要語法結構如下,更詳細的語法圖見電子文件《Oracle8i Reference 》 中的 Alter index.
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]
其中：
REBUILD 是 根據原來的索引結構重新建立索引，實際是刪除原來的索引後再重新建立。
提示：DBA經常用 REBUILD 來重建索引可以減少硬碟碎片和提高應用系統的效能。
例：
alter index pk_detno rebuild storage(initial 1m next 512k);
ALTER INDEX emp_ix REBUILD REVERSE;
Oracle8i 的新功能可以對索引的無用空間進行合併，它由下面命令完成：
ALTER INDEX . . . COALESCE;
例如：
ALTER INDEX ename_idx COALESCE;
§3.5.3 刪除索引
當不需要時可以將索引刪除以釋放出硬碟空間。命令如下：
DROP INDEX [schema.]indexname
例如：
sql> drop index pk_dept;
注：當表結構被刪除時，有其相關的所有索引也隨之被刪除。
§3.6 新索引型別
Oracle8i為了效能優化而提供新的建立新型別的索引。這些新索引在下面介紹：
§3.6.1 基於函式的索引
基於函式的索引就是儲存預先計算好的函式或表示式值的索引。這些表示式可以是算術運算表示式、SQL或PL/SQL函式、C呼叫等。值得注意的是，一般使用者要建立函式索引，必須具有GLOBAL QUERY REWRITE和CREATE ANY INDEX許可權。否則不能建立函式索引，看下面例子：
例1：為EMP表的ename 列建立大寫轉換函式的索引idx ：
CREATE INDEX idx ON emp ( UPPER(ename));
這樣就可以在查詢語句來使用：
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;
例2：為emp 的工資和獎金之和建立索引：
1) 檢視emp 的表結構：
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2)沒有授權就建立函式索引的提示：
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
create index sal_comm on emp ( (sal+comm)*12, sal,comm)
*
ERROR at line 1:
ORA-01031: insufficient privileges
3) 連線到DBA帳戶並授權：
SQL> connect sys/sys@ora816
Connected.
SQL> grant GLOBAL QUERY REWRITE to scott;
Grant succeeded.
SQL> grant CREATE ANY INDEX to scott;
Grant succeeded.
4）在連線到scott帳戶，建立基於函式的索引：
SQL> connect scott/tiger@ora816
Connected.
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
Index created.
1)在查詢中使用函式索引：
SQL> select ename,sal,comm from emp where (sal+comm)*12 >5000;
ENAME SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
趙元傑 1234.5 54321
§3.6.2 反向鍵索引
反向鍵索引通過反向鍵保持索引的所有葉子鍵上的插入分佈。有時，可用反向鍵索引來避免不平衡的索引。對於反向鍵索引可以進行下面操作：
l 通過在ALTER INDEX命令後加REBUILD NOREVERSE或REBUILD REVERSE子句來使索引邊為反向鍵索引或普通索引；
l 採用範圍掃描的查詢不能使用反向鍵索引；
l 點陣圖索引不能反向；
l 索引編排表不能反向。
例1：建立一個反向鍵索引：
CREATE INDEX i ON t (a,b,c) REVERSE;
例2：使一個索引變為反向鍵索引：
ALTER INDEX i REBUILD NOREVERSE;
§3.6.3 索引組織表
與普通的索引不一樣，索引組織表（Index_Organized Table）是根據表來儲存資料，即將索引和表儲存在一起。這樣的索引結構表（Index_organized table—IOT）的特點是：對錶資料的改變，如插入一新行、刪除某行都引起索引的更新。
索引組織表就象帶一個或多個列所有的普通表一樣，但索引組織表在B-樹索引結構的葉節點上儲存行資料。通過在索引結構中儲存資料，索引組織表減少了總的儲存量，此外，索引組織表也改善訪問效能。
由於表中的行與B_樹索引存放在一起，每個行都沒有ROWID，而是用主鍵來標識。但是Oracle會“猜”這些行的位置併為每個行分配邏輯的ROWID。此外，你可以為這樣的表建立第二個索引。
建立索引結構表也是用CREATE TABLE 命令加ORGANIZATION INDEX關鍵字來實現。但是，這樣的表在建立完後，你還必須為該表建立一個主鍵。
例子：
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引組織表有些限制：
l 不能使用唯一約束；
l 必須具有一個主鍵；
l 不能建立簇；
l 不能包含LONG型別列；
l 不支援分佈和複製。
提示：如果建立了索引組織表，則會在DBA_TABLES中的IOT_TYPE和IOT_NAME列上記錄有索引組織表的資訊。
例1．修改索引結構表 docindex 的索引段的INITRANS引數：
ALTER TABLE docindex INITRANS 4;
例2．下面語句加一個的溢位資料段到索引組織表 docindex中：
ALTER TABLE docindex ADD OVERFLOW;
例3．下面語句為索引組織表 docindex的溢位資料段修改INITRANS引數：
ALTER TABLE docindex OVERFLOW INITRANS 4;
============================================================================================================
適當的使用索引可以提高資料檢索速度，可以給經常需要進行查詢的欄位建立索引
oracle的索引分為5種:唯一索引，組合索引，反向鍵索引，點陣圖索引，基於函式的索引
建立索引的標準語法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空間名;
建立反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空間名;
檢視文章
oracle 檢視索引類別以及檢視索引欄位被引用的欄位方法2008年01月04日 星期五 13:20檢視索引個數和類別
select * from user_indexes where table='表名' ;
檢視索引被索引的欄位
SQL>select * from user_ind_columns where index_name=upper('&index_name');
PS:
檢視某表的約束條件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
檢視檢視的名稱
SQL>select view_name from user_views;
Oracle索引index建立刪除
在查中文字典的時候，我們之所以能夠很快的查到所要查詢的字是因為字典建立了索引。同樣，為了高效的獲取資料，在資料量比較大的時候，我們需要給表中的某一列建立索引。
建立索引的作用是明顯的，主要有：
1、快速存取資料
2、改善資料庫效能，保證列值的唯一性
3、在使用order
按照儲存方法可以將索引分為B*樹索引和點陣圖索引。
B*樹索引：顧名思義其儲存型別就是一棵樹，有分支和葉，分支相當於書的大目錄，葉則相當於具體的書頁。Oracle用B*樹機制儲存索引條目，可以保證用最短路徑訪問鍵值。預設情況下大多使用B*樹索引。
點陣圖索引：點陣圖索引儲存主要用於節省空間，減少Oracle對資料塊的訪問，它採用點陣圖偏移方式來與表的行id號對應，採用點陣圖索引一般是重複值太多的表欄位。點陣圖索引在實際密集型OLTP(資料事務處理)中用得比較少，因為OLTP會對錶進行大量的刪除、修改、新增操作，Oracle每次進行這些操作的時候都會對要操作的資料塊加鎖，以防止多人操作容易產生的資料塊鎖等待甚至死鎖現象。而在OLAP(資料分析處理)中應用點陣圖有優勢，因為OLAP中大部分是對資料庫的查詢操作，而且一般採用資料倉儲技術，所以大量資料採用點陣圖索引節省空間比較明顯。
建立索引：
語法格式：
Sql程式碼
create [unique | bitmap] index [schema.]indexName
on [schema.]tableName(columnName[ASC | DESC],...n)
[tablespace tablespaceName] [nosort] [reverse]
其中，unique指定索引所基於的列值必須唯一。預設的索引是非唯一索引。Oracle建議不要在表上顯示的定義unique索引。BITMAP指定建立點陣圖索引。
刪除索引：
Sql程式碼
drop index indexName;
Oracle中的索引詳解
本頁包含5張圖片，預設未載入，顯示所有圖片
crazy
一、 ROWID的概念
儲存了row在資料檔案中的具體位置：64位 編碼的資料，A-Z, a-z, 0-9, +, 和 /，
row在資料塊中的儲存方式
SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;
比 如：OOOOOOFFFBBBBBBRRR
OOOOOO：data object number, 對應dba_objects.data_object_id
FFF：file#, 對應v$datafile.file#
BBBBBB：block#
RRR：row#
Dbms_rowid包
SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;
具 體到特定的物理檔案
二、 索引的概念
1、 類似書的目錄結構
2、 Oracle 的“索引”物件，與表關聯的可選物件，提高SQL查詢語句的速度
3、 索引直接指向包含所查詢值的行的位置，減少磁碟I/O
4、 與所索引的表是相互獨立的物理結構
5、 Oracle 自動使用並維護索引，插入、刪除、更新表後，自動更新索引
6、 語法：CREATE INDEX index ON table (column[, column]...);
7、 B-tree結構(非bitmap)：
[一]瞭解索引的工作原理：
表：emp
點選載入圖片
目標：查詢Frank的工資salary
建立索 引：create index emp_name_idx on emp(name);
點選載入圖片
點選載入圖片
[試驗]測試索引的作用：
1. 執行/rdbms/admin/utlxplan 指令碼
2. 建立測試表
create table t as select * from dba_objects;
insert into t select * from t;
create table indextable
as select rownum id,owner,object_name,subobject_name,
object_id,data_object_id,object_type,created
from t;
3. set autotrace trace explain
4. set timing on
5. 分析表，可以得到cost
6. 查詢 object_name=’DBA_INDEXES’
7. 在object_name列上建立索引
8. 再查詢
[思考]索引的代價：
插入，更新
三、 唯一索引
1、 何時建立：當某列任意兩行的值都不相同
2、 當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時，唯一索引將被自動建立
3、 語法：CREATE UNIQUE INDEX index ON table (column);
4、 演示
四、 組合索引
1、 何時建立：當兩個或多個列經常一起出現在where條件中時，則在這些列上同時建立組合索引
2、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的最前面
3、 演示(組合列，單獨列)
五、 點陣圖索引
1、 何時建立：
列中有非常多的重複的值時候。例如某列儲存了 “性別”資訊。
Where 條件中包含了很多OR操作符。
較少的update操作，因為要相應的跟新所有的bitmap
2、 結構：點陣圖索引使用點陣圖作為鍵值，對於表中的每一資料行點陣圖包含了TRUE(1)、FALSE(0)、或NULL值。
3、 優點：點陣圖以一種壓縮格式存放，因此佔用的磁碟空間比標準索引要小得多
4、 語法：CREATE BITMAP INDEX index ON table (column[, column]...);
5、 掩飾：
create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');
分析，查詢，建立索引，查詢
六、 基於函式的索引
1、 何時建立：在WHERE條件語句中包含函式或者表示式時
2、 函式包括：算數表示式、PL/SQL函式、程式包函式、SQL函式、使用者自定義函式。
3、 語法：CREATE INDEX index ON table (FUNCTION(column));
4、 演示
必須要分析表，並且 query_rewrite_enabled=TRUE
或者使用提示/*+ INDEX(ic_index)*/
七、 反向鍵索引
目的：比如索引值是一個自動增長的列：
點選載入圖片
多個使用者對集中在少數塊上的索引行進行修改，容易引起資源的爭用，比如對資料塊的等待。此時建立反向索 引。
效能問題：
語法：
重建為標準索引：反之不行
八、 鍵壓縮索引
比如表landscp的資料如下：
site feature job
Britten Park, Rose Bed 1, Prune
Britten Park, Rose Bed 1, Mulch
Britten Park, Rose Bed 1,Spray
Britten Park, Shrub Bed 1, Mulch
Britten Park, Shrub Bed 1, Weed
Britten Park, Shrub Bed 1, Hoe
……
查詢時，以上3列均在where條件中同時出現，所以建立基於以上3列的組合索引。但是發現重複值很多，所以考慮壓縮特性。
Create index zip_idx
on landscp(site, feature, job)
compress 2;
將索引項分成字首(prefix)和字尾(postfix)兩部分。前兩項被放置到字首部分。
Prefix 0: Britten Park, Rose Bed 1
Prefix 1: Britten Park, Shrub Bed 1
實際所以的結構為：
0 Prune
0 Mulch
0 Spray
1 Mulch
1 Weed
1 Hoe
特點：組合索引的字首部分具 有非選擇性時，考慮使用壓縮。減少I/O,增加效能。
九、 索引組織表(IOT)
將表中的資料按照索 引的結構儲存在索引中，提高查詢速度。
犧牲插入更新的效能，換取查詢 效能。通常用於資料倉儲，提供大量的查詢，極少的插入修改工作。
必須指定主鍵。插入資料時，會根據主鍵列進行B樹索引排序，寫入磁碟。
十、 分割槽索引
簇:
A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
oracle中最常用的索引就兩種：B樹索引和點陣圖索引，這裡就來簡單說下這兩種索引的使用。
B-樹索引在Oracle中是一個通用的索引，在建立索引時它就是預設的索引型別。最多可以包括32列。
點陣圖索引Oracle為每個唯一鍵建立一個點陣圖，然後把與鍵值所關聯的ROWID儲存為點陣圖。最多可以包括30列。
一般情況下，大多數使用者都只建立TYPE為NORMAL的B-樹索引，所以對於較低基數的列我們都是不建立索引的，因為B-樹索引對查詢速度提升不一定會有改善，甚至會增加Insert、Update、Delete命令所消耗的時間。
點陣圖索引在載入表（插入操作）時通常要比B-樹索引做得好。通常，點陣圖索引要比一個低基數（很少不同值）上的B-樹索引要快3~4倍，但如果新增的值佔插入行的70%以上時，B-樹索引通常會更快一些。當每條記錄都增加一個新值時，B-樹索引要比點陣圖索引快3倍。
建議不要在一些聯機事務處理（OLTP）應用程式中使用點陣圖索引。B-樹索引的索引值中包含ROWID，這樣Oracle就可以在行級別上鎖定索引。點陣圖索引被儲存為壓縮的索引值，其中包含了一個範圍內的ROWID，因此ORACLE必須針對一個給定值鎖定所有範圍內的ROWID。這種鎖定可能自阿某些DML語句中造成死鎖。SELECT語句不會受到這種鎖定問題的影響。
點陣圖索引有很多限制：
1、 基於規則的優化器不會考慮點陣圖索引
2、 當執行ATLER TABLE語句，並修改包含有點陣圖索引的列時，會使點陣圖索引實效
3、 點陣圖索引在索引塊中儲存了索引鍵的值；然而，他們並不能使用者任何型別的完整性檢查
4、 點陣圖索引不能被申明為唯一索引
以上是援引的一些簡單概念，下面是我實際工作中總結出來的：
我要做一個查詢，涉及兩個表t_sym_dict,t_sym_operlog，表結構分別如下：
X
其中t_sym_operlog的索引如下：
上面基數比較小的三列建立了點陣圖索引
t_sym_dict的索引如下：
查詢語句如下：
select (select c.dict_name
from t_sym_dict c
where c.dict_typeid = 'SYM_CITYINFO'
and c.dict_id = t.memo) 分公司,
t.staff_id 工號
from t_sym_operlog t
where t.operlog_subtype = '103'
and t.obj_type = 'CUSTLINKINFO'
and t.memo = '200'
-- and t.extsys_code = ''
-- and t.staff_id = ''
and t.oper_date >= to_date('20110501000000', 'yyyymmddhh24miss')
and t.oper_date <= to_date('20110530000000', 'yyyymmddhh24miss')
-- order byt.memo, t.oper_date
然後就出現瞭如下奇怪的現象（索引的建立沒有問題）
1、索引使用正常
2、下面的看不到oper_date索引的使用
3、下面的看不到t_sym_operlog表的索引使用
嘗試的解決的辦法：
對資料表做採集，就是analysis
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'CSID',tabname => 't_sym_operlog_back');
end;
猜測的導致原因：
當你運用SQL語言，向資料庫釋出一條查詢語句時，ORACLE將伴隨產生一個“執行計劃”，也就是該語句將通過何種資料搜尋方案執行，是通過全表掃描、還是通過索引搜尋等其它方式。搜尋方案的選用與ORACLE的優化器息息相關。
SQL語句的執行步驟
一條SQL語句的處理過程要經過以下幾個步驟。
1 語法分析 分析語句的語法是否符合規範，衡量語句中各表示式的意義。
2 語義分析 檢查語句中涉及的所有資料庫物件是否存在，且使用者有相應的許可權。
3 檢視轉換 將涉及檢視的查詢語句轉換為相應的對基表查詢語句。
4 表示式轉換 將複雜的SQL表示式轉換為較簡單的等效連線表示式。
5 選擇優化器 不同的優化器一般產生不同的“執行計劃”
6 選擇連線方式 ORACLE有三種連線方式，對多表連線ORACLE可選擇適當的連線方式。
7 選擇連線順序 對多表連線ORACLE選擇哪一對錶先連線，選擇這兩表中哪個表做為源資料表。
8 選擇資料的搜尋路徑 根據以上條件選擇合適的資料搜尋路徑，如是選用全表搜尋還是利用索引或是其他的方式。
9 執行“執行計劃”
分析：
oracle優化器CBO存在的典型問題：
有時，表明明建有索引，但查詢過程顯然沒有用到相關的索引，導致查詢過程耗時漫長，佔用資源巨大，問題到底出在哪兒呢？按照以下順序查詢，基本上能發現原因所在。
查詢原因的步驟
首先，我們要確定資料庫執行在何種優化模式下，相應的引數是：optimizer_mode。可在svrmgrl中執行“show parameter optimizer_mode'來檢視。ORACLE V7以來預設的設定應是'choose'，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。如果該引數設為“rule”，則不論表是否分析過，一概選用RBO，除非在語句中用hint強制。
其次，檢查被索引的列或組合索引的首列是否出現在PL/SQL語句的WHERE子句中，這是“執行計劃”能用到相關索引的必要條件。
第三，看採用了哪種型別的連線方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在兩張表連線，且內表的目標列上建有索引時，只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引，最多隻能因索引的存在，避免資料排序過程。HJ由於須做HASH運算，索引的存在對資料查詢速度幾乎沒有影響。
第四，看連線順序是否允許使用相關索引。假設表emp的deptno列上有索引，表dept的列deptno上無索引，WHERE語句有emp.deptno=dept.deptno條件。在做NL連線時，emp做為外表，先被訪問，由於連線機制原因，外表的資料訪問方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。
第五，是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過，可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析，否則可能導致死鎖，或系統效能下降。
第六，索引列是否函式的引數。如是，索引在查詢時用不上。
第七，是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較，ORACLE會自動將字元型用to_number()函式進行轉換，從而導致第六種現象的發生。
第八，是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析，可用SQL語句“analyze table xxxx compute statistics for all indexes;'。ORACLE掌握了充分反映實際的統計資料，才有可能做出正確的選擇。
第九，索引列的選擇性不高。
我們假設典型情況，有表emp，共有一百萬行資料，但其中的emp.deptno列，資料只有4種不同的值，如10、20、30、40。雖然emp資料行有很多，ORACLE預設認定表中列的值是在所有資料行均勻分佈的，也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10，利用deptno列上的索引進行資料搜尋效率，往往不比全表掃描的高，ORACLE理所當然對索引“視而不見”，認為該索引的選擇性不高。
但我們考慮另一種情況，如果一百萬資料行實際不是在4種deptno值間平均分配，其中有99萬行對應著值10，5000行對應值20，3000行對應值30，2000行對應值40。在這種資料分佈圖案中對除值為10外的其它deptno值搜尋時，毫無疑問，如果索引能被應用，那麼效率會高出很多。我們可以採用對該索引列進行單獨分析，或用analyze語句對該列建立直方圖，對該列蒐集足夠的統計資料，使ORACLE在搜尋選擇性較高的值能用上索引。
第十，索引列值是否可為空（NULL）。如果索引列值可以是空值，在SQL語句中那些需要返回NULL值的操作，將不會用到索引，如COUNT（*），而是用全表掃描。這是因為索引中儲存值不能為全空。
第十一，看是否有用到並行查詢（PQO）。並行查詢將不會用到索引。
第十二，看PL/SQL語句中是否有用到bind變數。由於資料庫不知道bind變數具體是什麼值，在做非相等連線時，如“<”，“>”,“like”等。ORACLE將引用預設值，在某些情況下會對執行計劃造成影響。
如果從以上幾個方面都查不出原因的話，我們只好用採用在語句中加hint的方式強制ORACLE使用最優的“執行計劃”。
hint採用註釋的方式，有行註釋和段註釋兩種方式。
如我們想要用到A表的IND_COL1索引的話，可採用以下方式：
“SELECT * FROM A WHERE COL1 = XXX;'
注意，註釋符必須跟在SELECT之後，且註釋中的“+”要緊跟著註釋起始符“/*”或“--”，否則hint就被認為是一般註釋，對PL/SQL語句的執行不產生任何影響。
兩種有效的跟蹤除錯方法
ORACLE提供了兩種有效的工具來跟蹤除錯PL/SQL語句的執行計劃。
一種是EXPLAIN TABLE方式。使用者必須首先在自己的模式（SCHEMA）下，建立PLAN_TABLE表，執行計劃的每一步驟都將記錄在該表中，建表SQL指令碼為在${ORACLE_HOME}/rdbms/admin/下的utlxplan.sql。
開啟SQL*PLUS，輸入“SET AUTOTRACE ON”，然後執行待除錯的SQL語句。在給出查詢結果後，ORACLE將顯示相應的“執行計劃”，包括優化器型別、執行代價、連線方式、連線順序、資料搜尋路徑以及相應的連續讀、物理讀等資源代價。
如果我們不能確定需要跟蹤的具體SQL語句，比如某個應用使用一段時間後，響應速度忽然變慢。我們這時可以利用ORACLE提供的另一個有力工具TKPROF，對應用的執行過程全程跟蹤。
我們要先在系統檢視V$SESSION中，可根據USERID或MACHINE，查出相應的SID和SERIAL#。
以SYS或其他有執行DBMS_SYSTEM程式包的使用者連線資料庫，執行“EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION（SID，SERIAL#，TRUE）；”。
然後執行應用程式，這時在伺服器端，資料庫引數“USER_DUMP_DEST”指示的目錄下，會生成ora__xxxx.trc檔案，其中xxxx為被跟蹤應用的作業系統程式號。
應用程式執行完成後，用命令tkprof對該檔案進行分析。命令示例：“tkprof tracefile outputfile explain=useri
相關文章
- 索引@oracle索引技術索引Oracle
- Oracle 索引Oracle索引
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- 【Oracle索引】-索引基本概念Oracle索引
- Oracle索引——點陣圖索引Oracle索引
- oracle 索引分析及索引重建Oracle索引
- oracle索引操作Oracle索引
- oracle重建索引Oracle索引
- ORACLE索引概述Oracle索引
- Oracle索引原理Oracle索引
- oracle 索引 管理Oracle索引
- oracle的索引Oracle索引
- Oracle全域性索引和本地索引Oracle索引
- oracle索引詳解 分割槽索引Oracle索引
- oracle 索引使用及索引失效總結Oracle索引
- oracle重建索引（一）Oracle索引
- oracle重建索引（三）Oracle索引
- oracle重建索引（二）Oracle索引
- Oracle OCP(25)：索引Oracle索引
- Oracle虛擬索引Oracle索引
- Oracle：全文索引Oracle索引
- oracle index索引原理OracleIndex索引
- ORACLE 索引使用規Oracle索引
- ORACLE索引的管理Oracle索引
- oracle函式索引Oracle函式索引
- oracle 索引壓縮Oracle索引
- （轉）Oracle索引原理Oracle索引
- Oracle索引分割槽Oracle索引
- oracle手冊索引Oracle索引
- oracle索引分類Oracle索引
- Oracle索引失效-likeOracle索引
- Oracle 基礎--索引Oracle索引
- Oracle實驗(03)：number的使用Oracle
- oracle statspack 安裝詳解---03Oracle
- Oracle表與索引的分析及索引重建Oracle索引
- 【索引】Oracle之不可見索引和虛擬索引的比對索引Oracle
- [轉]Oracle分割槽索引--本地索引和全域性索引比較Oracle索引
- Oracle分割槽索引--本地索引和全域性索引比較（轉）Oracle索引