Performance problem in a DELETE query

From: Sanjay Mishra <oraskm_at_yahoo.com>
Date: 21 Nov 2001 15:28:08 -0800
Message-ID: <eca56b4a.0111211528.2f7d0ab5@posting.google.com>

I am having difficulty in tuning the following query. It takes more than one hour to delete 1.4 million rows.

DELETE FROM TABLE_O
WHERE EXISTS ( SELECT
*
FROM
TABLE_C , TABLE_B , TABLE_A
WHERE TABLE_A.c2 = TABLE_B.c2

AND TABLE_A.c1  = TABLE_B.c1 
AND TABLE_B.c3  = TABLE_C.c3 
AND TABLE_B.MEASURE_ID  = 'FORECAST' 
AND TABLE_C.START_DATE  = TABLE_A.START_DATE 
AND TABLE_C.END_DATE  = TABLE_A.END_DATE 
AND TABLE_A.ITEM_ID  = TABLE_O.ITEM_ID
AND TABLE_A.LOCATION_ID  = TABLE_O.LOCATION_ID

AND I_BUCKET_SPEC.BUCKET_ID = TABLE_O.BUCKET_ID AND TABLE_O.MEASURE_ID = 'FORECAST'
AND TABLE_C.BUCKET_ID = ( SELECT
(bs.BUCKET_ID )
FROM
TABLE_C bs , TABLE_D tp
WHERE tp.FC_CURRENT < bs.END_DATE
AND tp.FC_CURRENT >= bs.START_DATE ))

The OPTIMIZER_MODE = CHOOSE. I have analyzed all the tables using COMPUTE. Number of records in the tables:

TABLE_C : 168
TABLE_B : 2935956
TABLE_A : 17615736
TABLE_D : 1
TABLE_O : 4487074 (prior to DELETE)

Number of records expected to be deleted: 1467978

Each of these tables (except TABLE_D) has just one index, the primary key, and they are:

TABLE_C : (c3, BUCKET_ID)
TABLE_B : (c1, c2, MEASURE_ID)
TABLE_A : (c1, c2, START_DATE)
TABLE_D : No primary key. No index.
TABLE_O : (c1, c2, MEASURE_ID, BUCKET_ID)

The explain plan is:
DELETE STATEMENT Cost = 2144
  DELETE I_MEASURE_ITM_LOC_BUCKET
    FILTER

      TABLE ACCESS FULL TABLE_O
      FILTER
        NESTED LOOPS
          NESTED LOOPS
            TABLE ACCESS BY INDEX ROWID TABLE_B
              INDEX UNIQUE SCAN SYS_C00280993
            TABLE ACCESS BY INDEX ROWID TABLE_C
              INDEX UNIQUE SCAN SYS_C00280706
                NESTED LOOPS
                  TABLE ACCESS FULL TABLE_D
                  TABLE ACCESS FULL TABLE_C
          TABLE ACCESS BY INDEX ROWID TABLE_A
            INDEX UNIQUE SCAN SYS_C00283080
      

Any suggestion to improve the performance of the query is highly appreciated.

Thanks, Received on Wed Nov 21 2001 - 17:28:08 CST