oracle重建索引的實現

Oracle重建索引是我們經常會用到的操作，下面就將為您詳細介紹Oracle重建索引的實現方法，希望對您能夠有所幫助。

Oracle重建索引有多種方式，如drop and re-create、rebuild、rebuild online等。下面簡單比較這幾種方式異同以及優缺點：

首先建立測試表及數據：

SQL> CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2;  
 
Table created  
 
SQL> ALTER TABLE TEST MODIFY C1 NOT NULL;  
 
Table altered  
 
SQL> SELECT COUNT(1) FROM TEST;  
 
COUNT(1)  
----------  
16000000  
 

一、drop and re-create和rebuild

首先看看正常建立索引時，對表的加鎖情況。

suk@Oracle9I> @show_sid  
 
SID  
----------  
14  
 
suk@Oracle9I> CREATE INDEX IDX_TEST_C1 ON TEST(C1);  

索引已創建。

SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;  
 
OBJECT_NAME LMODE  
------------------------------ ----------  
OBJ$ 3  
TEST 4  

可見，普通情況下建立索引時，Oracle會對基表加share鎖，由於share鎖和 row-X是不兼容的，也就是說，在建立索引期間，無法對基表進行DML操作。

對於刪除重建索引的方法就不介紹了，它與上面的描述是一樣的，下面我們看看用rebuild的方式建立索引有什麼特別。

suk@Oracle9I> ALTER INDEX IDX_TEST_C1 REBUILD; 

索引已更改。

另開一個會話，查詢此時test的加鎖情況：

SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;  
 
OBJECT_NAME LMODE  
------------------------------ ----------  
TEST 4  

可見，rebuild的方式對基表的加鎖方式與CREATE時是一樣的。

另開一個會話，在索引正在rebuild時，執行如下SQL：

suk@Oracle9I> SET AUTOTRACE TRACE  
suk@Oracle9I> SELECT /*+ INDEX(TEST) */ COUNT(1) FROM TEST WHERE ROWNUM<10; 

執行計劃

----------------------------------------------------------  
SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)  
0 SORT (AGGREGATE)  
1 COUNT (STOPKEY)  
2 INDEX (FULL SCAN) OF 'IDX_TEST_C1' (NON-UNIQUE) (Cost=  
26 Card=1986621) 

可以看到oracle重建索引時，查詢仍然可以使用舊索引。實際上，Oracle在rebuild時，在創建新索引過程中，並不會刪除舊索引，直到新索引rebuild成功。

從這點可以知道rebuild比刪除重建的一個好處是不會影響原有的SQL查詢，但也正由於此，用rebuild方式建立索引需要相應表空間的空閒空間是刪除重建方式的2倍。