11G Truncate new Feature


Traditionally, running a truncate command results in removing all the segments allocated to the given table and the command is auto-commit. Even in Oracle 11g and 12c the behavior of truncate remain same but before I talk about new option of truncate command in these releases, let us see quickly how it works:

Truncate can be run with basically two options in release prior to Oracle 11gR2 and same can be run with three options starting Oracle 11gR2.

  1. Truncate table .. drop storage
  2. Truncate table .. reuse storage
  3. Truncate table .. drop all storage (Available from Oracle 11gR2 onwards)

Truncate table .. drop storage

This is how truncate has been working all these days. You have a table and you want to remove all data from it, you get truncate table as first option. When you use this option, any extents allocated to the table is removed and HWM (High water mark) will be reset which, in simple terms, resets the table size.

Let us see that:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL:SCOTT@tp11g>create table test_trunc as select rownum as rn,lpad('test',10,'*') name from dual connect by level <=1000;
Table created.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
     65536
SQL:SCOTT@tp11g>insert into  test_trunc select rownum as rn,lpad('test',10,'*') name from dual connect by level <=100000;
100000 rows created.
SQL:SCOTT@tp11g>commit;
Commit complete.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
   3145728
SQL:SCOTT@tp11g>truncate table test_trunc;
Table truncated.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
     65536
SQL:SCOTT@tp11g>

This example clearly prove that all the extents allocated to TEST_TRUNC is removed after the truncate command execution. This is why, it is always preferred to use TRUNCATE command in place of DELETE when all the records are to be deleted. Running a truncate command with/without DROP STORAGE clause is same because this is the default behavior of truncate command. Let us see that .

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL:SCOTT@tp11g>insert into  test_trunc select rownum as rn,lpad('test',10,'*') name from dual connect by level <=100000;
100000 rows created.
SQL:SCOTT@tp11g>commit;
Commit complete.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
   3145728
SQL:SCOTT@tp11g>truncate table test_trunc drop storage;
Table truncated.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
     65536
SQL:SCOTT@tp11g>

Truncate table .. reuse storage

Though DROP STORAGE is very good option but what if you have very large table and you dont want to remove the storage but at the same time you want to delete all records. You might pitch for DELETE but the problem is delete generates redo and undo which slows it down compared to truncate which does not generate redo and undo. In such cases REUSE STORAGE clause of TRUNCATE command comes handy. Let us see that:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SQL:SCOTT@tp11g>insert into  test_trunc select rownum as rn,lpad('test',10,'*') name from dual connect by level <=100000;
100000 rows created.
SQL:SCOTT@tp11g>commit;
Commit complete.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
3145728
SQL:SCOTT@tp11g>truncate table test_trunc reuse storage;
Table truncated.
SQL:SCOTT@tp11g>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
3145728
SQL:SCOTT@tp11g>select count(*) from test_trunc;
COUNT(*)
----------
0
SQL:SCOTT@tp11g>

Even though table does not contain any data, the size of the table does not change which in some cases good because at the time of loading you dont have to wait for allocation of new extents. Very nice stuff. Right? But consider another scenario: You have a table with some extra segments created for them say for example, a table with LOB datatype creates LOB segments apart from the table or segments allocated for primary key indexes. Such segments are never released with any of the above options but wouldn’t it be a good idea to release such segments also at the time of truncating the table. This is what Oracle 11g R2 offers. Oracle 11g R2 offers a new clause DROP ALL STORAGE which drops all the related segments for the given table.

Truncate table .. drop all storage (Available from Oracle 11gR2 onwards)

Oracle 11gR2 offers new option with Truncate command i.e. DROP ALL STORAGE. This removes not only data and resets the HWM of the table but it also releases any segments created (indexes, constraints, Lob segments etc) for the table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
SQL:SCOTT@tp11gr2>create table test_trunc (sr number primary key, bio_data clob) segment creation immediate;
Table created.
SQL:SCOTT@tp11gr2>select segment_name,segment_type from user_segments;
SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
DEPT                                                                              TABLE
EMP                                                                               TABLE
SALGRADE                                                                          TABLE
TEST_TRUNC                                                                        TABLE
PK_DEPT                                                                           INDEX
PK_EMP                                                                            INDEX
SYS_IL0000075713C00002$$                                                          LOBINDEX
SYS_C0011207                                                                      INDEX
SYS_LOB0000075713C00002$$                                                         LOBSEGMENT
9 rows selected.
SQL:SCOTT@tp11gr2>insert into test_trunc select rownum as sr,lpad('this is test data',100,'*') from dual connect by level <=10000;
10000 rows created.
SQL:SCOTT@tp11gr2>commit;
Commit complete.
SQL:SCOTT@tp11gr2>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
   2097152
SQL:SCOTT@tp11gr2>truncate table test_trunc ;
Table truncated.
SQL:SCOTT@tp11gr2>select sum(bytes) from user_extents where segment_name='TEST_TRUNC';
SUM(BYTES)
----------
     65536
SQL:SCOTT@tp11gr2>select segment_name,segment_type from user_segments;
SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
DEPT                                                                              TABLE
EMP                                                                               TABLE
SALGRADE                                                                          TABLE
TEST_TRUNC                                                                        TABLE
PK_DEPT                                                                           INDEX
PK_EMP                                                                            INDEX
SYS_IL0000075713C00002$$                                                          LOBINDEX
SYS_C0011207                                                                      INDEX
SYS_LOB0000075713C00002$$                                                         LOBSEGMENT
9 rows selected.
SQL:SCOTT@tp11gr2>truncate table test_trunc drop all storage;
Table truncated.
SQL:SCOTT@tp11gr2>select segment_name,segment_type from user_segments;
SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
DEPT                                                                              TABLE
EMP                                                                               TABLE
SALGRADE                                                                          TABLE
PK_DEPT                                                                           INDEX
PK_EMP                                                                            INDEX
SQL:SCOTT@tp11gr2>

As you can see, after running truncate command with DROP ALL STORAGE clause, the segments are removed and hence TEST_TRUNC does not appear in the output. The first segment will be allocated at the time of very first insert statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL:SCOTT@tp11gr2>insert into test_trunc select rownum as sr,lpad('this is test data',100,'*') from dual connect by level =1;
1 row created.
SQL:SCOTT@tp11gr2>select segment_name,segment_type from user_segments;
SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
DEPT                                                                              TABLE
EMP                                                                               TABLE
SALGRADE                                                                          TABLE
TEST_TRUNC                                                                        TABLE
PK_DEPT                                                                           INDEX
PK_EMP                                                                            INDEX
SYS_IL0000075713C00002$$                                                          LOBINDEX
SYS_C0011207                                                                      INDEX
SYS_LOB0000075713C00002$$                                                         LOBSEGMENT
9 rows selected.
SQL:SCOTT@tp11gr2>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s