HomeНаука и техникаRelated VideosMore From: ORACLE-BASE.com

SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements

13 ratings | 1546 views
The tenth part of a mini-series of videos showing the basics of SQL. In this episode we take a look at the DELETE and TRUNCATE TABLE statements. For more information see: https://oracle-base.com/articles/misc/sql-for-beginners-the-delete-and-truncate-table-statements Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Michelle Malcher : Blog: http://michelledbaunleashed.blogspot.co.uk/ Twitter: https://twitter.com/malcherm Cameo appearances are for fun, not an endorsement of the content of this video.
Html code for embedding videos on your blog
Text Comments (5)
Bill Mitzmacher (1 year ago)
Was that a surprise intro from Michele Malcher..... "Dot Com"!
Kart (1 year ago)
Hi Tim, It would be lovely if you could cover what happens behind the scenes between the two statements. My understanding is DML's get redo entries and which is not written immediately to the disk files. But for truncate we have high water mark is reset, so does that mean it does the data block actions in real time ? For a very large table, which has just been deleted & truncated (separate cases) would a corresponding smae statement again be slow, meaning what is the data block situation in both. Will the truncate drop storage clause make a subsequent truncate faster. Would appreciate your insights. Thanks
ORACLE-BASE.com (1 year ago)
Not sure I understand the question. Delete is DML, so it has to do the usual thing of writing redo to disk immediately, and altering the blocks in the datafiles at some later time. It is slow both because of redo generation, and the fact constraints have to be checked, triggers have to fire and indexes have to be managed. A truncate is DDL, so it doesn't have to follow all those rules. It just throws away all the segment extents and starts again. This is much quicker. If you truncate a big table, then let it build up to a big table again, the delete of that big table will be slow again. I'm not sure what you are getting at with your last part of the question.
Nasim Chaklader (1 year ago)
Hi Tim my question is , when we use DELETE , we can capture how many deleted but can we capture by using TRUNCATE. THANKS
ORACLE-BASE.com (1 year ago)
No. TRUNCATE is DDL, not DML. It's doesn't check how many rows were affected. It just drops the segment.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.