Database SQL; Delete vs Truncate and segment size allocated

This article shows the differences between SQL commands delete and truncate in Oracle Database with segment size management.

Table of contents:

1. Delete
2. Truncate
3. Delete vs Truncate and segment size allocated

1. Command Delete:

You can delete the rows of a table using the DELETE statement. For example, the following statement deletes all rows from the tab_test table:

SQL> DELETE FROM tab_test;

If there are many rows present in a table or cluster when using the DELETE statement, significant system resources are consumed as the rows are deleted.

2. Commande TRUNCATE

You can delete all rows of the table using the TRUNCATE statement. For example, the following statement truncates the tab_test table:

SQL> TRUNCATE TABLE tab_test;

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

3. DELETE vs TRUNCATE; segment size allocated

step 1:

I want to create a new user user_test with sys user for test the diffrence beween the command delete and the command truncate.;

SQL> create user user_test identified by ut account unlock;

Create a new role “role_test”:

SQL> create role role_test;
SQL> grant create session to role_test;
SQL> grant create table to role_test;
SQL> grant create view to role_test;
SQL> grant create sequence to role_test;
SQL> grant create synonym to role_test;
SQL> grant create procedure to role_test;
SQL> grant create trigger to role_test;
SQL> grant create type to role_test;
SQL> grant create any index to role_test;
SQL> grant create job to role_test;
SQL> grant create role to role_test;
SQL> grant create user to role_test;
SQL> grant create any directory to role_test;
SQL> grant create public synonym to role_test;
SQL> grant drop public synonym to role_test;
SQL> grant debug connect session to role_test;
SQL> grant debug any procedure to role_test;
SQL> grant alter session to role_test;
SQL> grant execute any procedure to role_test;
SQL> grant execute on sys.dbms_stats to role_test;
SQL> grant query rewrite to role_test;
SQL> grant manage scheduler to role_test;
SQL> grant select on v_$parameter TO role_test;
SQL> grant role_test to user_test;
SQL> alter user role_test default tablespace users temporary tablespace temp quota unlimited on users;

Connect with the new user “user_test”;

SQL> conn user_test/ut;

Create a table named “tab_test“;

SQL> create table tab_test (id number,string varchar2(20));

When we consult the view dba_segments ( dba_segments describes the storage allocated for all segments in the database.):

Note: use the column format for more arrangment

SQL> col SEGMENT_TYPE format a12
SQL> col SEGMENT_NAME format a12
SQL> col OWNER format a12

Step 2:

Insert a line to our table:

SQL> insert into tab_test values (1, 'AAAAAAAAAAA');
1 row created.

Consult the view dba_segments:

 

DBA_SEGMENTS describes the storage allocated for all segments in the database.

SEGMENT_NAME			Name of the segment
SEGMENT_TYPE			Type of segment: TABLE, INDEX…
BYTES			Size in bytes, of the segment
BLOCKS			Size, in Oracle blocks, of the segment
EXTENTS			Number of extents allocated to the segment

So in this case Oracle server reserved one extent with 65536 Bytes witch composed on 8 blocks.

Step 3:

Now, I want to repeat the insert command many times:

SQL> Insert into tab_user select * from tab_test;

Then, create another table with the name tab_test_2, which is it a copy of the table tab_test;

 

 

Note 1: Check tables

 

 

 

 

 

Note 2: Check segment size from the view dba_segments

Step 4: using delete and truncate

Using the command delete on tab_test

 

 

 

The command truncate on tab_test_2

 

 

 

Consult the view dba_segments:

 

Benefits of truncate vs delete:

–          Truncate :

• Remove all lines (does not support conditions)
• Much faster
• Deallocates all space
• DDL (no rollback)
• Reset HWM

–          Delete :

• Remove specific/all lines (support conditions like where)
• DML (rollback possible)
• Slow
• Does not reset HWM

Note: High water mark (HWM)

High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used

Conclusion:

This article shows the difference between the command SQL detele and truncate and size of the segment allocated after the execution of those commands.