Things you should know before truncate tables and partitions

"Do you know truncate?" You will defenitely answer Yes, but how much? Here, what I would like to describe is about truncate. Even though truncate is such a simple command, what behind the scene is not not easy to explain. Throughout the past several years of maintaining a large Exadata, I would like to describe some of the lessons learned, in terms of how it may influence performance. Advanced DBAs probably know most of the things described here, but still, I thought it might be useful to write it down as a reference.
Let's start from the basics.

1. Basics of truncate

Truncate is a DDL that deletes all records in a table or a patition. To do that, it will lower the HWM (high water mark), therefore general, it is much faster than DELETE and generate less redo log. Truncate cannot rollback since it is a DDL. When you truncate a scott.emp table, you will do the following:

SQL> truncate table scott.emp;

Truncate frees all allocated extents belonging to the table by default ("drop storage" option). With "reuse storage" option, truncate keeps allocated extents and make the table empty.

SQL> truncate table scott.emp reuse storage;

Concerning a partition level truncate, you can specify options not only to rebuild global indexes with paralellism, as well as reuse storage option described above.

SQL> alter table scott.emp truncate partition part01 reuse storage update global indexes parallel 4;

It is not my intention to describe all the possilbe options of truncate here, but to give impression how such a simple DDL can do various things.

2. Five things you should know about truncate
I will describe five things that it's better to understand about truncate. Even though the behavirour is based on 11gR2, I believe most of the things will be still available on 12c as well.

2.1. Shared cursor is invalidated, then pard parsed

Upon executing truncate on a table, the table's DATA_OBJECT_ID (a object number of the segment) will be changed. Because of this, all shared cursors that relate to the table will be invalidated. When using RAC, this propagets to the other instances. As a result, all such SQLs will be hard parsed on next executions, not only the instance the truncate is executed, but other instances as well.

When you truncate a partition, all the partitions' DATA_OBJECT_ID will be changed, even you truncate a single partition out of thouthand. Therefore, if you truncate a old partition in range partitioned table as a maintenance task during online, there is a chance that online SQL that access to the table will be slow down, even if the query doesn't access the partition truncated.

By the way, the same will happen on DDLs such as ALTER TABLE or ALTER INDEX. To confirm what kind of DDLs make shared cursors INVALID, refer to DocumentID 1731739.1* (* this is written in Japanese, though).

2.2. Table is locked (not a specific partition)

Truncating a partition not only makes related SQLs invalidated, but also acquire "library cache lock" to the table ( not the partition) with exclusive mode. At this point, all SQLs that need to be hard parsed will be blocked because they also try to acquire "library cache lock" with shared mode. It is also worth while noted that if you try to truncate different partitions concurrently, it will be always serialized.

2.3. Mini-checkpoint executed

Truncate will execute mini-checkpoint. Mini-checkpoint will flush dirty blocks of the table in the buffer cache to its storage. While ordinaly checkpoint will deal with all dirty blocks, mini-checkpoint will only deal with relevant blocks of the table. Therefore, truncate may take a long time when the number of dirty blocks is large. This behaviour will be observed as excessive amount of a wait event "enq: RO fast object reuse".

2.4. reuse storage option may be slower

You may think truncate with reuse storage option faster than drop storage option because it doesn't have to free the extents, especially for large segments. However, it may not be true. With reuse storage option, truncate needs to mark all extents as free and takes time depending on the segment size. In fact, in my experience, trunate with resuse storage option was slower than drop storage option (10-20 sec vs 3 sec) in Exadata X4, while INSERT performance hardly have any difference. Since I think this may not be always true depending on its environment, it is a good idea to verify before deciding which is better on your environment.

2.5. Global index maintanance required and may take time

When a partition is truncated, the global index (if exists) will become unusable state, which means it will not be accessible until rebuild. It is possible to update such global indexes automatically with "update global index" option.

SQL> alter table scott.emp truncate partition part01 update global indexes

Even though it looks useful, it is internally a full table scan happening as described in DocID 2177233.1, which might take a long time to complete. It is often not a problem for the first time because the number of rows is small, but it might be a serious problem in the future gradually depending on the increase of the data. In such a situation, parallel option will be useful to reduce the time to rebuild global indexes. In fact, as far as I understand, this will be the only tuning option.

3. Conclusion

Considering the above, some tips for using truncate are the following:

- reduce the number of truncate executed to minimize lock and hard parse. For example, instead of truncate every partition, truncate a table once.
- avoid to execute truncate during business hours when online transaction is accessing the table frequently.
- avoid to use "reuse storage" option if you have severe performance requirement for the truncate. INSERT performance may not get benefit of reuse of extents.
- avoid to create global indexes. If it is really necessary, think about the maintenance in terms of performance.

I hope this document helps to understand truncate better.