This Online Tech Support page is based on examples to be easier to follow. The Oracle Delete statement removes a line or lines from Oracle database table or from an Oracle object. The delete operator syntax is:
DELETE FROM <table> WHERE <your condition>;
For example we are going to use table old_users in the Oracle database and to remove all lines from that table we will use the following command:
DELETE FROM old_users;
On the picture above you can see that we deleted 4221 lines from table old_users and since we didn’t apply any restrictions the statement took all possible lines – 4221.
To add some restrictions and avoiding deleting all lines from the Oracle table you should use keyword WHERE and set some conditions. This condition below will remove only users with name John.
DELETE FROM old_users WHERE name = 'John';
As you see the Oracle delete statement returned “0 rows deleted” and that means no lines were removed from the table. The reason is there wasn’t any user named John.
Sometime the tables can have more than million lines and the Oracle delete statement takes very long time to finish. In that case if you need for example to remove all lines in a table we suggest using another command similar to Oracle Delete but named 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 next example we are going to remove all lines in table old_users using TRUNCATE TABLE operator.
TRUNCATE TABLE old_users;
The Oracle truncate table does not return a number of delete lines, but it should be quick enough. As always enough can be sometimes not enough and it can stay slow with even huge amount of data. The solution for slow Truncate table in Oracle would be to use Oracle drop table statement. The drop table Oracle syntax is:
DROP TABLE <table_name>;
The following method described by Online Tech Support is using the Oracle table old_users and goes through step by step of the fast removing lines process using Oracle drop table operator.
- Rename table “old_users” to “old_users_drop” – to keep the timeout small just rename it
- Create new table “old_users” with the same syntax as it was originally
- Drop all indexes, constraints, triggers etc for “old_users_drop”
- Create all indexes, constraints etc to fresh table “old_users”
- Recompile all objects to make sure all code and objects are valid again
- Drop table “old_users_drop”
If you need to delete only part of Oracle table’s data for a huge table 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:
DELETE FROM old_users PARTITION (YEAR_2011);
Also you can truncate table partition using Oracle Truncate Partition command and 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 and some global objects over all partitions can become invalid, so keep in mind that global indexes need to rebuild after doing it.
ALTER TABLE old_users DROP PARTITION YEAR_2011;