I want to count how many bricks I have for each colour and shape on my table
here. I've already split them up into groups for colors and shapes. Now I need
to just go through and count them. This could take a while!
Particularly if my table has thousands, millions or even billions of bricks on
it. But I know that I'm gonna have a maximum of three different colors: green,
red, and blue. And at most three different shapes: cubes, cuboids,
and these star things. So at most I'm gonna have nine rows in my output. If
this is a query I'm going to run often I can make the process a lot faster
by creating a summary table storing the count for each color and shape. So I can
just read this rather than all the individual bricks. But in order for this
to be useful, every time I change the data here, for example add another red
cube, I need to go to my table and increment the corresponding counter. This
adds a lot of processing and code you need to write to keep that summary table
in sync. Luckily there's a way for the database to do all of this automatically.
Materialized views!
Before we jump into the details, a quick note on the
difference between normal views and materialized views. A regular view stores
the text of your SQL statement giving you a reusable statement. When you query
the view, it's exactly the same as if you ran the query within it. A materialized
view on the other hand stores the result of the SQL statement. When you access
it, you get this pre-calculated result. The database does not access the base
tables themselves. This is something that can make your SQL statements orders
of magnitude faster, particularly if you have a statement that reads millions or
billions of rows but returns just a handful. So
instead of creating a summary table and writing code to keep it up-to-date, you
can create a materialized view storing the color and shape count for the bricks.
So, after creating the materialized view we only have nine rows to read which we
can get in an instant. Whereas if we go to the table itself the query
could take seconds, minutes or even longer. And now for the real magic. If
your application contains SQL that matches that in the materialized view,
the database can automatically read the materialized view instead of the base
table via something called query rewrite. So you can get huge performance
advantages without changing any code! And this also applies for queries which are
similar to that in the materialized view. Say instead of counting the bricks for
each color and shape, you just want to count the number for each color. Well we
can get this information from the materialized view by summing up the
shape subtotals for each color. This means one materialized view can
give you large performance gains to many SQL queries in an application.
But, in order to be able to do a query rewrite, first you must enable it on the
materialized view. Also, and more importantly, the data we have stored in
the materialized view must exactly match that what we would get from running the
query against the table itself. If we change any information, say add another
red cube, there is now a mismatch between the table and the materialized view. So
the database will no longer do a query rewrite. To fix this we need to
bring the materialized view up to date. You can do this by calling
DBMS_MView.refresh. This will then rerun the query in the materialized view and store
its results. But this could take a while. After all, the reason you use a
materialized view is you have a long-running query you want to make
faster. So can we make this process faster? Well
every time we make a change to the table we can record it in a log. For example,
if I add a red cube and remove a blue one, what we can do is write that down.
Say plus one red cube - I'm not very good at writing here! - minus one blue cube.
Now when i refresh the table, instead of having to go through and
count them all individually, what I can do is just look at these changes. So I
can say: got plus one red cube so I'll increase that to 8. And minus one blue
cube so I can take that down to 12. I can then empty these changes out of
our log here ready for the next thing. This is the basis of something called
fast refresh. Record the changes and apply them when we do the refresh
to the totals. In order to be able to do this, we need to create a materialized
view log. Do this with the statement: create materialized view log on the
table you want to track changes for. To ensure maximum performance and refresh
possibilities, you'll also want to include: The primary key of the table. The
row IDs. The sequence of changes. The commit SCN for the transactions. And a
list of the columns you use in the materialized view. Finally, you also want
to include the new values for these columns. With the log in place we can now
do fast refreshes which take microseconds. So it is much easier to keep
the materialized view in sync. So at this point you might be wondering: why not
just always do fast refreshes and create the log when
we create the materialized view? Well there are some types of query where you
can't just take a look at the list of the changes and apply them to the totals
and still get the correct answer! For example, instead of counting the rows for
each color and shape say we count the rows for each color and the number of
distinct shapes for each. So after creating a materialized view
with these new subtotals, if I try and add a new brick to the table, say my blue
cube here, I don't know: is this cube in this count of two different shapes for
blue? There's just no way to know that other than to go back to the table
itself and count the distinct shapes. There are many other types of query
which have this kind of logical restriction where you can't just look at
a list of the changes and figure out the new totals. There are also many technical
restrictions which prevent fast refresh. If you want to know the details of all
of these check out the docs, which has a big list them. Check the links in the description if you
want to know more. But there is a much better way than reading through the
documentation! The procedure DBMS_MView.explain_mview tells you why
a materialized view is fast refreshable or not, and what you need to change to
make it so. Assuming your query does support fast refresh you can change the
materialized view to do this. Even better, you can get the database to update it
automatically by making it: fast refresh on commit. This is really handy because
it means the materialized view is always fresh. It never goes stale. But it does
add a little bit of extra processing to all of your transactions for those
tables. In write heavy applications, this may add too much stress to your
database. So you may be forced to call the refresh procedure on a schedule . So
there will be brief periods of time when the materialized view is up to date and
query rewrite can happen. Followed by chunks of time when it is stale and
query rewrite can't happen. So SQL performance could flick
between ultra-fast and ultra-slow. This is the kind of thing that drives users
crazy! People don't like slow queries, but if it's predictably slow - say it always
takes 5 seconds - they'll live with it. Something that flicks - seemingly
at random - between half a second and half a minute is the kind of thing that
will very quickly get them coming for you with axes! So what can we do about
this? Well there is a trick. You set the query rewrite integrity parameter to
stale tolerated. This means the database can use the materialized view for query
rewrite, even though the data is out of date! Now you may be wondering: Why would
we ever want stale data?! Imagine you have a materialized view you
just refreshed. One second later, a user runs a query in the materialized view.
But there's been changes to the tables, so it runs against the base tables. This
query will run for five minutes. One minute after the first, another user
comes along and queries the materialized view. At this point the materialized view
is one minute out-of-date. But this query completes in one second. The first user
still has to wait another four minutes to get their results. So even though the
second user starts their query later and used technically out-of-date information,
they waited less time and their answer is four minutes more up-to-date than the
first one! So stale data can be useful in some
cases. But this is something where you need to work closely with the data
consumers to ensure it meets their needs. Or in Oracle Database 12.2 there is
another option - real-time materialized views. These take a stale materialized
view and - at query run time - apply the changes in the log. This means you no
longer need to refresh the materialized view in every transaction. So you
avoid the hit there. But you still get good performance gains for those queries.
This hybrid approach can give best of both worlds outcome... provided you still
do the refreshes regularly to keep the materialized view log small! To recap.
Materialized views are an amazing performance tuning technique,
particularly for queries that read millions of rows but return just a
handful. For example counting how many orders your company received each day.
They can also be useful for things, like pre calculating joins and other types of
complex query. Provided you can define your materialized view as "fast refresh
on commit" then the data always stays up-to-date and the database can do a
query rewrite, swapping in the materialized view instead of reading all
the data from the table itself. Sadly there are many restrictions that prevent
fast refresh. If you hit these then either you need to rethink your queries
or deal with stale data. Thanks for watching! The rules for fast refreshable
materialized views are complicated. Check the documentation for full details. And
subscribe for more SQL tips! :)