I am having issues with glacial speeds of using a delete from statement within a SQLExecutor pointed to Oracle Database.
The SQL query is something like:
DELETE FROM ABC
WHERE ID = @value(id)
AND TO_CHAR(DATETIME,’YYYYMMDDHH24’) > @value(timefrom)
AND TO_CHAR(DATETIME,’YYYYMMDDHH24’) <= @value(timeto)
(note: The query above is paraphrased and might contain syntax-errors, I have checked and confirmed the syntax in the actual version)
The Oracle Table contains about 40 million rows, so I expect this to take some time (1-5 minutes per query) but not the >30 minutes for the first query (at which point I gave up, I have not confirmed if the query will ever finish)
Currently I am using a workaround of Loading the primary keys of all features that fit the selection criteria and using a writer to DELETE all corresponding records, but this seems unnecessarily inefficient and wasteful.
I can imagine that the error is caused by Oracle not returning a ‘succeeded’ or ‘finished’ message, causing FME to consider Oracle as busy forever.
Does anyone have any ideas to solve this issue?