Chapter 3. The SQL language 79
3.2.3 The basic truncation
Like any delete function, to execute TRUNCATE you must have an exclusive
access to that table. TRUNCATE is very fast and efficient in scenarios where the
table contains large amounts of data. This is because the deletion does not
happen on individual rows, which means that no delete triggers are fired. So any
logging of activity or other recording done in a trigger does not occur. This is one
of the main reasons why TRUNCATE cannot be used on tables that are a part of
Enterprise Replication (ER) but can be used with High Availability Data
Replication (HDR).
Example 3-4 shows the truncate statement in action. The examples in this
section all use a table called
dual, which has just one column and a single row of
data. TRUNCATE is not a large operation for a table with just one column and
one row. However, the operation is very fast on table of any size because the
individual rows are not deleted.
Example 3-4 Preparing the TRUNCATE data
> CREATE TABLE dual ( c1 INTEGER);
Table created.
> TRUNCATE dual;
Table truncated.
> SELECT * FROM dual;
c1
No rows found.
> INSERT INTO dual VALUES (1);
1 row(s) inserted.
> SELECT * FROM dual;
c1
1
1 row(s) retrieved.
80 Informix Dynamic Server V10 . . . Extended Functionality for Modern Business
The statistics about the table are not changed. So the number of rows and
distribution of values that were recorded in the most recent update statistics are
retained. Example 3-5 shows that the systables.nrows count is not affected by
truncating the table.
Example 3-5 TRUNCATE and table statistics
> SELECT tabname, nrows FROM systables WHERE tabname = 'dual';
tabname dual
nrows 0
1 row(s) retrieved.
> UPDATE STATISTICS FOR TABLE dual;
Statistics updated.
> SELECT tabname, nrows FROM systables WHERE tabname = 'dual';
tabname dual
nrows 1
1 row(s) retrieved.
> TRUNCATE dual;
Table truncated.
> SELECT tabname, nrows FROM systables WHERE tabname = 'dual';
tabname dual
nrows 1
1 row(s) retrieved.
> SELECT * FROM dual;
c1
No rows found.
> UPDATE STATISTICS FOR TABLE dual;
Statistics updated.

Get Informix Dynamic Server V10 . . . Extended Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.