Question

SQLExecutor Very slow when using DELETE FROM in Oracle

  • 9 February 2024
  • 1 reply
  • 18 views

Badge +2

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?


1 reply

Badge +2

This issue seems to have resolved itself overnight. I Will need more time to check for consistency.

I am mainly interested in people, who have encountered this before and have found a different workaround.

Reply