數據庫索引的使用方法

現定在的信息社會，大量的數據信息被使用，在數據庫中運用索引可快速訪問數據庫表中的特定信息，所以了解數據庫索引的使用變得尤為重要。

索引是由Oracle維護的可選結構，為數據提供快速的訪問。

准確地判斷在什麼地方需要使用索引是困難的，使用索引有利於調節檢索速度。

當建立一個索引時，必須指定用於跟蹤的表名以及一個或多個表列。一旦建立了索引，在用戶表中建立、更改和刪除數據庫時，Oracle就自動地維護索引。創建索引時，下列准則將幫助用戶做出決定：

1)索引應該在SQL語句的"where"或"and"部分涉及的表列(也稱謂詞)被建立。假如personnel表的"firstname"表列作為查詢結果顯示，而不是作為謂詞部分，則不論其值是什麼，該表列不會被索引。

2)用戶應該索引具有一定范圍的表列，索引時有一個大致的原則：如果表中列的值占該表中行的20%以內，這個表列就可以作為候選索引表列。假設一個表有36 000行且表中一個表列的值平均分布(大約每12000行)，那麼該表列不適合於一個索引。然而，如果同一個表中的其他表列中列值的行在1000～1500之間(占3%～4% )，則該表列可用作索引。

3)如果在SQL語句謂詞中多個表列被一起連續引用，則應該考慮將這些表列一起放在一個索引內，Oracle將維護單個表列的索引(建立在單一表列上)或復合索引(建立在多個表列上)。復合索引稱並置索引。

1. 主關鍵字的約束

關系數據庫理論指出，在表中能唯一標識表的每個數據行的一個或多個表列是對象的主關鍵字。由於數據字典中定義的主關鍵字能確保表中數據行之間的唯一性，因此，在Oracle 9i數據庫中建立表索引關鍵字有助於應用調節。另外，這也減輕了開發者為了實現唯一性檢查，而需要各自編程的要求。

提示使用主關鍵字索引條目比不使用主關鍵字索引檢索得快。

假設表person把它的id表列作為主關鍵字，用下列代碼設置約束：

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 ;

在查找一個已確定的"id"表列值時，Oracle將直接找到person_pk。如果其未找到正確的索引條目，Oracle知道該行不存在。主關鍵字索引具有下列兩個獨特之處：

1)因為索引是唯一的，所以Oracle知道只有一個條目具有設定值。如果查找到了所期望的條目，則立即終止查找。

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

高效: (使用索引)

SELECT DEPT_CODE

FROM DEPT

WHERE DEPT_TYPE > 0

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_NAME||ACCOUNT_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'; /*將使用索引*/

另：is null和is not null同樣會限制索引的使用，因為null值並沒有被定義。[sainth注]

5. 怎樣監控無用的索引

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

以上就是精品為您准備的關於的數據庫索引的使用信息，希望對您的生活工作有幫助，祝您生活愉快。