So far in this series we've talked about ways to make your queries faster. But you
want your writes - inserts, updates, and deletes - to be faster too, right?
Let's find out how!
Firstly you want to ensure that you're finding the rows you want to a
change efficiently. You can do this by changing the write into a read. Do this
for updates and deletes by querying the table using the same where clause. For
insert-as-select just remove the insert and run the query on its own. For insert
using values there is no query. So there's nothing to do here. But before
you do this combine your changes into as few SQL statements as possible. To see
why let's say I'm loading bricks from this box onto this table. I could do this
by picking up the bricks one-by-one and putting them on the table. Clearly this
is gonna take a while. It is much faster if I just pick up the whole box, and
tip them all out! Similarly if I now decide I want to remove some of these
bricks it's gonna take a while if I'm just picking them up one-by-one and
putting them back in the box. It is much faster if I just wipe a whole bunch off
in one go. It is the same with SQL statements. It is much faster to have one
statement that changes many rows compared to having lots of statements
which changes a couple of rows each. So make sure you're not making one of the
biggest performance mistakes around: writing data inside loops. You can often
make processes much faster by taking the write out of the loop and changing the
code to one SQL statement. But once you've combined all your changes into
one SQL statement you may still find - particularly for updates and deletes of
millions of rows that your SQL takes too long to run. Luckily for these large-scale
updates and deletes there is a trick available: turn DML into DDL. Let's say I
want to remove most of the bricks available. Instead of deleting the ones I
want to get rid of, I can save the ones I want to keep to another table. And then
switch the rows over. Do this by writing a query finding the rows you want to
keep. And save them by turning this into a create-table-as-select. Complete the
process by truncating the original table - an instant DDL operation - and copying the
rows back from the new table to the original. You can use the same idea to
make updates of millions of rows faster. Or if you need to go faster still you
can do so by switching the tables over. Do this by using create-table-as-select
as before, then switch the tables over by renaming or dropping the original and
renaming the new table. If you choose to switch the tables over, to complete the
process you will need to copy any indexes, constraints, and so on from the
old table to the new. This can still take a while. So to see whether it's faster
than just switching the rows over test both to see which is fastest. But in
either case the process is fiddly to write. And you will need to take your
application offline to complete it. Making this technique infeasible for
most of us. Luckily Oracle Database does have a
couple of other tricks of its sleeve. Partitioning and filtered table moves.
With partitioning you logically split up one table into several smaller sub
tables. So you can split the rows up into - effectively - separate smaller sub tables
based on values in one or more columns. You can then do operations on the rows
in just one partition. So we can get rid of all the
rows in one partition easily by truncating or dropping it. Provided you
want to remove all the rows in a partition you can do so instantly by
dropping or truncating it. Just remember that partitioning a table will affect
all operations against it; all queries, inserts, updates, and deletes. Some of
which could get slower. So make sure you assess your a whole workload before
diving in with partitioning. If this turns out to be a non-starter for you,
from Oracle Database 12.2 there is a much more lightweight way to remove lots
of rows from a table: a filtered table move. Do this by adding a where clause to
an alter table move command. This discards all the rows you want to remove.
Leaving a table with just the rows you want to keep. This is much easier than
creating a temporary table and swapping the rows over. And you can do it online.
But just remember: DDL in Oracle Database commits. Meaning you cannot roll these
changes back. If you made a mistake doing this, then you need to recover the lost
data from some kind of backup. Take extra care before going ahead with these
operations! But it's rare you need to change your DML processes into DDL.
Usually your process will change just a small fraction of the rows in the table.
In which case changing your code so you've got one SQL statement which
changes all the rows is usually good enough. But you may still find that -
unlike queries - there are other processes which are slowing down your writes. Two
common culprits for this are blocking sessions and triggers. In Oracle Database
nothing blocks queries. But writes can be blocked
by another session. So one process can stop another from completing. There are
many possible reasons for this. But common ones include clashes on
primary or unique keys for inserts and updates or deletes trying to change the
same rows. For example, let's say we've got two sessions trying to insert the
same primary key value. They both start at the same time so can't see each
other's data. This means that the second session has to wait for the first one to
commit or rollback before it knows if it's trying to add a duplicate value.
Similarly if you have two sessions trying to update or delete the same rows,
the second session has to wait for the first to commit or rollback before it
can write its changes. To see if blocking locks are a problem for your application,
check for them in your database monitoring tools. If they are then you'll
have to rewrite your processes to avoid the locks. But assuming that there are no
blocking sessions affecting your statement, you may still find that the
writes are slower than you expected. This could be because of triggers! These do
extra work on top of your SQL. It's often the case that your SQL
statement is fast enough - it is the work done in the trigger that really slows it
down. If this is the case again, to speed it up you will need to rewrite the
process. Ideally without any triggers! So to recap, the basic tuning process for
writes is the same for queries: get the SQL's execution plan and see if
there's any indexes you need to create to make the process faster. Assuming you
are finding the rows efficiently, look to see if there are any other processes
which are slowing your SQL down. Common causes are blocking locks and
triggers. Or - if you're updating or deleting millions of rows in a table - you
can often get good performance gains by changing the DML into DDL. But above all
else: take SQL out of loops! The key to fast
efficient SQL is to have one statement that changes all the rows.
Thanks for watching! There are of course many other things which could slow your
writes down, but for this video I wanted to focus on the common causes that you
as a developer can implement. If you've got any questions on these let us know
in the comments. And subscribe for more SQL tips :)