Oracle Live SQL - Script: DDL Methods to Delete Rows

Statement 1

create table bricks ( 
  brick_id integer  
    not null  
    primary key, 
  colour   varchar2(10),  
  shape    varchar2(10), 
  weight   integer 
)

Table created.

Statement 2

create table bricks_child ( 
  brick constraint fk  
    references bricks ( brick_id ) 
    on delete cascade 
)

Table created.

Statement 3

create or replace procedure load_rows ( num_rows integer ) as 
begin 
  execute immediate 'truncate table bricks cascade'; 
  insert into bricks  
    with rws as ( 
      select level x from dual 
      connect by level <= num_rows 
    ) 
      select rownum, 
             case mod ( rownum, 3 )  
               when 0 then 'red' 
               when 1 then 'blue' 
               when 2 then 'green' 
             end, 
             case mod ( rownum, 2 )  
               when 0 then 'cube' 
               when 1 then 'cylinder' 
             end, 
             round ( dbms_random.value ( 2, 10 ) ) 
      from   rws; 
end load_rows;

Procedure created.

Statement 4

create or replace procedure count_rows as 
  num_rows pls_integer; 
begin 
  select count (*)  
  into   num_rows 
  from   bricks; 
   
  dbms_output.put_line ( 'Num rows = ' || num_rows ); 
end count_rows;

Procedure created.

Statement 5

begin  
  load_rows ( 1000 ); 
  insert into bricks_child values ( 1 ); 
  commit; 
end;

Statement processed.

Statement 6

Truncate removes all the rows from a table. But you can't use it when there are enabled foreign keys referencing the table.

Truncate Table

truncate table bricks

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02266

Statement 7

Adding the cascade clause also removes rows from child tables. To use this you must declare the foreign keys as ON DELETE CASCADE.

Truncate Table Cascade

truncate table bricks  
  cascade

Table truncated.

Statement 8

select count (*) from bricks


COUNT(*)
0

COUNT(*)
0

Statement 9

select count (*) from bricks_child


COUNT(*)
0

COUNT(*)
0

Statement 10

Instead of the CASCADE clause, you can disable foreign keys before TRUNCATE. Then re-enable them after. You need to remove all rows from the child tables before you can do this!

Truncate Table Disable FKs

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate 'alter table bricks_child   
    modify constraint fk disable';  
  
  execute immediate 'truncate table bricks';  
  
  execute immediate 'alter table bricks_child   
    modify constraint fk enable';  
  count_rows ();  
end;

Statement processed.
Num rows = 1000
Num rows = 0

Statement 11

drop table bricks_child

Table dropped.

Statement 12

Added in 122, you can use the INCLUDING ROWS clause of ALTER TABLE ... MOVE to discard rows not matching the where clause.

Filtered Table Move

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate 'alter table bricks  
    move including rows  
    where brick_id > 900';  
  count_rows ();  
end;

Statement processed.
Num rows = 1000
Num rows = 100

Statement 13

A fast way to remove lots of data is to save the rows you want to keep to a temporary table. Then truncate the original table and copy the rows back from the temporary table.

CTAS Switch Rows

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate '  
  create table bricks_keep (  
    brick_id primary key,  
    colour, shape, weight  
  ) as  
    select * from bricks  
    where  brick_id > 900';  
        
  execute immediate 'truncate table bricks';  
    
  execute immediate 'insert into bricks  
    select * from bricks_keep';  
  count_rows ();  
end;

Table created.
Num rows = 1000
Num rows = 100

Statement 14

drop table bricks_keep

Table dropped.

Statement 15

Instead of switching the rows over, you can switch the tables over by renaming them! To complete this process you also have to copy indexes, grants, etc. from the old table to the new.

CTAS Switch Tables

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate '  
  create table bricks_keep (  
    brick_id primary key,  
    colour, shape, weight  
  ) as  
    select * from bricks  
    where  brick_id > 900';  
        
  execute immediate 'rename bricks to bricks_old';  
    
  execute immediate 'rename bricks_keep to bricks';  
  count_rows ();  
end;

Table created.
Num rows = 1000
Num rows = 100

Statement 16

alter table bricks 
  modify partition by range ( brick_id ) 
  interval ( 100 ) ( 
  partition p1 values less than ( 101 ) 
)

Table altered.

Statement 17

If the table is partitioned and you want to delete all the rows in a partition, you can do so by truncating that partition.

Truncate Partition

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate 'alter table bricks  
    truncate partition p1';  
  count_rows ();  
end;

Table truncated.
Num rows = 1000
Num rows = 900

Statement 18

Or by dropping the whole partition!

Drop Partition

declare  
  row_count pls_integer;  
begin  
  load_rows ( 1000 );  
  count_rows ();  
  execute immediate 'alter table bricks  
    drop partition p1';  
  count_rows ();  
end;

Table dropped.
Num rows = 1000
Num rows = 900

Statement 19

