Optimize delete in a very big database table

[Former Member]

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.

[Former Member]

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

[Former Member]

Hi,

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

Regards.

[Former Member]

hi ,

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

regards.

Reena

[Former Member]

Have you tried to create an index?

[Former Member]

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

[Former Member]

i guess u can use package size in ur select

nd use delete accordinly

[Former Member]

Hi,

I havn't tried with an index .

How can i use package size ???

Regards.

[Former Member]

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

[Former Member]

Hi jca,

1. INDEXING this field would REDUCE

the time CONSIDERABLY. That is one of the best

and simplest option.

regards,

amit m.

[Sm1tje]

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