Welcome to the Beyond Blog

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.




The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.
3 minutes reading time (533 words)

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.

Extracting Data from Oracle Fusion SaaS using Data...
 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Thursday, 13 December 2018

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

76 King Street, Manchester

M2 4NH United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk