Aggregating Millions of Rows

It's common for reporting queries to aggregate many rows down to a handful. Examples include:

• Counts of sales per week, month or year
• Total value of orders per customer
• Counting employees per department

These queries may process millions or billions of rows, but only return a few. This is particularly common when aggregating by date.

This query counts the total rows per colour in the table:

select /*+ gather_plan_statistics */colour, count(*) 
from   bricks
group  by colour;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, null, 'IOSTATS LAST'));

This reads 10,000 rows. But returns just 4. You can speed up this search by creating a summary table with the count per colour. Then querying that:

create table colour_summary as 
  select colour, count(*)  c
  from   bricks
  group  by colour;

select /*+ gather_plan_statistics */*
from   colour_summary ;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, null, 'IOSTATS LAST'));

But for COLOUR_SUMMARY to be correct, you need to update it every time sometime changes rows in BRICKS. Coding this is a lot of effort. Luckily Oracle Database has an in-built option to do this automatically: materialized views (MVs).

Create Summaries with Materialized Views

Unlike a regular view, an MV stores the result of its query. When you access the MV it reads these saved data.

This creates an MV counting the number of rows for each colour:

create materialized view brick_colours_mv
as
  select colour, count(*) 
  from   bricks
  group  by colour;

select /*+ gather_plan_statistics */* from brick_colours_mv;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'IOSTATS LAST'));

There are 10,000 rows in the bricks table. But only four different colours. So the MV only stores four rows! So querying the MV does a lot less work compared to running the query against the base table.

Try It!

Replace /* TODO */ to create an MV that stores the count of rows for each shape:

create materialized view brick_shapes_mv 
as
  select /* TODO */
  from   bricks
  group  by shape;

select * from brick_shapes_mv;

Using Materialized Views Automatically: Query Rewrite

Querying an MV can be orders of magnitude faster than running the query you use to build it. But changing existing queries to use an MV could be a huge task.

Fortunately the optimizer has a trick up its sleeve. If you run the query used in an MV, the optimizer can detect this. And change your query to use the MV instead of the base table!

To do this, you need to enable query rewrite for the MV:

alter materialized view brick_colours_mv
  enable query rewrite;

Now this is enabled, the optimizer can redirect the queries matching the one in an MV to the saved output, instead of the bricks table. You can check this is happening by the "MAT_VIEW REWRITE ACCESS FULL" operation in the execution plan:

select /*+ gather_plan_statistics */colour, count(*) c
from   bricks
group  by colour;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'IOSTATS LAST'));

A rewrite is also possible when the query includes a where clause - provided the filter columns are in the MV. So this query to get the count of red rows uses the MV:

select /*+ gather_plan_statistics */count(*) row#
from   bricks
where  colour = 'red'
group  by colour;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'IOSTATS LAST'));

The optimizer can also use query rewrite for other queries it can derive the result of using an MV. For example, the MV gets the count by colour. So you can get the total number of rows in the table by adding up these totals.

This means the optimizer can use BRICK_COLOURS_MV to get a count of the total number rows in the table:

select /*+ gather_plan_statistics */count(*) 
from   bricks;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'IOSTATS LAST'));

Try It!

Replace /* TODO */ to enable query rewrite for the BRICK_SHAPES_MV you created in module 3:

alter materialized view brick_shapes_mv 
  /* TODO */;

select /*+ gather_plan_statistics */shape, count(*) 
from   bricks
group  by shape;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'IOSTATS LAST'));

When successful, you should see the plan use the MV with a rewrite operation.

Impact of Data Changes

To do query rewrite, by default there must be an exact match between the rows in the table and the data in the MV. If there are any changes to the base table, there is a mismatch. And the optimizer will no longer do a rewrite.

This adds one row to BRICKS:

insert into bricks values ( 0, 'red', 'cube', 100, sysdate, default );
commit;

The MV is now stale. This stops the optimizer from using BRICK_COLOURS_MV for query rewrite:

