Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

The Difference Between DELETE And TRUNCATE in Oracle – Querychat

In this article, we will discover the syntax of the DELETE and TRUNCATE commands.. Also, we will find out the difference between these two commands.

The Oracle DELETE command syntax

This command allows us to delete one specific row (or more) from our table based on a condition. We can also delete all the rows from our table using this command.

The syntax of this command is:

DELETE

FROM

    our_table_name

WHERE

    our_condition;

Where:

  • our_table_name is the table name we want to delete data in.
  • our_condition is what condition the row must meet to be deleted.

Note: The condition is optional and we don’t have to specify it.

The Oracle TRUNCATE command syntax

Using this command, we can delete all the records from our Oracle table at once without the condition specification.

The syntax is:  

TRUNCATE TABLE [schema.] our_table_name  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]  [ DROP STORAGE | REUSE STORAGE ] ;

Where: 

  • schema is the name of the schema our table is related to.
  • our_table_name is the table name we want to truncate data in.
  • MATERIALIZED VIEW LOG option means that the materialized view log will be saved or cleaned after the table is truncated.
  • STORAGE option means that we can deallocate or remain located the storage used for rows of the table.

TRUNCATE vs. DELETE

DELETE command is good for deleting some specific rows of the table which meets the conditions. When we need to delete all the table contents, it is better to use the command that exists specifically for this, the TRUNCATE command.

DELETE TRUNCATE
It locks the rows when they are assigned for deletion. Locks the table instead of separate rows.
We can specify the requirements for deletion using the WHERE clause. We cannot specify the conditions for deletion, and thus, we can only delete all rows from the table.
It writes down all the actions to the log, so it acts slower. Deletes the rows one by one and writes every action to the log. This is why it uses more space in the log. Acts faster, as it uses less space in the log. Deletes data, freeing up data pages used to store table data. Only freed pages are written to the log.
Before deleting something in the table we need to have the DELETE permission to do this. ALTER permission is needed.
Works with indexed views Doesn’t work with indexed views

Table 1. DELETE and TRUNCATE difference

Examples

Let’s create a sample table to test the DELETE and TRUNCATE commands. 

The script will be: 

CREATE TABLE SomeWeek( DayId INTEGER PRIMARY KEY,

   Day_of_the_week VARCHAR (10) NOT NULL);

And also we will add some records: 

INSERT INTO SomeWeek VALUES (1, 'Monday');

INSERT INTO SomeWeek VALUES (2, 'Tuesday');

INSERT INTO SomeWeek VALUES (3, 'Wednesday');

INSERT INTO SomeWeek VALUES (4, 'Thursday');

INSERT INTO SomeWeek VALUES (5, 'Friday');

INSERT INTO SomeWeek VALUES (6, 'Saturday');

INSERT INTO SomeWeek VALUES (7, 'Sunday');

Result:

Figure 1. “SomeWeek” table

Example 1.

To get rid of all the records with the DELETE command  in our table, we will use a script:

DELETE FROM SomeWeek;

Result:

Figure 2. Deleting the rows from “SomeWeek” table

Example 2.

To get rid of specific rows using the DELETE command, we will use a script: 

DELETE FROM SomeWeek WHERE DayId = 2; 

Result:

Figure 3. Deleting a specific row from “SomeWeek” table

Example 3.

To clear all the records in our table with the TRUNCATE table command, we will use the script:

TRUNCATE TABLE SomeWeek;

Result: 

Figure 4. Truncating the “SomeWeek” table

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc