topic Re: Optimize delete in a very big database table in Application Development and Automation Discussions

Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:23:15 GMT

Hi,

For delete entries in database table i use instruction:

Delete from <table> where <zone> = 'X'.

The delete take seven hours (the table is very big and <zone> isn't an index)

How can i optimize for reduce the delete time.

Thanks in advance for your response.

Regards.

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:43:20 GMT

Hi,

This is the shortest way of delete.

Wht you can do is, schedule it in background..

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 7, 2008 12:47 PM

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:48:43 GMT

Hi,

It's scheduled in background, but it's too long.

Regards.

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:53:14 GMT

hi ,

did u used the sort command in the coding before the delete satement..

regards.

Reena

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:56:24 GMT

Have you tried to create an index?

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:56:24 GMT

what is the size of the table and how many lines are you going to delete?

I would recommend you to delete only up to 5000 or 10000 records in one step.


do 100 times.
select * 
          from 
          into table itab.
          where
          up to 10.000 records.

if ( itab is initial )
  exit.
endif.

delete ... from table itab.
commit work.

If this is still too slow, than you should create a secondary index with zone.

You can drop the index after the deletion is finished.

Siegfried

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 13:56:42 GMT

i guess u can use package size in ur select

nd use delete accordinly

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 14:03:58 GMT

Hi,

I havn't tried with an index .

How can i use package size ???

Regards.

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 14:05:42 GMT

Hi jca,

1. INDEXING this field would REDUCE

the time CONSIDERABLY. That is one of the best

and simplest option.

regards,

amit m.

Re: Optimize delete in a very big database table

Former Member — Mon, 07 Apr 2008 14:22:59 GMT

u can create a secondary index on the field u r using in where condition but if tats not allowed use package size in ur select statement.

1>See how u can use package size press f1

2>make it variable on ur selection screen

Re: Optimize delete in a very big database table

Sm1tje — Mon, 07 Apr 2008 16:52:53 GMT

This very much depends on the <ZONE> field whether it is highly selective or not. If practically all the fields have the same value for this, than you're not gonna reduce the time CONSIDERABLY. The only thing you have done is create an unneccasary additional index which can even have a negative effect on other reports, selects, etc.

Only remark that I want to make: Be careful when creating new secondary index!!