索引是由Oracle維護的可選結構，為數(shù)據(jù)提供快速的訪問。準確地判定在什么地方需要使用索引是困難的，使用索引有利于調節(jié)檢索速度。 當建立一個索引時，必須指定用于跟蹤的表名以及一個或多個表列。一旦建立了索引，在用戶表中建立、更改和刪除數(shù)據(jù)庫時， Oracle就自動地維護索引。創(chuàng)建索引時，下列準則將幫助用戶做出決定：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語句謂詞中多個表列被一起連續(xù)引用，則應該考慮將這些表列一起放在一個索引內， O r a c l e將維護單個表列的索引(建立在單一表列上)或復合索引(建立在多個表列上)。復合索引稱并置索引。　　1 主要害字的約束關系數(shù)據(jù)庫理論指出，在表中能唯一標識表的每個數(shù)據(jù)行的一個或多個表列是對象的主要害字。由于數(shù)據(jù)字典中定義的主要害字能確保表中數(shù)據(jù)行之間的唯一性，因此，在O r a c l e 8 i數(shù)據(jù)庫中建立表索引要害字有助于應用調節(jié)。另外，這也減輕了開發(fā)者為了實現(xiàn)唯一性檢查，而需要各自編程的要求。提示使用主要害字索引條目比不使用主要害字索引檢索得快。假設表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一旦碰到一個大于設定值的條目，索引的順序搜索可被終止;2 ORDER BY中用索引ORDER BY 子句只在兩種嚴格的條件下使用索引. ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序. ORDER BY中所有的列必須定義為非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列. 例如: 表DEPT包含以下列: DEPT_CODE PKNOT 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 3 避免改變索引列的類型. 當比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進行簡單的類型轉換. 假設 EMPNO是一個數(shù)值類型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 實際上,經(jīng)過ORACLE類型轉換, 語句轉化為: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 幸運的是,類型轉換沒有發(fā)生在索引列上,索引的用途沒有被改變. 現(xiàn)在,假設EMP_TYPE是一個字符類型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 這個語句被ORACLE轉換為: SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123 因為內部發(fā)生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現(xiàn)出來. 注重當字符和數(shù)值比較時, ORACLE會優(yōu)先轉換數(shù)值類型到字符類型. 4 需要當心的WHERE子句 某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子. 在下面的例子里, ‘!=' 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0; 下面的例子中, ‘'是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAMEACCOUNT_TYPE='AMEXA'; 使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX' AND ACCOUNT_TYPE=' A'; 下面的例子中, ‘+'是數(shù)學函數(shù). 就象其他數(shù)學函數(shù)那樣, 停用了索引. 不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 >5000; 使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT > 2000 ; 下面的例子中,相同的索引列不能互相比較,這將會啟用全表掃描. 不使用索引: 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,'%'); 假如一定要對使用函數(shù)的列啟用索引, ORACLE新的功能: 基于函數(shù)的索引(Function-Based Index) 也許是一個較好的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函數(shù)的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL'; /*將使用索引*/ 5 怎樣監(jiān)控無用的索引Oracle 9i以上，可以監(jiān)控索引的使用情況，假如一段時間內沒有使用的索引，一般就是無用的索引語法為：開始監(jiān)控：alter index index_name monitoring usage;檢查使用狀態(tài)：select * from v$object_usage;停止監(jiān)控：alter index index_name nomonitoring usage;當然，假如想監(jiān)控整個用戶下的索引，可以采用如下的腳本：set heading offset echo offset feedback offset pages 10000spool start_index_monitor.sqlSELECT 'alter index 'owner'.'index_name' monitoring usage;'FROM dba_indexesWHERE owner = USER; spool off set heading onset echo onset feedback on------------------------------------------------set heading offset echo offset feedback offset pages 10000spool stop_index_monitor.sqlSELECT 'alter index 'owner'.'index_name' nomonitoring usage;'FROM dba_indexesWHERE owner = USER; spool off set heading onset echo onset feedback on