Friday, November 19, 2010

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