select /*+ gather_plan_statistics */colour, count(*) num_rows
from   bricks
group  by colour;

select * 
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'BASIC LAST'));

You can check if an MV is stale by querying *_MVIEWS:

select mview_name, staleness 
from   user_mviews;

There may be cases where you still want to use a stale MV. For example, when querying old data you know is correct in the MV. Or it may be better to get an out-of-date answer quickly than wait a long time for a result.

By tweaking session settings, you can allow the optimizer to use stale MVs in these cases.

Using Stale Data

It's possible to let the optimizer use stale materialized views for query rewrite. Do this by changing the QUERY_REWRITE_INTEGRITY parameter. This supports these values:

• ENFORCED - there must be an exact match between table and MV data for a rewrite to happen. This is the default.
• TRUSTED - the database can do rewrites based on declared constraints that are disabled. This setting rarely affects MV rewrites.
• STALE_TOLERATED - the optimizer can do a query rewrite using stale MVs

Changing this parameter enables the optimizer to use an MV:

alter session set query_rewrite_integrity = stale_tolerated;

select /* stale */count(*) c
from   bricks;

select * 
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'BASIC LAST'));

alter session set query_rewrite_integrity = enforced;

select /* enforced */count(*)
from   bricks;

select * 
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'BASIC LAST'));

Note that the queries now return different results!

While this trick is handy in some cases, usually you want fresh data in an MV. So you need to bring the MV up-to-date.

Keeping MVs In Sync with the Table

You can update the data in an MV by calling exec dbms_mview.refresh. Pass this the name of the MV and the refresh type. C does a complete refresh:

exec dbms_mview.refresh ( 'brick_colours_mv', 'C' );

A complete refresh re-runs the query in the MV, bringing it up-to-date with the underlying data. The MV is now fresh, so the optimizer can use query rewrite again using enforced integrity:

select mview_name, staleness 
from   user_mviews;

select colour, count(*) rws
from   bricks
group  by colour;

select * 
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'BASIC LAST'));

The query in an MV may take minutes or hours to complete. During this time it's likely rows in the tables it uses will change. So the MV is already out-of-date when the refresh finishes!

This is a problem if you need to get current data from an MV. To combat this, you can change the refresh so it starts with the stale MV. Then applies any changes to the tables' data to it.

Incremental Materialized View Refreshes

Instead of re-running the whole query in an MV, you can apply the changes since the last refresh. This is called a fast refresh. To do this, first you must create a materialized view log on the table:

create materialized view log 
  on bricks 
  with primary key, rowid, sequence, commit scn ( 
    colour, shape, weight, insert_date 
  )
  including new values;

This captures details of all changes made to the rows. By default this is named MLOG$_<tablename>. This records all changes you make to the table's rows:

update bricks
set    colour = 'green'
where  brick_id = 1;

delete bricks
where  brick_id = 2;

insert into bricks values ( -2, 'red', 'cube', 100, sysdate, default );

commit;

select * from mlog$_bricks;

With the log in place, the database can bring an MV up-to-date by applying the changes in the log to the MV itself.

When defining an MV the refresh methods are:

• COMPLETE - refresh the MV by running its query
• FAST- apply changes in the MV log to bring it up-to-date
• FORCE - use FAST refresh if possible and COMPLETE if not

The default refresh clause is:

REFRESH FORCE ON DEMAND

This means you have to refresh the MV manually. When you do so, the database will pick fast refresh if it can. To ensure the database always applies changes from the MV log, set the MV to REFRESH FAST.

You can get the database to do this automatically at the end of every transaction. Do this by setting the refresh to ON COMMIT. When you commit, the database will apply any changes in the log to the MV. To set this property the log must be empty, so first do a final complete refresh:

exec dbms_mview.refresh ( 'brick_colours_mv', 'C' );

alter materialized view brick_colours_mv
  refresh fast on commit;

Now any changes you make to the table are reflected in the MV during the commit:

select *
from   brick_colours_mv;

insert into bricks values ( -1, 'red', 'cube', 100, sysdate, default );
commit;

