Fast Insert and Delete operation method in Oracle

Performing the insert and delete operation in short span of time if one wants to perform bulk load of insert or delete operation on a table (Oracle Database).

Following steps needs to be followed for carrying out the activity:

1. Mark the Indexes unusable
2. Disable primary key, if any
3. Do an Delete
4. Do an Insert /*+ append */ into table (select ..)
5. Enable primary key
6. Rebuild Indexes

Explanation:

Step 1: Mark the Indexes unusable

This is the first important step to perform, get the list of indexes belonging to table which needs to be marked as unusable as shown below:

Example:

SQL> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME FROM DBA_INDEXES WHERE 
     TABLE_NAME='TEST_DETAILS' AND OWNER='TEST';

OWNER	INDEX_NAME	INDEX_TYPE	TABLE_OWNER	  TABLE_NAME
------ ------------     ----------      ------------    --------------
TEST	TEST_IDX	NORMAL	            TEST	TEST_DETAILS
TEST	TEST_IDX1	NORMAL	            TEST	TEST_DETAILS

For example in above output table TEST_DETAILS belonging to TEST schema has two indexes named TEST_IDX and TEST_IDX1. Make both the indexes as unusable by firing below command:

SQL> ALTER INDEX TEST.TEST_IDX unusable;
SQL> ALTER INDEX TEST.TEST_IDX1 unusable;

 

Step 2: Disable primary key,if any

Check TEST.TEST_DETAILS table whether any primary key is there or not if exists disable the same as shown below; if primary key is not there you may move on to Step 3:

SQL> ALTER TABLE TEST.TEST_DETAILS MODIFY CONSTRAINT PK_TEST DISABLE;

 

Step 3: Do an Delete

SQL> DELETE FROM TEST.TEST_DETAILS WHERE L_DATE >= '31/MAR/2012';

18808657 rows deleted.

SQL> commit;
Commit complete.

 

Step 4: Do an Insert /*+ append */ into table (select ..)

Use Append Hint for insert operation;

SQL> INSERT /*+ APPEND*/ INTO TEST.TEST_DETAILS SELECT * FROM TEST.TEST_DETAILS_BKP;

18777927 rows created.

SQL>commit;

Commit complete.

 

Step 5: Enable primary key

SQL> ALTER TABLE TEST.TEST_DETAILS MODIFY CONSTRAINT PK_TEST DISABLE;

 

Step 6: Rebuild Indexes

SQL> ALTER INDEX TEST.TEST_IDX rebuild;
SQL> ALTER INDEX TEST.TEST_IDX1 rebuild;

If you will rebuild the index it will come into USABLE state.

Advantage:

If one performs the Insert or Delete operation on a TABLE having millions of rows and with INDEXES created on the TABLE then the transaction may take a huge time for Deleting or Inserting the records, time may vary from 1 hour to 24 hours or even more.
If above Steps are performed then the tasks would be achieved in very short time say within 5 to 10 minutes.

Please share If you like..Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Yummly

3 Comments

Add a Comment
  1. It worked for me…thanks man

  2. On large tables, where index rebuild takes 4-5 hrs, is there any other optiin

    1. To make itself into usable state it has to take some amount of time which is valid..

Leave a Reply

Your email address will not be published. Required fields are marked *

Fariddin Shaikh's Oracle DBA Blog © 2016 Being DBA
Go Back