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.


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


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.

Article Information

Last Updated:2020-07-16 11:44:08