Oracle Database Tips by Donald Burleson

The DELETE Command  

You can delete data selectively or delete all of a table's data using the DELETE command. The format for this command follows.

See Code Depot

where the clauses have the following definitions:

•    hint. Any one of the allowed hints.

•    schema. The schema or owner of the table, view, or partition being deleted from. If this is left off, the user's default schema is used.

•    Table_name, view_name, or materialized_view. The name of the table, view, or materialized view to be deleted from.

•    dblink. If the table, view, or partition is in a remote database, this is the dblink to that database.

•    PARTITION(partition name). Deletes from a specified (partition_name) of a partitioned table.

•    SUBPARTITION (subpartition_name). Deletes from a specified (subpartition_name) of a subpartitioned table.

•    TABLE. Used to flatten nested tables. The subquery following the TABLE clause tells Oracle how the flattening should occur.

•    subquery. Used to tell Oracle how to delete from the table or nested table. If the deletion is from a nested table, the TABLE clause must be included.

•    alias. Used when a correlated subquery is used to denote table hierarchy in the query/delete commands.

•    WHERE condition. The condition each deleted row must meet or fail.

•    Returning_clause. Retrieves the rows affected by the DELETE statement. You can retrieve only scalar, LOB, rowid, and REF types. 

TIP  

You can use hints in a DELETE statement to optimize delete subquery processing.

The table name can include an alias; if the WHERE clause is left out, all of the rows in the table are deleted. Four examples follow:

DELETE FROM PER_DBA.JOBS A WHERE A.JOB_STATUS = 'COMPLETE';      

This command would delete all rows with the data value COMPLETE in the column JOB_STATUS from the JOBS table owned by the PER_DBA user.

DELETE PER_DBA.OLD_JOBS      

This command would remove all rows from the table OLD_JOBS that belongs to the schema PER_DBA.

www.dba-oracle.com/oracle_scripts.htm