CODEWITHSUNDEEP
oracle11gmybatisibatis
Phate
Phate

Reputation: 6622

truncate not always working: why?

I have defined this mapper method:

@Delete("truncate table MY_TABLE")
public void wipeAllData();

and it usually works...anyway sometimes it doesn't...is there any particular reason/known bug for that?

I'm using mybatis 3.3.0 with oracle 11g as DBMS.

Upvotes: 0

Views: 4211

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23381

EDIT

Since you added the oracle11g tag. My previous answer is no longer valid, at least not the reason why it would not be working. So I edited it.

There are some reasons that I'm aware of why sometimes it is not working in ORACLE. According to the ORACLE docs

You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.

You cannot truncate the parent table of a reference-partitioned table. You must first drop the reference-partitioned child table.

But you should be aware that the usage or a TRUNCATE command is not ideal in an application scope. It should be an operation executed on the database only. The reason lies in another indication of the docs:

If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment. This operation is equivalent to creating a new segment for the index. Therefore, at the end of the truncate operation, the indexes are once again USABLE.

So it can be a painfully long operation depending on indexes and the size of the table.

Also, for tables that have constraints the truncate operation will not drop the table, it will delete registries one by one. If you have ON DELETE CASCADE on your constraints, if not, an error will be thrown. This is still true for oracle database

Another thing will should aware of is

Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

So if by any means you have a trigger on that table it will do nothing.

The original DOC about TRUNCATE command is here: TRUNCATE TABLE

Upvotes: 2

Related Questions