TRUNCATE

Use the TRUNCATE command to remove all rows from a table. This has the same effect as the DELETE command, but is faster than the DELETE command for large tables.

You can use TRUNCATE within a transaction or stored procedure to make other related changes to data in one atomic unit. When you use TRUNCATE in this manner:
  • The truncating transaction can be rolled back, in which case the table storage and contents remain intact.
  • The table is locked, and no concurrent access (including SELECT commands) to the table from other sessions or transactions is permitted until the truncating transaction commits or rolls back. The rollback might happen as a result of a ROLLBACK command or an execution failure in the transaction.
  • If the truncating transaction accesses other tables, there is an increased likelihood of deadlock. You can avoid this problem issuing LOCK TABLE <tablename> IN ACCESS EXCLUSIVE MODE at the beginning of a truncating transaction. See LOCK TABLE for guidelines on the use of that command.
  • TRUNCATE TABLE is much faster than deleting all rows with DELETE FROM <table> and also does not require a subsequent GROOM TABLE to reclaim the space. But it does incur a small risk that concurrent transactions attempting to modify and truncate the same table could see the message ERROR: Cannot truncate table because of concurrent insert or update. You can minimize the risk by using the LOCK TABLE command and by keeping a transaction that truncates one or more tables active for no longer than is necessary to achieve its goal.

NPS supports SELECTs in concurrent transactions against a truncated table. Disk storage for a truncated table is freed only when there are no active concurrent transactions that started before the truncate committed. If there are no such concurrent transactions at the time a truncate commits, the storage is freed a few seconds after the commit. Open transactions that started before the TRUNCATE committed can still see the rows in the table from before the TRUNCATE.

Operations other than SELECT that modify the table being truncated must wait for the truncating transaction to commit or roll back.

If your system often has concurrent sessions that select against a table that is truncated, note that the TRUNCATE command resets the table rowcount statistics to zero. The reset benefits the planning estimates for newer, subsequent queries on the table. Resetting the rowcount statistics on TRUNCATE can impact existing queries from concurrent transactions. If the new rowcount statistics are very different from the rowcounts before the TRUNCATE, the concurrent queries could take longer to run due to the inaccurate planner estimates for the older queries.

If you would prefer to keep the table statistics intact to benefit the in-flight queries, you can configure the system to retain the table statistics and rowcount of the table from before the TRUNCATE. To retain the statistics, you can set the database variable concurrent_truncate_zero_stats to false. This setting causes the system to always retain the table statistics, which increase over time as new rows are inserted to the table and can result in the query estimate problems that can lead to longer runtimes. To change the default behavior:

  1. Log in to the NPS system as the nz user account.
  2. With any text editor, open the /nz/data/postgresql.conf file. Use caution when you edit postgresql.conf. It contains important configuration parameters for the Netezza Performance Server system operation.
  3. Look for a concurrent_truncate_zero_stats definition in the file to make sure that one is not already present and/or commented out. If you find an entry, make sure that concurrent_truncate_zero_stats=false and that it is not commented out. If you cannot find an entry, add concurrent_truncate_zero_stats=false to the file.
  4. Save and close the postgresql.conf file.
  5. Stop and restart the NPS software by using the nzstop and then the nzstart commands.

The concurrent_truncate_zero_stats variable can also be set (or reset) in individual NPS database sessions by using the set concurrent_truncate_zero_stats to <value> command. For example, it is possible to set the variable at the session level to get different behavior for different tables. Different sessions that truncate from the same table should use a consistent setting for this variable to get consistent behavior.

Note: The following practices regarding the use of GENERATE STATISTICS on tables that are repeatedly truncated and reloaded:
  • Do not add a GENERATE STATISTICS after the TRUNCATE (or after the transaction that calls the TRUNCATE commits).
  • Ideally, GENERATE STATISTICS should be run after reloading the data prior to querying. This is especially important if concurrent_truncate_zero_stats is set to false. It is also good general practice for large Performance Server tables because some statistics (such as column value "dispersions") are not collected automatically while loading.
  • If GENERATE STATISTICS is not currently being run after each reload and it is too hard to change the existing workflows to add the command, then in cases where concurrent_truncate_zero_stats is false, you should run GENERATE STATISTICS periodically in a background job. Choose a job frequency so that no more than four or five truncates and reloads have occurred since the last GENERATE STATISTICS.

Syntax

Syntax for truncating an empty table:
TRUNCATE [ TABLE ] <name>

Input

The TRUNCATE command takes the following input:
Table 1. TRUNCATE input
Input Description
<name> The name of the table to be truncated.

Output

The TRUNCATE command has the following output:
Table 2. TRUNCATE output
Output Description
TRUNCATE TABLE The command was successful.

Privileges

You must be the admin user, the table owner, the owner of the database or schema, or your account must have the Truncate privilege on the table or the Table object class.

Usage

The following provides sample usage.
  • Remove all rows from the table bigtable: 
    MYDB.SCH1(USER)=> TRUNCATE  bigtable;
  • Replace the contents of the table mytable with new data:
    BEGIN;
TRUNCATE TABLE mytable;
INSERT INTO mytable SELECT …;
COMMIT;