How to fix BULKDELETE performance problem

Due to several reasons, one may have to delete all the bootstrapped 3rd party user data from Oracle Internet Directory (OID). For example, thousands of entries may have got loaded from MS Active Directory into OID during the initial bootstrap process. If one wants to bulkdelete all these entries from OID, one can notice a performance problem during the delete operation. This problem currently exists in 10.1.4.2 Oracle Identity Management software. When i tried the bulkdelete operation with 60,000 entries, it took almost 20 hours to finish the execution.

The fix for this performance problem is to create a custom index on one of the OID tables.

1. Login as ods/***@ssodb
2. CREATE INDEX TEMP_UBS_CT_HRCH_QUERY_IDX on CT_HRCH_QUERY(ENTRYID);
3. exit
4. $ORACLE_HOME/ldap/bin/bulkdelete connect="ssodb" basedn="cn=Users,dc=oid_srv,dc=mycompany,dc=com"
5. Login as ods/***@ssodb
6. Drop index TEMP_UBS_CT_HRCH_QUERY_IDX

This index is only for fixing the performance problem and can be dropped after the bulkdelete operation completes successfully. I could complete the bulkdelete operation in less than 20 minutes with the custom index in place to delete the 60,000 entries from OID. Compare that with the 20 hours that i got without the index !