Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle DELETE Tips

Oracle tips by Burleson


Using Oracle DELETE Statement

The general format for the DELETE statement when using a RETURNING clause is for a single table delete as shown below.
DELETE <table or expression> <alias> 

WHERE <where_clause>

RETURNING <exprs> INTO <data_items>;
  • Table or expression -- This is a valid table, materialized view, single-table view, or an expression based on one of the above
  • Alias -- A valid alias for the table or expression
  • Where_clause -- This is a valid where clause which may include a subquery
  • Exprs -- A set of expressions based on the affected row
  • Data_items -- A valid set of variables in which to load the values returned by the expressions.

The purpose of the RETURNING clause is to return the rows affected by the DELETE statement. The RETURNING clause can only be used with single tables and materialized views and regular views based on a single table.

When the target of the DELETE is a single row, the RETURNING clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row.

An example DELETE using a subquery in the WHERE statement and the RETURNING clause with a single-set aggregate would be:
set serveroutput on

variable tot_sal number;


delete emp a where a.rowid > (
select min (x.rowid) from emp x
where x.empno=a.empno)
RETURNING sum(a.sal) INTO :tot_sal;

dbms_output.put_line('Total Company Payroll now '||to_char(:tot_sal,'$999,999.00'));


The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement.

For the following SQL statement listed in the output:


Trace Analyzer would also display:

0:"2/4/2003 15:57:35" 1:1

which equates to the actual SQL statement of:


2. Trace Analyzer provides the hottest blocks, optimizer statistics for indexes and tables, and other information not available through tkprof. The output below shows the SQL statement, the execution plan, and statistics for each object in the SQL.


call count cpu elapsed disk query current rows misses
------- ------- ------- -------- -------- --------- --------- ------------ ---------
Parse 1 0.00 0.00 0 0 0 0 0
Execute 3 0.05 0.52 0 27 224 216 0
------- -------- -------- -------- -------- --------- --------- ----------- --------
total 4 0.05 0.52 0 27 224 216 0

Explain Plan

...owner.index_name num rows blocks sample last analyzed date
------------------------------------ ---------- ---------- ---------- -------------------

The output above indicates that the EMPLOYEE table does not have statistics.

Like the UPDATE statement, the DELETE statement removes all rows identified by the WHERE clause. This is another data manipulation command, which means that we can roll back the deleted data, and that to make our changes permanent, we need to issue a commit. We have already looked at a couple of the DELETE formats.

SQL> delete from author;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> delete author;

10 rows deleted.

SQL> rollback;

Rollback complete.

Both commands deleted all the rows in the table. You cannot delete part of a row. If you want to remove some of the data such as setting all columns to NULL except the author_key, you would use the UPDATE statement. The basic format of the command is:

delete from <table name> where <expression>;

Every row that matches the expression will be removed from the table.

SQL> delete from author
2 where
3 author_key in ('A101','A103','A120');

2 rows deleted.

SQL> select author_key from author;


8 rows selected.

Notice that there is no author with an author_key = A120, so only two authors were deleted.

Let's delete the order with the smallest quantity.

SQL> delete from sales
2 where
3 quantity = (select
4 min(quantity)
5 from
6 sales);

1 row deleted.

Any valid WHERE clause is acceptable to identify which rows to delete. I can get the same results as above using a nested query and the order number.

SQL> delete from sales
2 where
3 order_number = (select
4 order_number
5 from sales
6 where quantity = (select
7 min(quantity)
8 from
9 sales))

1 row deleted.

But, what happens when I run the above delete query again?

SQL> delete from sales
2 where
3 order_number = (select
4 order_number
5 from sales
6 where quantity = (select
7 min(quantity)
8 from
9 sales));
order_number = (select
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

What happened? Well, the first time I ran the query, it returned one row because there was only one order number that had the minimum quantity. That order had been deleted. Now, there are many orders that have the new minimum quantity and the query fails. This is an example of writing a query expecting one row, testing it and it working, but then having it fail in other tests. How do I fix it? Change the equals to IN.

SQL> delete from sales
2 where
3 order_number in (select
4 order_number
5 from sales
6 where quantity = (select
7 min(quantity)
8 from
9 sales));

47 rows deleted.

There were 47 order numbers with the new minimum quantity.

Throughout the discussion of INSERTs, UPDATEs and DELETEs, we have seen the ROLLBACK command used to undo the changes. The ROLLBACK command is part of how any database management system implements transactions.

See my notes on tuning Oracle delete statements and more Oracle delete Performance Tips



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.