Deletion of records in sys_user_grmember table is slow.


Description

Deletion of records in sys_user_grmember table is slow.

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.

- Deleting records from 'sys_user_grmember' table deletes records in 'sys_user_has_role' table as well. And DELETE operation is not properly supported by indexes on sys_user_has_role table.

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.