Removing Rows from Tables

Depending on whether you want to remove all rows from a table or selected rows, you might use the methods described in the following sections.

Removing All Rows with the TRUNCATE TABLE Statement

Use the TRUNCATE TABLE statement to remove all rows from a table in a single operation and leave it ready for new content. The TRUNCATE TABLE statement is a quick way to prepare a table into which you plan to load completely new data.

When it executes the TRUNCATE TABLE statement, the database server executes a single transaction that includes a commit and writes only three records to the logical log. When the database server removes rows from the table, it frees the old extents and allocates a new extent immediately. It also removes the contents of all indexes and re-creates the indexes when the table is populated again.

To execute the TRUNCATE TABLE statement, you must be the owner of the table or have DBA privileges. You cannot use the TRUNCATE TABLE statement to remove rows from an external table or from any of the internal database tables, such as the system catalog tables or the violation tables.

The TRUNCATE TABLE statement provides the following performance advantages:

• It removes rows from a table without changing access privileges so that you do not have to re-create the table definition and assign access privileges to users.
• It does not use a time-consuming row-by-row delete operation, which also adds a record to the logical log for each deleted row, greatly increasing the size of the logical log file.

Although the database server can roll back an unsuccessful TRUNCATE TABLE operation, it cannot roll back a successful TRUNCATE TABLE operation because it is committed immediately.

Removing Selected Rows with a Delete Join

The DELETE....USING statement lets you join two tables and delete rows from one table based on a WHERE clause that compares column values in both tables. The performance of a single statement to join tables A and B and perform a conditional delete is better than the performance of two statements, the first of which selects rows and the second of which deletes the selected rows. In addition to improving processing performance, DELETE...USING statement simplifies the syntax required to perform the operation.

For example, to delete rows in the open table based on a comparison with certain columns in the bills table, you might enter the following statement:

DELETE FROM open USING open, bills 
   WHERE open.cust_no = bill.custno
   AND open.paid_date > bills.bill_date
   AND open.pd_amt = bills.bill_amt;

The only table listed in the FROM clause is the table from which rows will be deleted, but all joined tables must be listed in the USING clause. Outer joins are not allowed.

The DELETE...USING statement performs its actions in two steps. First it joins the tables and creates a temporary table based on all rows for which the WHERE clause evaluates to true. Then it deletes the matching rows in the target table.

For a complete description of the DELETE... USING statement, refer to the IBM Informix: Guide to SQL Syntax.