Deleting large amount of data using index or full table scan?

Setup:

[11gr2@rh5 ~]$ uname -snrio
Linux rh5.lab.dbaglobe.com 2.6.18-194.26.1.el5 i386 GNU/LinuxTable created.

SQL> create table large1
  2  (id number,
  3  char20 varchar2(20),
  4  char180 char(180))             
  5  tablespace assm;

Table created.

SQL> create table large2
  2  (id number,
  3  char20 varchar2(20),
  4  char180 char(180))
  5  tablespace assm;

Table created.

SQL> begin 
  2  for i in 1..4000000
  3  loop
  4  insert into large1 values (i,dbms_random.string('X',20),' ');
  5  insert into large2 values (i,dbms_random.string('X',20),' ');
  6  if mod(i,1000)=0 then
  7  commit;
  8  end if;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> create unique index large1_pk on large1(id);

Index created.

SQL> create unique index large2_pk on large2(id);

Index created.

SQL> create index large1_n1 on large1(char20);

Index created.

SQL> create index large2_n1 on large2(char20);

Index created.

SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

SQL> col segsize format 999,999,999,999
SQL> col segment_name for a30

SQL> select segment_name,sum(bytes) segsize from user_extents
  2  where segment_name like 'LARGE%'
  3  group by segment_name
  4  order by segment_name;

SEGMENT_NAME    SEGSIZE
------------------------------ ----------------
LARGE1        964,689,920
LARGE1_N1       150,994,944
LARGE1_PK        75,497,472
LARGE2        964,689,920
LARGE2_N1       150,994,944
LARGE2_PK        75,497,472

6 rows selected.

Round 1:

SQL> explain plan for 
  2  delete from large1 where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |       |   999K|    25M|  2230 (1)| 00:00:27 |
|   1 |  DELETE    | LARGE1    |       |       |     |       |
|*  2 |   INDEX RANGE SCAN| LARGE1_PK |   999K|    25M|  2230 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("ID"<1000000)

14 rows selected.

SQL> explain plan for 
  2  delete /*+ full(t) */ from large2 t where id<1000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |     | 999K|  25M| 31892   (1)| 00:06:23 |
|   1 |  DELETE     | LARGE2 |     |     |   |     |
|*  2 |   TABLE ACCESS FULL| LARGE2 | 999K|  25M| 31892   (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("ID"<1000000)

14 rows selected.

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

999999 rows deleted.

Elapsed: 00:03:23.35
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<1000000;

999999 rows deleted.

Elapsed: 00:40:21.74

SQL> commit;

Commit complete.

Round 2:

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:29.88
SQL> exec dbms_stats.gather_schema_stats('');

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.85

SQL> set timing off

SQL> explain plan for 
  2  delete from large1 where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040529653

-------------------------------------------------------------------------------
| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |       |  1000K|    25M|  2249 (1)| 00:00:27 |
|   1 |  DELETE    | LARGE1    |       |       |     |       |
|*  2 |   INDEX RANGE SCAN| LARGE1_PK |  1000K|    25M|  2249 (1)| 00:00:27 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("ID"<2000000)

14 rows selected.

SQL> explain plan for 
  2  delete /*+ full(t) */ from large2 t where id<2000000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2228625945

-----------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |     |  1000K|  25M| 31874   (1)| 00:06:23 |
|   1 |  DELETE     | LARGE2 |     |     |   |     |
|*  2 |   TABLE ACCESS FULL| LARGE2 |  1000K|  25M| 31874   (1)| 00:06:23 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("ID"<2000000)

14 rows selected.

SQL> set timing on
SQL> alter system flush buffer_cache;

System altered.

SQL> delete from large1 where id<2000000;

1000000 rows deleted.

Elapsed: 00:03:35.38
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> delete /*+ full(t) */ from large2 t where id<2000000;

1000000 rows deleted.

Elapsed: 00:45:13.68
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

SQL> select /*+ full(t) */ count(*) from large2 t;

  COUNT(*)
----------
   1979001

Elapsed: 00:00:14.37

Round 3: table without index

SQL> drop index large2_pk;

Index dropped.

Elapsed: 00:00:00.86

SQL> drop index large2_n1;

Index dropped.

Elapsed: 00:00:00.36

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.25

SQL> delete /*+ full(t) */ from large2 t where id<3000000;

1000000 rows deleted.

Elapsed: 00:02:50.25

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01