Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

July 20

13:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Description

All about delete - Back to basics
In July's session we looked at how to remove data with delete. Highlights include:

00:25 - Structure of a delete statement
02:30 - Capture details of the removed data with the returning clause
04:45 - Using flashback query to recover rows deleted by mistake
10:00 - Demo of delete, returning clause, and flashback query
20:30 - Recovering space freed up by deletes
22:10 - Purging vs archiving vs staging
26:30 - Purging significant numbers of rows
27:50 - Move vs shrink to recover space
29:45 - Rolling archival processes
32:00 - Drop partition
32:15 - Deleting staging data
33:40 - Truncate table
34:10 - Simple dot-notation access for JSON data
35:10 - Demo of space recovery using shrink and move
39:50 - Demo of delete vs truncate
41:30 - Using global temporary tables to manage staging data
44:30 - Delete performance - how to make it faster
45:30 - Create table as select to "delete" data
46:10 - Drop partition
47:15 - Filtered table move to delete rows
48:40 - Demo of deleting 99.5% of the rows from 100 million row tables fast

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.