select *
from   brick_colours_mv;

When you create ENABLE QUERY REWRITE REFRESH FAST ON COMMIT MVs, the optimizer automatically uses them where possible. So these can give huge performance gains with no code changes!

Try It!

Replace /* TODO */ to make brick_shapes_mv fast refreshable:

exec dbms_mview.refresh ( 'brick_shapes_mv', 'C' );

alter materialized view brick_shapes_mv 
  /* TODO */;

select refresh_mode, refresh_method
from   user_mviews
where  mview_name = 'BRICK_SHAPES_MV';

The REFRESH_MODE should be COMMIT and the REFRESH_METHOD be FAST.

Fast Refresh Limitations

Sadly there are many restrictions on the types of query you can use in a fast refresh on commit MV. For example, you can't use COUNT ( DISTINCT ):

create materialized view bricks_not_fast_mv 
as
  select colour, count(*), count ( distinct shape )
  from   bricks
  group  by colour;

alter materialized view bricks_not_fast_mv 
  refresh fast on commit;

This is because if you insert a row with the shape PRISM, there's no way to know if this is already included in the distinct count.

There are many other limitations for fast refreshable MVs. The documentation contains a complete list of these restrictions. You can see which refresh options are allowed on an MV using dbms_mview.explain_mview.

A FAST REFRESH ON COMMIT MV also adds some overhead to every transaction using the tables in the MV. In write heavy applications this can put too much stress on the database.

It would be nice if queries could apply changes in an MV log to a stale MV while running a query. Oracle Database 12.2 makes this possible with Real-Time Materialized Views.

Real-Time Materialized Views

A real-time MV allows a query to use its stale data. At query run time, the database applies any changes in the MV logs to get current results. To do this, the MV must be:

• FAST REFRESH ON DEMAND
• Use the ENABLE ON QUERY COMPUTATION clause

These statements change the MV do to this:

alter materialized view brick_colours_mv
  refresh fast on demand;
  
alter materialized view brick_colours_mv
  enable on query computation;

Now when you make DML changes, the MV remains unchanged. So it is stale:

insert into bricks values ( -3, 'red', 'cube', 100, sysdate, default );
commit;

select *
from   brick_colours_mv;

select mview_name, staleness 
from   user_mviews;

But running the query within the MV uses it as a starting point, applying the changes in the log. This leads to a scary looking execution plan!

select /*+ gather_plan_statistics on query computation */colour, count(*) 
from   bricks
group  by colour;
  
select * 
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));

Provided you refresh the MV regularly so there is always few rows in the log, this is efficient. But leave it too long between refreshes and there will be lots of changes to apply. At some point this is slower than running the query itself!

The optimizer takes this into account when deciding whether to do a rewrite using a real-time MVs. If it thinks applying the changes is too much work, it'll read raw table data.

To ensure the best performance for real-time MVs, keep the time between refreshes small.

Try It Challenge!

Complete the MV definition below, so the database will use it in both the queries below:

drop materialized view bricks_challenge_mv;

create materialized view bricks_challenge_mv
enable query rewrite
as
  select trunc ( insert_date, 'mm' ) mth/* TODO */;
  
select /*+ gather_plan_statistics */trunc ( insert_date, 'mm' ), sum ( weight )
from   bricks
where  trunc ( insert_date, 'mm' ) = date'2020-06-01'
group  by trunc ( insert_date, 'mm' );

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));

select /*+ gather_plan_statistics */ trunc ( insert_date, 'mm' ), weight, count (*)
from   bricks
where  trunc ( insert_date, 'mm' ) = date'2020-12-01'
group  by trunc ( insert_date, 'mm' ), weight;

select * 
from   table(dbms_xplan.display_cursor( :LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));

Remember: the MV doesn't need a where clause. As long as expressions in a WHERE clause appear in an MV, the database can use it for rewrite.

You can make queries using MVs faster still by creating indexes on the MV! Can you create any indexes on BRICKS_CHALLENGE_MV which these queries will use?