Implementation of oracle re-Indexing

Oracle re-indexing is a frequently used operation. The following describes the implementation of oracle re-Indexing in detail, hoping to help you.

Oracle indexes can be re-built in multiple ways, such as drop and re-create, rebuild, and rebuild online. The similarities, differences, and advantages and disadvantages of these methods are compared below:

First, create a test table and data:

 
 

  
  
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  
  
  
 
 
 

1. drop and re-create and rebuild

First, let's take a look at the locking status of the table when the index is created normally.

 
 

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

The index has been created.

 
 

  
  
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  
 
 

It can be seen that oracle will apply the share lock to the base table when an index is created. Because the share lock and row-X are incompatible, that is, during the index creation process, you cannot perform DML operations on the base table.

The method for deleting and re-indexing is not introduced. It is the same as the above description. Let's take a look at how to create an index using rebuild.

 
 

  
  
suk@ORACLE9I> ALTER INDEX IDX_TEST_C1 REBUILD; 
 
 

The index has been changed.

Open another session to query the test lock:

 
 

  
  
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  
 
 

It can be seen that the rebuild method locks the base table in the same way as the CREATE method.

Start another session and execute the following SQL statement when the index is being rebuild:

 
 

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

Execution Plan

 
 

  
  
----------------------------------------------------------  
  
  
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) 
 
 

You can see that the old index can still be used for queries when oracle re-creates an index. In fact, oracle does not delete the old index when creating a new index during rebuild until the new index rebuild is successful.

From this point, we can know that one of the advantages of rebuild over deleting and rebuilding is that it will not affect the original SQL query, but it is also because of this, to create an index using the rebuild method, the free space of the corresponding tablespace is twice that of the deletion and reconstruction method.
 

Using replacement variables to improve Oracle Interaction

ORACLE system table and data dictionary View

Measure the test taker's knowledge about the Oracle file system mechanism.

Detailed explanation of four categories of Oracle index Scanning

Three methods for querying duplicate records in Oracle