Whether it's unwanted junk, or stuff that's just getting too old, often there's
rubbish you want to clear from your tables. It's time for a delete. As the
name suggests, deleting things removes information from the table matching the
where clause. Once you've committed the data is gone, so take special care with
your where clause, or you may find yourself losing some vital information. But
sometimes you do want to do a wholesale delete. Whether it's temporary data
that's no longer relevant or historic data you're archiving, from time to time
you do want to wipe out everything. A delete without a where clause will do the job
but this can take a long time, particularly if your table stores lots of data.
Fortunately there's a much quicker way: truncate. This instantly resets your table
marking it as empty. But this comes with two big gotchas. Firstly it always
removes everything. You can't selectively truncate. Secondly
truncate commits. So if you change your mind you can't get your data back.
Many people don't like deleting data. Partly it's human nature to want to keep
a hold of stuff. But there are practical reasons to disallow this too. If people can
delete whatever they want it enables them to cover their tracks if they've
been up to no good. So a lot of applications use a soft delete. Here the
data remains in the table. You just mark rows as gone by setting an 'is deleted' flag.
This makes it easy to recover data if you delete the wrong thing by mistake. Or
if someone has been trying to cover their tracks you can still see what they
thought they deleted. If you go for soft deletes you need to
ensure your queries only return rows that are active. This means adding a
where clause to all your queries. But this is tedious and easy to forget.
Fortunately there are a few ways to simplify this. The most universal is to
create a view over the table. This includes the where clause to only return
active rows. You then change your application so it only uses the view, not
the underlying table itself. This is simple but crude. For instance there may
be super users who do have permission to delete data or need to see the soft
deleted rows. This complicates your code because you now need to choose between
the view and the table depending on the user. Luckily, if you're using Oracle
database, there's a more powerful option available. Virtual Private database. This
can automatically add a where clause to your queries based on criteria such as
the user. This is transparent to your application. So once it's configured you
can forget about it. Or you can take advantage of another Oracle database
feature: in database archiving. This adds a hidden column to your table
controlling whether or not a row is visible. Like classic soft deletion you
set this by updating it. The database then automatically hides the deleted
rows from you, but because they still exist you can make them visible by
toggling session settings. For more details on this and Virtual Private
database check out the links down in the description.
Just remember soft deletes increase the complexity of your application. Before
blindly going ahead and doing this ask yourself if it's really necessary to
keep everything. Often your real requirement isn't to prevent deletes but
to know who removed what and when. Something you can do with auditing
instead, which you really should do anyway. That way if something unexpected
does happen you can follow the trail and find the perpetrator. Thanks for watching!
Subscribe to my youtube channel to learn more about database development and see
SQL magic.