Deletion of Table Data Oracle Tips by Burleson Consulting

Deletion of a table’s data can be done by two methods. The first method uses the DELETE command and can be conditional. A DELETE is also able to be rolled back if you decide, before you commit, that you goofed. The second method is the TRUNCATE command. TRUNCATE is a DDL, not a DML, command, and it’s not conditional (it’s all or nothing) and cannot be rolled back (yes, you will spend days reentering data if you goof). Let’s look at these commands and their options.

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.

DELETE hint [FROM] [ONLY]
[TABLE select_clause][(+)]
[schema.]table_name|view_name|materialized_view[@db_link]
 sub_query [WITH READ ONLY|CHECK OPTION [CONSTRAINT con_clause]] alias
[WHERE expr]
[schema.]table_name [PARTITION (partition_name)]
[schema.]table_name [SUBPARTITION (subpartition_name)]
[returning clause]

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_Remote 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_Remote DBA user.

DELETE PER_Remote DBA.OLD_JOBS

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

To delete specific rows from a nested table, the TABLE clause is specified (I like to think ‘FROM THE SET’ when I see TABLE):

DELETE TABLE (SELECT addresses
 FROM clientsv8 c
 WHERE c.customer_name = 'Joes Bar and Grill, Inc.')
 AS a
WHERE a.addrtype=1;

In this example, note the use of the alias “AS a”; this is required.

Deleting from a single partition is accomplished by use of the PARTITION clause:

DELETE FROM trains PARTITION (roundhouse1)
WHERE 
service_date < to_date('01-Jan-1956 00:00:00,
'DD-Mon-YYYY hh24:mi:ss');
 

If you wish to delete from a specific subpartition, this is accomplished with the SUBPARTITION clause:

DELETE FROM trains SUBPARTITION (subroundhouse1a)
WHERE 
service_date < to_date('01-Jan-1956 00:00:00,
'DD-Mon-YYYY hh24:mi:ss');

The TRUNCATE TABLE Command  

There is also a way to avoid the use of rollback, thus making deletions much faster: use the TRUNCATE command. One good feature of this command is that it can be used to reclaim the space used by the data that was in the table. As noted in the introduction to this section, TRUNCATE is a DDL command; once issued, the data is gone. A TRUNCATE cannot be rolled back. The format for this command follows.

TRUNCATE TABLE|CLUSTER [schema.]table_name|cluster_name
[PRESERVE|PURGE SNAPSHOT LOG]
[DROP|REUSE STORAGE] 

The DROP|REUSE STORAGE option allows you to shrink the table back to its high-water mark or leave the table at its current size. Both DROP and REUSE qualifiers also apply to whatever index space is regained.

For tables, PRESERVE or PURGE SNAPSHOT options allow control over a table’s snapshot logs as well.

Again, the TRUNCATE command is faster than the DELETE command because it is a DDL command and generates no rollback data. When using TRUNCATE on a clustered table, the data must be removed from the entire cluster, not just the one table. Any referential integrity constraints on a table must be disabled before it can be truncated. Like a table DROP, a truncation is not recoverable. If a table is truncated, you cannot roll back if you made a mistake. Use TRUNCATE carefully.

Dropping a Table  

To completely remove a table from the tablespace, use the DROP TABLE command. This command’s format follows:

DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

Oracle will drop the table regardless of its contents. The only time a drop will fail is when a table’s primary key is referenced by another table’s foreign key via a restraint clause. The Remote DBA can check for this situation by looking at the Remote DBA_CONSTRAINTS and the Remote DBA_CONS_COLUMNS views. A view called USER_CROSS_ REFS provides this information on a user-by-user basis. Using the CASCADE CONSTRAINTS clause will force a CASCADE DELETE to occur in all child tables.

Rebuilding Tables  

The Remote DBA may have to rebuild a table or tables after maintenance, after a physical disk crash, or--the leading cause--due to operational stupidity. If the application designers were thoughtful enough to provide a build script, there is no problem. However, for legacy systems, systems that have been modified and not redocumented, or systems created on the fly, there may be no current build scripts, if there were any to begin with. In this case, the Remote DBA is in trouble. How can this situation be prevented? Require build scripts for each application and keep them up to date. For existing, undocumented systems, the script TAB_RCT9i.sql from the Wiley Web site will create a build script for simple existing tables. For very complex tables, I suggest implementing a utility such as TOAD from Quest, Inc. In Oracle9i, the DBMS_METADATA can also be used to generate the DDL for any database object. Any rebuild script generator must be run before any loss has occurred. Due to the added complexity of Oracle9i, it is doubtful many systems created on the fly will that make use of the complex options and types. Over time, I will be revising TAB_RCT9i.SQL to handle the more complex Oracle9i structures, and newer versions will be made available at the Wiley Web site.

A similar script is referenced for indexes in Chapter 6. The TAB_RCT9i.SQL script will rebuild straight-partitioned or subpartitioned Oracle9i and Oracle8i relational tables only. It will not rebuild tables with types, objects, nested tables, or varrays.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.