Massive Deletes Vs Truncating Partitions

This is not a revolutionary post, everything I discuss here has been discussed numerous times. Massive deletes are nothing new in a DBA’s life. They were there, they are there and they will continue to be there. From time to time DBA’s fall prey to the bad application design. My last post was about heavy redo log switches and this post is a continuation.

The application was performing massive DELETE operations resulting in excessive redo being generated. This in turn was responsible for too many redo log switches and severely affecting database performance. It was possible that the same DELETE statement on a non-partitioned table could be translated into a “ALTER TABLE … TRUNCATE PARTITION” statement by partitioning the same table.

In this post, I will demonstrate how one can achieve extreme performance by employing right techniques for the right job.

For this demo, I will create a test table “T” (without any indexes) with a single column. Populate the table with 2 Million records and then DELETE 1 Million records. Before and after the delete we will make a note of the redo generated.

Here we go:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id number) tablespace test_ts;

Table created.

SQL>
SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Before we delete, let’s make a note of redo generated by our session:

SQL> column name format a20
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                30.93

SQL>

Now perform the massive DELETE operation:

SQL>
SQL> set timing on
SQL> delete from t where id <= 1000000;

1000000 rows deleted.

Elapsed: 00:02:12.87
SQL> set timing off
SQL>

Make a note of redo generated by the DELETE statement:

SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                257.41

SQL>

So, the DELETE statement generated 226.48 MB (257.41 – 30.93) of redo and it took over 2 minutes to clock time.

Now, let us improvise the purge strategy but partitioning the table. I range partition the table into six partitions as shown:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number)  tablespace test_ts
  2  partition by range (id) (
  3  partition p1 values less than (500000),
  4  partition p2 values less than (1000000),
  5  partition p3 values less than (1500000),
  6  partition p4 values less than (2000000),
  7  partition p5 values less than (2500000),
  8  partition p6 values less than (MAXVALUE)
  9  );

Table created.

SQL>

Now run the same INSERT statement against the partitioned table:

SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Make a note of redo before and after executing the purge command:

SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.53

SQL>
SQL> set timing on
SQL> alter table t truncate partition p1;

Table truncated.

Elapsed: 00:00:00.62
SQL> alter table t truncate partition p2;

Table truncated.

Elapsed: 00:00:00.29
SQL> set timing off
SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.64

SQL>

Purging of two partitions consumed less than a second and generated only 0.11 MB of redo.

In some situations it may be possible that the redo logs were sized inadequately but in this case it was the application’s bad design responsible for massive redo log generation.

What happens when you have additional columns and indexes associated with the table? You could take that as a further exercise.