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

 
 Home
 E-mail Us
 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 s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle DELETE SQL Tips


Oracle Tips by Burleson Consulting

 

Using Oracle DELETE Statement

The general format for the DELETE statement when using a RETURING 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 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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.