How to speed up client deletions

[daniel_lippmann5]

When deleting large clients, long runtimes are often a problem. Parallelism can help, but the real bottleneck are large tables. So maybe this guide can help. The SQL statements are for Oracle databases with SAPSR3 layout, for other databases or layouts you have to modify them.

Manual deletion of tables

First of all, you need to identify large, client specific tables. E. g. in IS-U systems candidates are DFKKOP or SWWCNTP0. These tables are client
specific, so you can easily find out if the table can be truncated without harming the other clients:

SELECT mandt, count( * ) FROM sapsr3.dfkkop GROUP BY mandt; 

or

SELECT client, count( * ) FROM sapsr3.swwcntp0 GROUP BY mandt; 

As result, you see the number of rows for each client. When only the client you want to delete has rows, you can truncate the tables beforehand. This will significantly speed up the client deletion.

Copy & deletion of "unbalanced" tables

When you identify tables, where only a few rows are contained in the clients which will remain in the system and a huge amount of rows in the client
you want to delete, you can first copy all remaining rows in a new table (assuming you want to delete client 100):

CREATE TABLE sapsr3."dfkkop_tmp" TABLESPACE psapsr3 NOLOGGING PARALLEL 8 AS

SELECT /*+ INDEX("dfkkop" "dfkkop~0") */ * FROM sapsr3."dfkkop" WHERE mandt NOT IN ('100'): 

After that, you just have to truncate the original table and insert all rows from the temp table:

TRUNCATE TABLE sapsr3."dfkkop";

INSERT INTO sapsr3."dfkkop" SELECT * FROM "dfkkop_tmp";

COMMIT; 

ALTER INDEX sapsr3."dfkkop~0" REBUILD ONLINE; 

Rebuilding the primary index is a good thing to speed up the client deletion afterwards.

Automation of finding tables / preparing statements

If  there are many huge tables and the time windows is very small, you may use a scripting language to prepare the SQL statements. I prefer Perl,
as I know this language and as Oracle comes with Perl and the DBI module. Connecting to your database is pretty simple:

use DBI;

my $sid = "<SID>";

my $hostname = "localhost";

my $port = "<listener port>";

my $sappw = "<password>";

my $sapuser = "SAPSR3";

my $connection = DBI->connect("DBI:Oracle:SID=$sid;host=$hostname;port=$port","$sapuser/$sappw")

|| die "Can't connect to $sid: $!";

You can start the script with $ORACLE_HOME/perl/bin/perl script.pl as oracle owner. Next step will be to determine all client specific tables - in most cases,
the first column is named client, mandt or mandant. Here is a code snippet which will give you number of rows in the remaining clients and overall rows, you just have to adjust to your needs:

my $mandt_exclude = '100';

my $sql_all_tables = "SELECT object_name FROM dba_objects

WHERE owner = 'SAPSR3' AND object_type = 'TABLE' ORDER BY object_name ASC";

my $all_cursor = $connection->prepare($sql_all_tables);

$all_cursor->execute();

while (my @table = $all_cursor->fetchrow_array) {

$sql_first_column = "SELECT table_name, column_name FROM dba_cons_columns

WHERE table_name = '$table[0]'

AND constraint_name = ( SELECT MIN( constraint_name ) FROM dba_cons_columns

WHERE table_name = '$table[0]' )";

my $first_col_cursor = $connection->prepare($sql_first_column);

$first_col_cursor->execute();

while (my @col = $first_col_cursor->fetchrow_array) {

if ($col[1] eq "CLIENT" or $col[1] eq "MANDANT" or $col[1] eq "MANDT") {

  $sql_count_all = "SELECT count( * ) FROM \"$table[0]\"";

  my $count_all_cursor = $connection->prepare($sql_count_all);

  $count_all_cursor->execute();

  while (my @exc = $count_all_cursor->fetchrow_array) {

<Put your custom logic here>

  }

  $sql_count_exclude_mandt = "SELECT count( * ) FROM \"$table[0]\"

WHERE $col[1] NOT IN ($mandt_exclude)";

  my $count_exclude_cursor = $connection->prepare($sql_count_exclude_mandt);

  $count_exclude_cursor->execute();

  while (my @exc = $count_exclude_cursor->fetchrow_array) {

<Put your custom logic here>

}

}

You just have to add some output to build the SQL scripts.