basically - the segment is checkpointed and the dictionary updated - for each segment involved.

Yes, it might obviously have to cascade to subordinate segments - doing the same exact thing (which is part of the reason why the checkpoint is necessary) - but it is all the same.


I don't like the way they wrote that section. It makes it sound like:


o You cannot roll back a TRUNCATE TABLE statement.
o All cursors are invalidated.
o You cannot flash back to the state of the table before the truncate operation.

are specific to reuse storage when in fact they apply to drop storage as well.

it is not specific to reuse storage.


In fact now that you pointed me to that all and I've read it - I have more comments...

They wrote:

This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future. TRUNCATE TABLE with REUSE STORAGE performs several orders of magnitude faster than deleting all rows

It might also perform several orders of magnitude slower than deleting all rows.
It might perform about the same.

It might perform faster.

It depends. I don't know what the number of extents has to do with anything - unless they were awkwardly trying to say "reuse might be faster than drop storage as we don't have to deallocate the extents" - but they are in a paragraph that was comparing truncate to delete.

if you read it in context, it would appear they are saying that the list of three things above applies ONLY when you use reuse storage. They stuck that list in the middle of reuse storage - not in a section "hey - things to think about with truncate in general"

It (the list) belongs in the section "hey - things to think about with truncate in general"


The three things are true - but they are true for drop storage as well as reuse storage.

I've filed a documentation change against that

the data object id can be changed - the underlying physical data objects might be different. So while the logical structure is the same, the physical structure might well be altered.

the DATA_OBJECT_ID can change. Look in user objects - you'll see there is an object_id, that is the "logical" id. It never changes.

Then there is a data_object_id, that is physical, it is a pointer to the segment.

that the cursors get invalid doesn't affect you - the will be implicitly validated as soon as you execute them again. There is no way to "stop it", it is a natural event in the database. Your cursors go invalid all of the time due to shared pool aging, gathering statistics, etc - it happens all of the time.


The dependency of WHAT object?

user dependencies is "object id" based - when you truncate a table - stored procedures do NOT go invalid.

The query would be implicitly hard parsed - yes. But you don't truncate a table every minute so it shouldn't be a big deal. And in a data warehouse environment - you don't really rely on shared sql anyway - you typically do not use bind variables, you typically do not run the same queries over and over and over again.

... Just come across a case where the developer was truncating every 8 seconds.
Basically he was using a real table where he should have been using a GTT. ...

wow??? Can you spell "serial processing"??? You could never have more than one user running their application at a time.


... We called it Mutant
Nija SQL - it sneaks in - kills your database - and then disappears leaving no
trace that it ever existed. ...

ok, I have to admit, I laughed out loud at that.