Re: Performance problem in a DELETE query

On 21 Nov 2001 15:28:08 -0800, oraskm_at_yahoo.com (Sanjay Mishra) wrote:

>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,

The problem is your inner subquery, with two full table scans and nested loops
either : rewrite that to where exist
or
force it with user_merge to do a sort/merge Furthermore: a table should _always_ have a primary key and I would recommend to use explicit primary key names instead of system generated

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Nov 21 2001 - 23:25:23 CST