• Oracle Truncate Date
• Oracle Truncate Command
• Oracle Truncate Delete
• Oracle Truncate
• Oracle Truncate Partition

What’s the difference between truncating, deleting, and dropping a table in SQL? Find out in this article.

There are a lot of ways to delete data in SQL, including the DELETE, TRUNCATE TABLE and DROP TABLE commands. Which one should you use in a given situation?

Oracle Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway. However, see Flashback below. Starting with Oracle 11gR2 (11.2.0.2), a TRUNCATE statement can also specify the DROP ALL STORAGE clause to release the space currently allocated for a table to the containing tablespace. Impdp truncate date imported in timestamp Hi,I created a table with a date column in an Oracle 18 database and I filled it with some date values: create table testdate (col1 date); insert into testdate values (sysdate); insert into testdate values (sysdate); select. from testdate; COL1. TRUNC is an Oracle function used for truncating the value up to n decimal places after the decimal point. If we specify n as 0 then truncation happens only up to the digit before the decimal point. Unlike the ROUND function, TRUNC function simply truncates the value and makes no changes to the value itself.

In this article, you’ll learn the syntax of each command in different database engines like MySQL, PostgreSQL, SQL Server, and Oracle. And you’ll understand the DROP TABLE vs. DELETE vs. TRUNCATE TABLE debate.

Let’s get started!

DELETE

DELETE is a DML (Data Manipulation Language) command. This command removes records from a table. It is used only for deleting data from a table, not to remove the table from the database.

You can delete all records with the syntax:

Or you can delete a group of records using the WHERE clause:

If you’d like to remove all records from a given table, use DELETE FROM followed by the table name. Notice that there aren’t any column names in this syntax; you’re removing all records, not the data in selected columns.

If you want to remove specific records, use WHERE with filtering conditions, as in the second example.

Oracle Truncate Date

Let’s use these commands in an example. Here’s the table product:

Oracle Truncate Command

However, DELETE uses a row lock during execution and can be rolled back. Every deleted row is locked, so it will require a lot of locks if you’re working in a large table.

DELETE also keeps the auto-increment ID in the table. If you remove the last record in the table with ID=20 and then add a new record, this record will have ID=21 – even though the record immediately before it will be ID=19.

DELETE can be executed by triggers. A trigger can be called before, after, or instead of the DELETE operation. It can be executed for any row change or when all rows are removed. Removing rows in another table can also trigger DELETE.

Of course, to use the DELETE command you need DELETE permission for that table.

TRUNCATE TABLE

TRUNCATE TABLE is similar to DELETE, but this operation is a DDL (Data Definition Language) command. It also deletes records from a table without removing table structure, but it doesn’t use the WHERE clause. Here’s the syntax:

If you use this command, all rows in this table will be removed. The following query ...

… deletes all records stored in the table product.

How does TRUNCATE TABLE work?

Be careful using this command. TRUNCATE transactions can be rolled back in database engines like SQL Server and PostgreSQL, but not in MySQL and Oracle.

TRUNCATE is faster than DELETE, as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE.

Unlike DELETE, TRUNCATE does not return the number of rows deleted from the table. It also resets the table auto-increment value to the starting value (usually 1). If you add a record after truncating the table, it will have ID=1. Note: In PostgreSQL, you can choose to restart or continue the auto-increment value. Oracle uses a sequence to increment values, which is not reset by TRUNCATE.

Of course, you need permission to use TRUNCATE TABLE. In PostgreSQL, you need the privilege TRUNCATE; in SQL Server, the minimum permission is ALTER table; in MySQL, you need the DROP privilege. Finally, Oracle requires the DROP ANY TABLE system privilege to use this command.

You can learn more in the course “The Basics of Creating Tables in SQL”, which is part of our Data Engineering Path.

DROP TABLE

The DROP TABLE is another DDL (Data Definition Language) operation. But it is not used for simply removing data from a table; it deletes the table structure from the database, along with any data stored in the table.

Here is the syntax of this command:

All you need after DROP TABLE is the name of the table you want to delete. For example, if you’d like to remove the entire product table from the database, you’d write:

This removes all data in the table product and the structure of the table.

How does DROP TABLE work?

The DROP TABLE operation removes the table definition and data as well as the indexes, constraints, and triggers related to the table.

This command frees the memory space.

No triggers are fired when executing DROP TABLE.

This operation cannot be rolled back in MySQL, but it can in Oracle, SQL Server, and PostgreSQL.

In SQL Server, DROP TABLE requires ALTER permission in the schema to which the table belongs; MySQL requires the DROP privilege; Oracle the requires the DROP ANY TABLE privilege. In PostgreSQL, users can drop their own tables.

DROP TABLE vs. DELETE TABLE vs. TRUNCATE TABLE in SQL

Which cases call for DROP TABLE? When should you use TRUNCATE or opt for a simple DELETE? We’ve prepared the table below to summarize the properties of each command:

In SQL, DELETE, TRUNCATE TABLE, and DELETE TABLE all have pluses and minuses. Hopefully, this article has helped you understand when and how to use each command.

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function (as it applies to numeric values) with syntax and examples.

Oracle Truncate Delete

Description

The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

Oracle Truncate

Syntax (with numbers)

The syntax for the TRUNC function in Oracle/PLSQL is:

Parameters or Arguments

number

The number to truncate.

decimal_places

Optional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.

Returns

The TRUNC function (as it applies to numbers) returns a numeric value.

Applies To

The TRUNC function can be used in the following versions of Oracle/PLSQL:

Oracle Truncate Partition

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example (with numbers)

Let's look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.

For example: