A user was recently complaining that deleting documents was very long. Indeed, for each document it was taking just short of 10 seconds.
After looking up what was happening in the Oracle db when doing this, I found out that the procedure RECYCLE_RECURSE was being called, and one statement in that procedure was accounted for 95% of the time spent.
The statement :
delete from entry_link
where source_id in (select tocid from to_recycle where sess_id = p_sess_id)
or target_id in (select tocid from to_recycle where sess_id = p_sess_id);
I currently have 750k rows in the ENTRY_LINK table, and the statement forces a ful table scan.
My simple remedy was to split the statement into two parts :
delete from entry_link
where source_id in (select tocid from to_recycle where sess_id = p_sess_id);
delete from entry_link
where target_id in (select tocid from to_recycle where sess_id = p_sess_id);
The 2 statement take close to no time to execute. Document deletions are now performed in subsecond's time.
Can you evaluate the possiblility to include this change in a future version of the product ?
Thank you !