create table bricks_part ( 
  brick_id integer  
    not null  
    primary key, 
  colour   varchar2(10),  
  shape    varchar2(10), 
  weight   integer 
) partition by range ( brick_id ) ( 
  partition p1 values less than ( 10001 ), 
  partition p2 values less than ( 20001 ), 
  partition p3 values less than ( 30001 ), 
  partition p4 values less than ( 40001 ), 
  partition p5 values less than ( 50001 ), 
  partition p6 values less than ( 60001 ), 
  partition p7 values less than ( 70001 ), 
  partition p8 values less than ( 80001 ), 
  partition p9 values less than ( 90001 ), 
  partition p10 values less than ( 100001 ) 
)

Table created.

Statement 20

create or replace package timing_pkg as  
 
  rows_to_load pls_integer := 100000; 
  start_time pls_integer; 
  time_taken pls_integer; 
   
  procedure set_start_time; 
  procedure calc_runtime (  
    operation varchar2 
  ); 
   
  procedure load_rows; 
  procedure load_rows_partition; 
   
end;

Package created.

Statement 21

Routines to test the performance of the "delete" methods

Test Framework

create or replace package body timing_pkg as   
    
  procedure set_start_time as  
  begin  
    start_time := dbms_utility.get_time;  
  end;  
    
  procedure calc_runtime (   
    operation varchar2  
  ) as  
  begin  
    time_taken :=   
      ( dbms_utility.get_time - start_time );  
    dbms_output.put_line ( operation || ' ' || time_taken );  
  end;  
    
  procedure load_rows_partition as  
  begin  
    execute immediate 'truncate table bricks_part';  
    insert into bricks_part  
      with rws as (  
        select level x from dual  
        connect by level <= timing_pkg.rows_to_load  
      )  
        select rownum,  
               case mod ( rownum, 3 )   
                 when 0 then 'red'  
                 when 1 then 'blue'  
                 when 2 then 'green'  
               end,  
               case mod ( rownum, 2 )   
                 when 0 then 'cube'  
                 when 1 then 'cylinder'  
               end,  
               round ( dbms_random.value ( 2, 10 ) )  
        from   rws;  
  end;  
    
  procedure load_rows as  
  begin  
    execute immediate 'truncate table bricks';  
    insert into bricks   
      with rws as (  
        select level x from dual  
        connect by level <= timing_pkg.rows_to_load  
      )  
        select rownum,  
               case mod ( rownum, 3 )   
                 when 0 then 'red'  
                 when 1 then 'blue'  
                 when 2 then 'green'  
               end,  
               case mod ( rownum, 2 )   
                 when 0 then 'cube'  
                 when 1 then 'cylinder'  
               end,  
               round ( dbms_random.value ( 2, 10 ) )  
        from   rws;  
  end;  
      
end;

Package Body created.

Statement 22

To test the performance of different methods, run "EXEC REMOVE_ROWS ( );", where PCT is the percentage between zero and one of rows you want to remove.

Deleting Rows Benchmark

create or replace procedure remove_rows ( pct number ) is  
begin  
  begin  
    execute immediate 'drop table bricks_keep purge';  
  exception  
    when others then null;  
  end;  
  begin  
    execute immediate 'drop table bricks_old purge';  
  exception  
    when others then null;  
  end;  
    
  timing_pkg.load_rows;  
  timing_pkg.set_start_time;  
    
  delete bricks  
  where  brick_id <= ( timing_pkg.rows_to_load ) * pct;  
    
  timing_pkg.calc_runtime ( 'Delete-where' );  
  
  timing_pkg.load_rows ();  
  timing_pkg.set_start_time ();  
    
  execute immediate '  
  create table bricks_keep (  
    brick_id primary key,  
    colour, shape, weight  
  ) as  
    select * from bricks  
    where  brick_id > ' ||   
      ( timing_pkg.rows_to_load ) * pct;  
        
  execute immediate 'rename bricks to bricks_old';  
    
  execute immediate 'rename bricks_keep to bricks';  
    
  timing_pkg.calc_runtime ( 'Delete-ctas-switch-table' );  
    
  timing_pkg.load_rows ();  
  timing_pkg.set_start_time ();  
    
  execute immediate '  
  create table bricks_keep (  
    brick_id primary key,  
    colour, shape, weight  
  ) as  
    select * from bricks  
    where  brick_id > ' ||   
      ( timing_pkg.rows_to_load ) * pct;  
        
  execute immediate 'truncate table bricks';  
    
  execute immediate 'insert into bricks  
    select * from bricks_keep';  
    
  timing_pkg.calc_runtime ( 'Delete-ctas-switch-rows' );  
    
  timing_pkg.load_rows ();  
  timing_pkg.set_start_time ();  
    
  execute immediate '  
  alter table bricks  
    move  including rows  
    where brick_id > ' ||   
      ( timing_pkg.rows_to_load ) * pct ||  
    ' online';  
    
  timing_pkg.calc_runtime ( 'Delete-move' );  
    
  timing_pkg.load_rows_partition ();  
  timing_pkg.set_start_time ();  
    
  for i in 1 .. ( pct * 10 ) loop  
    execute immediate 'alter table bricks_part  
      truncate partition p' || i;  
  end loop;  
    
  timing_pkg.calc_runtime ( 'Truncate-partition' );  
    
end;

Procedure created.

DDL Methods to Delete Rows

Additional Information