How to Write Delete Statement In Oracle

How to Write Delete Statement In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This tutorial page is based on examples to make it easier to follow. Oracle Delete statement does remove a line or many lines from Oracle database table or an Oracle object. The syntax of the delete operator is:

DELETE FROM <table> WHERE <your condition>;

For example we are going to use table old_users and to remove all lines from that table we will use the following Oracle delete command:

DELETE FROM old_users;

oracle dba oracle database management system management database management system oracle performance tuning oracle delete oracle delete in oracle sql delete in oracle sql oracle drop table oracle dba oracle truncate table oracle

On the example output above it shows that we just deleted 4221 lines from table old_users and since we did not apply any restrictions on the statement it took all possible lines which is 4221.

To add some restrictions and to avoid deleting all lines from your Oracle table use the WHERE keyword and set some SQL conditions. For example the Oracle DBA will remove only users with name John on the next example.

DELETE FROM old_users 
 WHERE name = 'John';

oracle dba oracle database management system management database management system oracle performance tuning oracle delete oracle delete in oracle sql delete in oracle sql oracle drop table oracle dba oracle truncate table oracle

This time the Oracle delete statement returned “0 rows deleted” and that does mean no lines were found either removed from the table. The reason could that there wasn’t any user with named John or the table is empty.

Sometime the tables can have more than million lines and the Oracle delete statement takes very long time to finish. In case you would need to remove all lines in a table we suggests to use another command similar to Oracle Delete but named as Oracle Truncate Table. Please keep in mind that truncate table in Oracle database not only removes all lines but as well does commit and all your transactions will be saved. The syntax of Oracle truncate table is:

TRUNCATE TABLE <table_name>;

In the following Oracle DBA example we are going to remove all lines in table old_users using TRUNCATE TABLE operator.

TRUNCATE TABLE old_users;

oracle dba oracle database management system management database management system oracle performance tuning oracle delete oracle delete in oracle sql delete in oracle sql oracle drop table oracle dba oracle truncate table oracle

The Oracle truncate table does not return a number of delete lines, but it should be more quicker than Delete Oracle. As always “quicker” can be sometimes be not quick enough and it can stay slow with even huge amount of data. The solution for slow Truncate table in Oracle can be to use Oracle Drop Table statement. The Drop Table Oracle syntax is:

DROP TABLE <table_name>;

The following method described by Oracle DBA is using table old_users and we will go through step by step the process of the fast removing lines using Oracle Drop Table operator.

  1. Rename table “old_users” to “old_users_drop” – to keep the timeout small just rename it
  2. Create new table “old_users” with the same syntax as it was originally
  3. Drop all indexes, constraints, triggers etc for “old_users_drop”
  4. Create all indexes, constraints etc to fresh table “old_users”
  5. Recompile all objects to make sure all code and objects are valid again
  6. Drop table “old_users_drop”

If you need to delete only part of a huge table’s data you would need to re-design your Oracle table structure and start using the Oracle table partition method. Read more about oracle partition and table partitioning from here.

To describe the Oracle table partition concept we will use again table “old_users” and for example it has column “ACTIVE_YEAR” as “Number(4) Not NULL“. All our users have the column filled with a year number i.e. “2012” and “2011” and the table is partitioned by the “ACTIVE_YEAR” column. The Oracle partition names are “YEAR_2012” and “YEAR_2011“. To remove all users from year 2011 there are the following ways to do it:

Using oracle delete command and applying it directly to partition:

DELETE FROM old_users PARTITION (YEAR_2011);

Also you can truncate table partition using Oracle Truncate Partition command and the additional keyword “DROP STORAGE” will free the allocated space:

ALTER TABLE old_users
   TRUNCATE PARTITION YEAR_2011 DROP STORAGE;

While you are dropping Oracle partitions you are amending the table construction and some global objects over all partitions can become invalid, so keep in mind to check the global indexes and rebuild after doing it.

ALTER TABLE old_users DROP PARTITION YEAR_2011;

See Also:
Oracle Select Oracle Rownum Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*