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?