Truncate vs Delete - Text index
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
We have a schema into which we periodically load data for display on a website.
The tables are all truncated before data is copied into them.
A couple of times now we have gotten this error "ORA-04020: deadlock detected while trying to lock object". then when we reattempt the upload this error occurs
"ORA-29860: cannot truncate a table with domain indexes marked LOADING".
the only fix is to drop and recreate the Oracle Text index.
The process has been running for years, and the only change is that the users now do the upload during business hours whereas previously it was done out of business hours.
We don't know the cause of the problem but I was thinking about changing the Truncates to Deletes. Performance and space are not an issue for us as the volume is low (5000 records max in a table).
Would using a Delete be "cleaner" than using a Truncate on a table with Oracle Text indexes or are the same issues likely to occur?