Issue
HI Team, For the business requirement we need to delete some set of record in the(sys_user_grmember). Hence we performed the fix script(delete existing group members for w.area). But we observed after 4 hrs script ran it deleted only 500 record. Could you please help why the deletion is taking too much of time.
Cause
- The deletion is slow as the table is by default subscribed to rollback, which means every record you delete will create a rollback record. This for large deletions adds up significant time.
- The other thing that happens is the DELETE operation is not properly supported by indexes on table sys_user_has_role.
Resolution
Recommendation to make the delete faster:
1. Disable rollback on table sys_user_has_role (add to the Collection record on Dictionary for table sys_user_has_role the attribute "Exclude from Rollback" (excludeFromRollback) with value true). This will stop rollback recording for any deletions from table sys_user_has_role
2. We need to add an index on table sys_user_has_role on column inh_count.
Once the 2 recommendations above are implemented the deletion should be much faster.
