Optimize the Massive DELETE Operation in Oracle, Part 1-3 : Page 3

1. Obtain performance results before load:

   
   select a.name,
          b.value
     from v$statname a,
          v$mystat b
    where a.statistic# = b.statistic#
      and a.name = 'redo size';
   
   NAME                    VALUE
   ----------------- -----------
   redo size            93019496
   
   insert /*+ APPEND */
   into TestData_Nologging
   select *
     from TestData;
   
   
   547875 rows created.
   
   Elapsed: 00:00:05.39
   

2. Obtain performance results after load:

   
   select a.name,
          b.value
     from v$statname a,
          v$mystat b
    where a.statistic# = b.statistic#
      and a.name = 'redo size';
   
   NAME                    VALUE
   ----------------- -----------
   redo size            93019404
   
   select used_ublk 
     from v$transaction;
   
   USED_UBLK
   ----------
          1
   

Populating the TestData_Nologging table with 547,875 rows in NOLOGGING mode took 5.39 seconds and generated only 0.01MB (!) (93,120,212 – 93,019,496= 100,716) of redo and 1 undo block.

Table 1 shows the performance numbers for both scenarios.

However, if you add just a primary key constraint to the TestData_Nologging table (Scenario 3), the redo size skyrockets even though both the table and the primary key index are in NOLOGGING mode:

drop table TestData_Nologging;

create table TestData_Nologging nologging as
select * 
  from TestData
 where 1 = 0;
 
alter table TestData_Nologging
add constraint TestData_Nologging_PK
primary key
(
  objectId,
  runId
);

alter index TestData_Nologging_PK nologging;

Obtain performance results before load:

select a.name,
       b.value
  from v$statname a,
       v$mystat b
 where a.statistic# = b.statistic#
   and a.name = 'redo size';

NAME                    VALUE
----------------- -----------
redo size            93198748

insert /*+ APPEND */
into TestData_Nologging
select *
  from TestData;


547875 rows created.

Elapsed: 00:00:15.61

Obtain performance results after load:

select a.name,
       b.value
  from v$statname a,
       v$mystat b
 where a.statistic# = b.statistic#
   and a.name = 'redo size';

NAME                    VALUE
----------------- -----------
redo size            131033428

select used_ublk 
  from v$transaction;

USED_UBLK
----------
       817

commit;

As you can see, adding index led to an elapsed time increase from 5.39 to 15.61 seconds, a redo size increase from 0.01MB to 36MB (131,033,428 – 93,198,748 = 37,834,680), and an undo blocks increase from 1 to 817 (!). The reason is the database did heavy logging for the primary key index. Putting a primary key index in NOLOGGING mode did not help because NOLOGGING applies only to a limited number of operations. Here is a list of such operations from Oracle9i SQL Reference Release 2 (9.2):

DML:

Direct-path INSERT (serial or parallel)
Direct Loader (SQL*Loader)


DDL:

CREATE TABLE ... AS SELECT
 
ALTER TABLE ... MOVE
 
ALTER TABLE ... [all partition operations that involve data movement]
 

ALTER TABLE ... ADD PARTITION (hash partition only)
ALTER TABLE ... MERGE PARTITIONS
 
ALTER TABLE ... SPLIT PARTITION
 
ALTER TABLE ... MOVE PARTITION
 
ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
 
ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
 
ALTER TABLE ... MODIFY PARTITION ... REBUILD UNUSABLE INDEXES


CREATE INDEX
 
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
 

See Oracle9i SQL Reference Release 2 (9.2) at for more details on NOLOGGING clause (login required).

The above list shows that you can take advantage of the CREATE INDEX operation by rearranging your steps:

1. Load the data into a table with no indexes.
2. Create indexes in NOLOGGING PARALLEL mode.
3. Create constraints.

The following series of commands for the TestData_Nologging table implements the above technique (Scenario 4):

drop table TestData_Nologging;

create table TestData_Nologging nologging as
select * 
  from TestData
 where 1 = 0;
 
insert /*+ APPEND */
into TestData_Nologging
select *
  from TestData;
  
create unique index TestData_Nologging_PK
on TestData_Nologging
(
  objectId,
  runId
)
nologging parallel;

alter table TestData_Nologging
add constraint TestData_Nologging_PK
primary key
(
  objectId,
  runId
);

Scenario 4 turns out to be more efficient than Scenario 3: redo size went down from 36.08MB to 0.17 and undo blocks from 817 to just 2.

Table 2 shows the performance numbers for both scenarios 3 and 4.

The numbers clearly show that Scenario 4 is the way to go. Now it's time to talk about implementing DELETEs as bulk INSERTs.