I've got some performance issues with an "delete ... where not exists" statement. The statement is executed via JDBC.

The statement looks like this:

CODE

DELETE FROM table_inputjobWHERE inputfilestate =? AND indexfilestate =? AND NOT EXISTS(SELECT * FROM table_job WHERE table_job.inputid = table_inputjob.inputid)

Each table contains about 4500000 rows. The above statement took 115 seconds to delete 1098 rows from TABLE_INPUTJOB. A smaller run with 50 rows deleted took about 46 seconds. Another run was about to delete 2000000 rows, which I've canceled after several hours have passed. I need to delete this 2000000 rows in an acceptable amount of time.

Do you have any ideas to boost this statement?Thanks in advance!

Here is the result from EXPLAIN PLAN (when the statement should delete 1036 rows):

The performance/runtime/resource consumption of DML operations depend roughly on to two factors (excluding topics as data order/clustering,read consistency issues or lgwr/dbwr bottlenecks)

1) performance of the query defining the result set and2) performance of executing the data manipulation (DELETE of a row in your case).

Your HASH ANTI JOIN on FULL SCAN row sources should perform almost independent from the size of the result set, but the effort and runtime of DELETING will increase linearly with the number of rows to delete.

According to your numbers (43 seconds for the query phase and about 66 milliseconds for DELETION of one row) I would estimate a runtime of about 131722 seconds for deletion of 2000000 rows !

Hence I would wonder why deleting one row takes about 66 milliseconds.Bear in mind that deletion involves index maintainence and FOREIGN KEY constraint checking, so Oracle has to read the index PK_TABLE_INPUTJOB and one of the TABLE_INPUTJOB.INPUTID indexes for every row to delete. In worst case Oracle is using IDX_TABLE_JOB instead of IDX_TJ_IIDJS which has already been read by the INDEX FAST FULL SCAN step (no idea how Oracle chooses the index for FK constraint checking). This may result into a lot of additional db file sequential reads in case of a bad clustering factor.

If possible (if deletion is running exclusively) I would try to disable the FOREIGN KEY constraint FK_JOB_INPUTJOBand even removing the index (and PK constraint) PK_TABLE_INPUTJOB for executing this DELETE.

The explain plan shows the table "table_inputjob" is FULL ACCESS, which is causing the delay. You may create an index for this column and try again. Ensure the Where clause field got index created in the respective field. Or already available indexed column may be used subject to the design.

Using not exists, not, and not equal - Oracle has to search everything before it knows the value is not present. An index won't help (unless the column is not null), because normal indexes do not index null values.