delete db dtable with non key fields

[UmaArjunan]

Dear Experts,

Deleting the temporary staging table in ECC for clearing item around 13M data based on open document number using range table with 10000 doc no at a time and then it is extracted to BW.

Tried with full scan parallel option also deletion is very slow at the db level.

DELETE FROM dfkkcibw WHERE opbel IN rt_del_ciopbel
%_HINTS DB6 'FULL(DFKKCIBW) PARALLEL (DFKKCIBW, 4)'.

COMMIT WORK AND WAIT.

Any other efficient way to delete records based on non-key fields ? please suggest .

Thanks,

ABAP dev

[Sandra_Rossi]

As you say, " 50000 records takes 2.6 seconds".

So 13M would then take 11 minutes and 16 seconds.

Parallelizing on 4 threads would take 2 minutes and 49 seconds (+ parallel overhead time).

Is 2 minutes 49 seconds "slow", as you say, or is it differently "slow"?

[Sandra_Rossi]

Above was with secondary index.

With full scan, you said " 50000 records takes 0.66 seconds".

So 13M would then take 2 minutes and 51 seconds.

Parallelizing on 4 threads would take 43 seconds (+ parallel overhead time).

Is 43 seconds "slow", as you say, or is it differently "slow"?

[UmaArjunan]

Hi Sandra , DELETE FROM dfkkxxx WHERE opbel IN rt_del_ciopbel ( range table with 10000 records at a time )

this is the statistics :

50000 records testing in dev to check the time consumed for index, it could be more in quality

[Sandra_Rossi]

You can't compare on 2 different systems. I thought you indicated the performance on a system identical to the target system. So, forget it.

Creating an index just for a one-time operation may be globally counter-performing, because the counterpart is to slow down all insertions. It's usually better accepted that user (day) activity is favored over a batch (night) process. A bulk database operation is slow, that's it.

Performance is to be measured and tuned on the target system or on an identical system. The performance tuning (hints or whatever) should be left to the database administrator.

[UmaArjunan]

transported now it is taking 3,689 seconds to delete 13m data in quality. thanks,

[appel_solar_dk]

I suppose that what takes the time is a full table scan of your table for each row in rt_del_ciopbel. On average n/2 rows will have to be scanned to find the row to delete. With an index that time will go down drastically. It will of cause take some time (and space) to maintain the index but probably a lot less than scanning for each deletion.

[UmaArjunan]

I was bit cautious to know , How does the secondary index works in case of full deletion of a table on a daily basis, will it automatically build the secondary index at the db level every time when the data is deleted fully and new data is populated ?? apart from secondary index creation for opbel at table, is there any thing to do (considering full table deletion) from db team ..

[UmaArjunan]

created secondary index for opbel, deletion for 50000 records takes 2.6 seconds where as without secondary index it took 0.66 seconds.

[appel_solar_dk]

Well it is hard to argue against the numbers but it sounds weird. How many rows were there in the table before deleting the 50.000? (Did the secondary index include mandt as a field, it could have an impact?)

[UmaArjunan]

it is 50216 and yes included Mandt , opbel is the secondary index, ,now removed mandt and kept opbel and tested it is 1.7sec should try with more data

[UmaArjunan]

thanks for all the suggestions provided . removed MANDT and created sec index with only OPBEL. Transported index to quality and for 13 M time went down drastically. now it is taking 3,689 seconds to 13m data

Do we need to index re-org as well at db level for this case ?.pl. suggest

[raymond_giuseppi]

Did you already look at 1427449 - Performance issue during deletion of item extracts for BW?

[UmaArjunan]

Yes already using the program RFKKOPBWS_DELETE_MARK to do the full deletion everyday. it deletes by comparing key fields i.e less than record number with block size 100000 and it completes less than a minute , the one i m deleting is for applying intermittent filter based on non key for BW extraction .

[appel_solar_dk]

Hi ABAP Dev

I think you should take a small DB course it will help you going forward. The index can be added in the normal t-codes so you don't need to involve the DB team as such but they may be able to help you with your detailed questions about how a DB works.

-Kristian

[appel_solar_dk]

Hi ABAP dev

Have you tried to put an index on opbel? It should help.

If you want to clear the table completely a TRUNCATE would be faster but I suppose you have a need to only delete some rows.

Regards

Kristian

[UmaArjunan]

Yes i have a need to delete certain rows . This is a temporary staging table and it is completely cleared everyday before loading, After loading again next day with full clear item, we are filtering the data in the table using the delete statement above .

considering full deletion on a daily basis, Would it still help for Deletion if a second index opbel is created on the table level , would it not consume additional time for building index etc, everyday. pl. clarify. If there is no impact i wanted to try