小議在Oracle中索引的使用
2024-08-29 13:49:44
供稿：網友
索引是由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將維護單個表列的索引(建立在單一表列上)或復合索引(建立在多個表列上)。復合索引稱并置索引。 1 主要害字的約束 關系數據庫理論指出，在表中能唯一標識表的每個數據行的一個或多個表列是對象的主要害字。由于數據字典中定義的主要害字能確保表中數據行之間的唯一性，因此，在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一旦碰到一個大于設定值的條目，索引的順序搜索可被終止; 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 3 避免改變索引列的類型. 當比較不同數據類型的數據時, 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會優先轉換數值類型到字符類型. 4 需要當心的WHERE子句 某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子. 在下面的例子里, ‘!=' 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0; 下面的例子中, ‘'是字符連接函數. 就象其他函數那樣, 停用了索引. 不使用索引: 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'; 下面的例子中, ‘+'是數學函數. 就象其他數學函數那樣, 停用了索引. 不使用索引: 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,'%'); 假如一定要對使用函數的列啟用索引, ORACLE新的功能: 基于函數的索引(Function-Based Index) 也許是一個較好的 方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函數的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL'; /*將使用索引*/ 5 怎樣監控無用的索引 Oracle 9i以上，可以監控索引的使用情況，假如一段時間內沒有使用的索引，一般就是無用的索引 語法為： 開始監控：alter index index_name monitoring usage; 檢查使用狀態：select * from v$object_usage; 停止監控：alter index index_name nomonitoring usage; 當然，假如想監控整個用戶下的索引，可以采用如下的腳本：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
