Re: delete takes too long

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Mar 1999 15:38:51 GMT
Message-ID: <36ea9183.10190112@192.86.155.100>

A copy of this was sent to christian B <parents_at_eskimo.com> (if that email address didn't require changing) On Tue, 09 Mar 1999 22:37:06 -0800, you wrote:

>Hi,
>
>I have a problem with a delete taking a really long time.
>
>here is the situation:
>
>three tables linked with foreign key constraints
>
>lbk_trip
> |
> ---< lbk_tow
> |
> -----< lbk_catch
>
>
>the row counts of the tables are about
>
>lbk_trip 60,000
>lbk_tow 320,000
>lbk_catch 1,500,000
>
>I just issue a delete on lbk_trip and let it cascade through the
>foreign key constraint.
>
>The time it takes to complete that delete not only depends on the total
>number of records deleted, but also on the overall size of the tables.
>A typical delete would delete 4000 records from lbk_trip. At its current
>size that takes about 2-3 hrs, as opposed to ca. 5-10 minutes when it
>only had 10,000 records. Now I can see that it will take longer to find
>the associated child records to be deleted, if the tables are bigger,
>but assuming Oracle uses the available indexes to do that, I wouldn't
>expect it to grow that dramatically.
>
>It's now almost as time consuming to delete 5% of the records as it is
>to drop the tables and refill 95% from text files, performing a bunch of
>data validation in the process.
>
>Is there a way to do this more efficiently?
>

have you used timed_statistics and sql_trace and tkprof to see whats really happening?

do you have correctly sized log files? (look in your alert.log for messages like "cannot allocate new log")

do you have your foreign keys indexed? else a full scan results -- perhaps many times (eg: if lbk_catch is not indexed on the foreign key, then a full scan will happen for each of the lbk_tow rows deleted as a result of deleting from lbk_trip). I have a script below that reports on unindexed foreign keys in a schema...

>Thanks
>
> Christian

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,

	     max(decode(position, 1,     column_name,NULL)) || 
	     max(decode(position, 2,', '||column_name,NULL)) || 
	     max(decode(position, 3,', '||column_name,NULL)) || 
	     max(decode(position, 4,', '||column_name,NULL)) || 
	     max(decode(position, 5,', '||column_name,NULL)) || 
	     max(decode(position, 6,', '||column_name,NULL)) || 
	     max(decode(position, 7,', '||column_name,NULL)) || 
	     max(decode(position, 8,', '||column_name,NULL)) || 
	     max(decode(position, 9,', '||column_name,NULL)) || 
	     max(decode(position,10,', '||column_name,NULL)) || 
	     max(decode(position,11,', '||column_name,NULL)) || 
	     max(decode(position,12,', '||column_name,NULL)) || 
	     max(decode(position,13,', '||column_name,NULL)) || 
	     max(decode(position,14,', '||column_name,NULL)) || 
	     max(decode(position,15,', '||column_name,NULL)) || 
	     max(decode(position,16,', '||column_name,NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a, ( select table_name, index_name,

	     max(decode(column_position, 1,     column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns

    from user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 10 1999 - 09:38:51 CST