Learning is not a spectator sport

A post on reddit created the usual reddit storm the other day due to its title line:

and whenever you make a claim on reddit (even though I think the original poster was actually posing this as a request for clarification), then rest assured there is going to be avalanche of people jumping into the fray to aggressively push their view 😀.

“If truncate was DML it would be transaction safe. Sounds like DDL to me”

“Truncate is not changing any structure, just data, so I’d put it solidly in the DML bucket.”

“TRUNCATE TABLE does implicitly commit the current transaction which is consistent with DDL”

“Internally Oracle treats truncate as a DDL operation and delete as DML.”

“As an Oracle veteran since 8i, I strongly disagree…So truncate being a user tool is DML”

“I started with 7. And I might ask my Oracle 6 veteran colegue to weigh in.There you go :)”

“TRUNCATE TABLE is DDL because it is followed by a COMMIT, just like all DDL operations”

“Truncate is immediate and does not need to be followed by a commit.”

“Get a new trainer.”

With so much kerfuffle this is eventually bled into Twitter and friend Jeff made what I think the most important point

but notice there is no mention of “DDL” or “DML” there because as another friend John wisely chipped in:

Ultimately, it matters not if you class TRUNCATE as DDL or DML or raspberry pumpkin pudding 😀. For example, it is not too difficult to wander off into niche cases which can throw a spanner into the works no matter what side of the fence you want to site on.

(The trick in this one is that “some_other_table_name” is something like ORA$PTT… indicating a private temporary table)

What really matters is that you understand the implications of performing a truncate in its various usages, and docs do their best to elucidate on that:

and we know that this is a big deal, because if a new developer thinks of DELETE and TRUNCATE as being synonymous then (as Jeff alluded to) that is going to be a career limiting move one day. So even in the docs we double down and re-stress the important parts right at the start of the section.

So feel free to classify TRUNCATE however you like – it is way more important to just make sure you know how it works!

Read the reddit thread here