Speeding up Oracle Queries with Materialized View Rewrite

Materialized views (MV’s) – the developers go-to tool for poor performing queries. 🙂 Of course that isn’t really their only purpose, and creating one just because a query isn’t performing too well should be the last resort after fully investigating the root cause of the problem. We can however use MV’s as a tool in optimizing our query performance. Take the following example of a “transactions” table that I have mocked up, containing one million rows.
 create table a as   select level txn_id, sysdate  + dbms_random.value(-100,100) dt, dbms_random.value n from dual   connect by level <= 1000000;    alter table a add constraint x_pk primary key (txn_id); 
Now we get asked to write a query to give the total and average transaction amounts by month. We’d probably write something like this.
 select trunc(dt,'mm') month_start, sum(n) sum_n, avg(n) avg_n    from a group by trunc(dt,'mm') order by month_start; 
However when we look at the execution plan, we can see we’re having to process every row in the table.
 ------------------------------------------------------------------------ | Id  | Operation           | Name | E-Rows |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------ |   0 | SELECT STATEMENT    |      |        |       |       |          | |   1 |  SORT ORDER BY      |      |    970K|  2048 |  2048 | 2048  (0)| |   2 |   HASH GROUP BY     |      |    970K|  1063K|  1063K| 1245K (0)| |   3 |    TABLE ACCESS FULL| A    |   1000K|       |       |          | ------------------------------------------------------------------------ 
Adding an index isn’t going to help really because we’re doing analytical work – we’re retrieving most of the rows in the table before summarizing. So accessing via an index would actually be less efficient.

What we can do instead however is make use of the Query Rewrite functionality of materialized views. What this does is allows us to create a pre-calculated aggregate MV, which Oracle will select automatically when it can, instead of having to query the underlying table.

 create materialized view a_mv refresh complete on demand enable query rewrite as     select trunc(dt, 'mm') month_start,            sum(n)          sum_n,            avg(n)          avg_n       from a   group by trunc(dt, 'mm'); 
Now look what happens when we run our query from above.
 select trunc(dt,'mm') month_start, sum(n) sum_n, avg(n) avg_n    from a group by trunc(dt,'mm') order by month_start; 
Then look at the plan:
 ---------------------------------------------------------------------------------- | Id  | Operation                     | Name | E-Rows |  OMem |  1Mem | Used-Mem | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |      |        |       |       |          | |   1 |  SORT ORDER BY                |      |      8 |  2048 |  2048 | 2048  (0)| |   2 |   MAT_VIEW REWRITE ACCESS FULL| A_MV |      8 |       |       |          | ---------------------------------------------------------------------------------- 
We can see that Oracle has recognised that our predicates match those in the MV, and re-written the query automatically and transparently to select from our A_MV instead. Thus, rather than processing a million rows, we only process 8. If your query can’t be satisfied by the MV then Oracle will automatically choose the base table instead.

Whilst it can be difficult sometimes to write the MV in such a way to satisfy the different possible queries your users may issue, this is a quick and easy way of optimizing performance for a known set of queries without the need to modify those queries.

Leave a Reply

Your email address will not be published. Required fields are marked *

9 − four =

This site uses Akismet to reduce spam. Learn how your comment data is processed.