Home
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
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle DELETE Tips

Oracle Database Tips by Donald 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>;

Where:

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;

begin

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'));

end;

/

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:

DELETE FROM HISTORY where ALERT_TIME <= :b1 AND INSTANCE_NUMBER = :b2

Trace Analyzer would also display:

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

which equates to the actual SQL statement of:

DELETE FROM HISTORY where ALERT_TIME <= :"2/4/2003 15:57:35" AND INSTANCE_NUMBER = 1

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.

DELETE FROM SCOTT.EMPLOYEE

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
---------------------------------------------------------------
...3 DELETE STATEMENT
...2 .DELETE OF 'SCOTT.EMPLOYEE
...1 ..TABLE ACCESS (FULL) OF 'SCOTT.EMPLOYEE'

OWNER.TABLE_NAME
...owner.index_name num rows blocks sample last analyzed date
------------------------------------ ---------- ---------- ---------- -------------------
SCOTT.EMPLOYEE..........................

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;

AUTHOR_KEY
-----------
A102
A104
A105
A106
A107
A108
A109
A110

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

��