delete ... where not exists ... performance Options

[PPJava]

Hi there!

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_inputjob
WHERE  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):

CODE

Plan hash value: 3615700051

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |                |  7872 |   615K|       | 56776   (1)| 00:11:22 |
|   1 |  DELETE                | TABLE_INPUTJOB |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |                |  7872 |   615K|    41M| 56776   (1)| 00:11:22 |
|*  3 |    TABLE ACCESS FULL   | TABLE_INPUTJOB |   787K|    32M|       | 25398   (1)| 00:05:05 |
|   4 |    INDEX FAST FULL SCAN| IDX_TJ_IIDJS   |  4651K|   164M|       | 18477   (1)| 00:03:42 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TABLE_JOB"."INPUTID"="TABLE_INPUTJOB"."INPUTID")
   3 - filter("INDEXFILESTATE"=TO_NUMBER(:2) AND "INPUTFILESTATE"=TO_NUMBER(:1))

Table definition looks like this:

CODE

CREATE TABLE TABLE_INPUTJOB (
  TASKNAMES VARCHAR2(4000),
  INPUTID VARCHAR2(100) NOT NULL,
  INPUTFILESTATE NUMBER(10),
  EXECUTIONQUEUED DATE,
  EXECUTIONSTART DATE,
  EXECUTIONEND DATE,
  PRIORITY NUMBER(10),
  SERVERNAME VARCHAR2(100),
  INDEXFILESTATE NUMBER(10),
  OBSERVER VARCHAR2(50),
  ALLJOBS NUMBER(10),
    CONSTRAINT PK_TABLE_INPUTJOB PRIMARY KEY (INPUTID)
);

CREATE TABLE "TABLE_JOB" (
  "JOBSTATE" NUMBER(10),
  "DATETIME" DATE,
  "USERNAME" VARCHAR2(50),
  "DDONAME" VARCHAR2(1024) NOT NULL,
  "TEXTSEARCH" VARCHAR2(4000),
  "PROCESSED" NUMBER(10),
  "ARCHIVED" NUMBER(1),
  "ENABLED" NUMBER(1) NOT NULL,
  "QUEUENAME" VARCHAR2(100),
  "DOCTYPE" VARCHAR2(100),
  "PAGECOUNT" NUMBER(10) NOT NULL,
  "SHEETCOUNT" NUMBER(10) NOT NULL,
  "COPY" NUMBER(10),
  "ERRORDESCRIPTION" VARCHAR2(255),
  "JOB_ID" VARCHAR2(36) UNIQUE NOT NULL,
  "INPUTID" VARCHAR2(100) NOT NULL,
  "JOBID" VARCHAR2(100) UNIQUE,
  "LOCKED" NUMBER(10),
  "LAST_OUTPUT" DATE,
  "DOCTYPENR" NUMBER(10),
  "EVENT_ID" VARCHAR2(36),
    CONSTRAINT "PK_TABLE_JOB" PRIMARY KEY ("JOB_ID", "INPUTID")
);

ALTER TABLE "TABLE_JOB" ADD CONSTRAINT "FK_JOB_INPUTJOB" FOREIGN KEY (
  "INPUTID"
)
REFERENCES "TABLE_INPUTJOB" (
  "INPUTID"
) ON DELETE CASCADE;

CREATE INDEX IDX_TABLE_JOB
  ON TABLE_JOB (INPUTID, JOB_ID) NOLOGGING;

CREATE INDEX IDX_TJ_IIDJS
  ON TABLE_JOB (INPUTID, JOBSTATE) NOLOGGING;

[burleson]

Hi,

>> I've got some performance issues with an "delete ... where not exists" statement.

The WHERE NOT EXISTS subquery, in most cases, this type of subquery can be re-written with a MINUS operator:

http://www.dba-oracle.com/oracle_news/2005...s_minus_sql.htm


See here, how to tune "not exists" subqueries:

http://www.dba-oracle.com/t_cbo_existence_queries.htm

[rastl]

Hello PPJava,

interesting case.

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 and
2) 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 !

q+1098*d=115 (statement took 115 seconds to delete 1098)
q+50*d=46 (50 rows deletion took about 46 seconds)

q = 22379/524 (about 43 seconds)
d = 69/1048 (about 66 milliseconds)

131722=round(22379/524+2000000*69/1048)

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_INPUTJOB
and even removing the index (and PK constraint) PK_TABLE_INPUTJOB for executing this DELETE.

Please provide some runtime statistics for further investigation.

Best Regards,
Rainer Stenzel

[HelloRavi]

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.

[SteveC]

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.

[burleson]

See here, my tips for tuning a NOT EXISTS subquery:

http://www.dba-oracle.com/t_tuning_where_not_exists_sql.htm