Informix Dynamic Server V10 . . . Extended Functionality for Modern Business

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.