索引是由Oracle維護的可選結構，為數據提供快速的訪問。准確地判斷在什麼地方需要使用索引是困難的，使用索引有利於調節檢索速度。 當建立一個索引時，必須指定用於跟蹤的表名以及一個或多個表列。一旦建立了索引，在用戶表中建立、更改和刪除數據庫時， Oracle就自動地維護索引。創建索引時，下列准則將幫助用戶做出決定：
1) 索引應該在SQL語句的"where"或"and"部分涉及的表列(也稱謂詞)被建立。假如personnel表的"firstname"表列作為查詢結果顯示，而不是作為謂詞部分，則不論其值是什麼，該表列不會被索引。
2) 用戶應該索引具有一定范圍的表列，索引時有一個大致的原則：如果表中列的值占該表中行的2 0 %以內，這個表列就可以作為候選索引表列。假設一個表有36 000行且表中一個表列的值平均分布(大約每12000行)，那麼該表列不適合於一個索引。然而，如果同一個表中的其他表列中列值的行在1 0 0 0～1 5 0 0之間(占3 %～4 % )，則該表列可用作索引。
3)如果在S Q L語句謂詞中多個表列被一起連續引用，則應該考慮將這些表列一起放在一個索引內， O r a c l e將維護單個表列的索引(建立在單一表列上)或復合索引(建立在多個表列上)。復合索引稱並置索引。
一、主關鍵字的約束
關系數據庫理論指出，在表中能唯一標識表的每個數據行的一個或多個表列是對象的主關鍵字。由於數據字典中定義的主關鍵字能確保表中數據行之間的唯一性，因此，在O r a c l e 8 i數據庫中建立表索引關鍵字有助於應用調節。另外，這也減輕了開發者為了實現唯一性檢查，而需要各自編程的要求。
提示使用主關鍵字索引條目比不使用主關鍵字索引檢索得快。
假設表p e r s o n把它的i d表列作為主關鍵字，用下列代碼設置約束：
alter table person add constraint person_pk primary key (id) using index storage (initial 1m next 1m pctincrease 0) tablespace prd_indexes ;
處理下列S Q L語句時：select last_name ,first_name ,salary from person where id = 289 ;
在查找一個已確定的“ i d”表列值時， O r a c l e將直接找到p e r s o n _ p k。如果其未找到正確的索引條目，O r a c l e知道該行不存在。主關鍵字索引具有下列兩個獨特之處：
1.1因為索引是唯一的， 所以O r a c l e知道只有一個條目具有設定值。如果查找到了所期望的條目，則立即終止查找。
1.2一旦遇到一個大於設定值的條目，索引的順序搜索可被終止;
二、ORDER BY中用索引
ORDER BY 子句只在兩種嚴格的條件下使用索引.
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列.
例如:
表DEPT包含以下列:
dept_code pk not null
dept_desc not null
dept_type null
非唯一性的索引(dept_type) ,
低效: (索引不被使用)
select dept_code from dept order by dept_type
explain plan: sort order by table access full
高效: (使用索引)
select dept_code from dept where dept_type > 0
explain plan:
table access by rowid on emp
index range scan on dept_idx
三、避免改變索引列的類型
當比較不同數據類型的數據時, oracle自動對列進行簡單的類型轉換.
假設 empno是一個數值類型的索引列:
select …from emp where empno = '123'
實際上,經過ORACLE類型轉換, 語句轉化為: select … from emp where empno = to_number('123')
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設emp_type是一個字符類型的索引列: select … from emp where emp_type = 123
這個語句被oracle轉換為: select … from emp where to_number(emp_type)=123
因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免oracle對你的sql進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字符和數值比較時, oracle會優先轉換數值類型到字符類型.
四、需要當心的where子句
某些select 語句中的where子句不使用索引. 這裡有一些例子:
1、IS NULL 與 IS NOT NULL
不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。
任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。
2、'!=' 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中
不使用索引: select * from employee where salary<>3000;
使用索引: select account_name from transaction where amount >0;
使用索引: select * from employee where salary<3000 or salary>3000;
3、聯接列,'||'是字符連接函數. 就象其他函數那樣, 停用了索引
不使用索引: select account_name,amount from transaction where account_name||account_type='AMEXA';
使用索引: select account_name,amount from transaction where account_name = 'AMEX' and account_type=' A';
4、'+'是數學函數. 就象其他數學函數那樣, 停用了索引
不使用索引: select account_name, amount from transaction where amount + 3000 >5000;
使用索引: select account_name, amount from transaction where amount > 2000 ;
5、相同的索引列不能互相比較,這將會啟用全表掃描
不使用索引: select account_name, amount from transaction where account_name = nvl(:acc_name,account_name);
使用索引: select account_name, amount from transaction where account_name like nvl(:acc_name,'%');
6、帶通配符（%）的like語句
不使用索引: select * from employee where last_name like '%cliton%';
使用索引: select * from employee where last_name like 'c%'
7、IN和EXISTS
不使用索引: ... where column in(select * from ... where ...);
使用索引: ... where exists (select 'X' from ...where ...);
同時應盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高。
如果一定要對使用函數的列啟用索引:
1、oracle新的功能: 基於函數的索引(function-based index) 也許是一個較好的方案:
create index emp_i on emp (upper(ename)); /*建立基於函數的索引*/
select * from emp where upper(ename) = 'BLACKSNAIL'; /*將使用索引*/
2、MS SQL Server顯示申明指定索引：
SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男'，'女')
五、怎樣監控無用的索引
Oracle 9i以上，可以監控索引的使用情況，如果一段時間內沒有使用的索引，一般就是無用的索引
語法為：
開始監控：alter index index_name monitoring usage;
檢查使用狀態：select * from v$object_usage;
停止監控：alter index index_name nomonitoring usage;
當然，如果想監控整個用戶下的索引，可以采用如下的腳本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' FROM dba_indexes WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
------------------------------------------------------------------------------------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;' FROM dba_indexes WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
原文：http://czllfy.iteye.com/blog/510282
