Real Time Materialized Views

Today's throwback feature of the week is the Real-Time Materialized View.

What are Materialized Views and how are they different from Real-Time Materialized Views?

A Materialized View stores the pre-calculated results of a given query. This can have huge performance benefits and drastically improve query response time because the database can use the pre-calculated results directly.

When using Materialized Views, if the data in the table changes, the pre-calculated query results can become outdated (stale) until the view is refreshed.

For example, let's say you've got a calculation. You want the total number of sales for each of your products. Both the sales and product tables have millions of rows. You'll want to be sure the Materialized View always has the latest totals, and if the table is constantly receiving updates, the materialized view needs to constantly refresh. In general, with large tables and complex calculations the constant refresh of the view could have a big effect on the performance of the database.

Real-time materialized views provide a lightweight way to address this by showing fresh data only when you query it.

How Do They Work?

Real-time materialized views use a clause on-query computation to provide up real time functionality. Over time as the underlying table gets updated, the view becomes stale. The database keeps track of the changes to the base table using change logs. When you query a real-time materialized view, the database:

• Uses the stale data in the view

• Add the most recent changes from the change longs when you execute the query

• Gives you the results based on the combo from above

One thing to note, the database does not permanently refresh or update the materialized view during query execution. Instead, it temporarily applies recent changes from logs. The view itself is stale but you're getting the fresh results. You'll need to manage the materialized view refresh over time.

Using the Real-Time Materialized Views

You can use the real-time materialized views in two ways:

1. Directly accessing the real-time materialized view by using its name.

2. Writing a query that the database then determines would be better off to use the view instead of manually calculating the results

Let's look at two small examples. We'll start with directly accessing the real-time materialized view.

Directly accessing the real-time materialized view

First, let's create two basic tables named `sales` and `products`:

CREATE TABLE products (
	prod_id NUMBER PRIMARY KEY,
	prod_name VARCHAR2(50)
);

CREATE TABLE sales (
	sale_id NUMBER PRIMARY KEY,
	prod_id NUMBER,
	quantity_sold NUMBER,
	FOREIGN KEY (prod_id) REFERENCES products(prod_id)
);

Insert some data:

INSERT INTO products VALUES (1, 'Laptop'),
						   (2, 'Phone');

INSERT INTO sales VALUES (101, 1, 5),
						(102, 2, 10);

COMMIT;

In order to use a real-time materialized view, the database will need change logs on all of the base tables of the real-time materialized view:

CREATE MATERIALIZED VIEW LOG ON products WITH ROWID (prod_id, prod_name) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, quantity_sold) INCLUDING NEW VALUES;

To create a real-time materialized view, add the ON QUERY COMPUTATION clause in the CREATE MATERIALIZED VIEW statement

CREATE MATERIALIZED VIEW sales_summary_rtmv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT p.prod_name, SUM(s.quantity_sold) AS total_quantity
FROM sales s
JOIN products p ON s.prod_id = p.prod_id
GROUP BY p.prod_name;

Initially, your view is fresh:

SELECT * FROM sales_summary_rtmv;

Now, insert new sales data:

INSERT INTO sales VALUES (103, 1, 3);

COMMIT;

If you query the materialized view now without refreshing, results are now outdated:

SELECT * FROM sales_summary_rtmv;

Laptops should have 8 sales, not 5.

To get the fresh data directly from the MV, we can use the `FRESH_MV` hint:

SELECT /*+ fresh_mv */ * FROM sales_summary_rtmv;

This time, Oracle uses the real time materialized view.

Writing Queries That Benefit from Real-Time Materialized Views

Apart from directly querying the real-time materialized view, the database can also automatically find queries that should use the real-time materialized view.

When the database runs a query, it checks a few things:

1. If the real-time materialized view is fresh:The database just uses the data already stored in the view to answer the query.

2. If the view is stale: Then the database decides between two options:

   • Use the view plus recent changes (from the logs) to answer the query.

   • Or skip the view and go straight to the original base tables.

The database chooses whichever option is more efficient.

In the example, since the view is stale, the database will compare the cost of:

• Using the view with on-the-fly updates from the logs, vs.

• Just using the base tables directly.

For our demo, we can force itt to use the materialized view with a hint in the SQL.

SELECT /*+ REWRITE(sales_summary_rtmv) */
       p.prod_name,
       SUM(s.quantity_sold) AS total_quantity
  FROM sales s
  JOIN products p ON s.prod_id = p.prod_id
 GROUP BY p.prod_name;

This gives us the updated sales numbers as we would expect.

We can check the explain plan and see a lot of info. The improtant part to realize is the database is using the stale materialized view and applying the change logs to give the up to date query.

EXPLAIN PLAN FOR
SELECT /*+ REWRITE(sales_summary_rtmv) */
       p.prod_name,
       SUM(s.quantity_sold) AS total_quantity
  FROM sales s
  JOIN products p ON s.prod_id = p.prod_id
 GROUP BY p.prod_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Real Time Materialized View Restrictions and things to note:

• Real-time materialized views cannot be used when:

  • one or more materialized view logs created on the base tables are either unusable or nonexistent.

  • out-of-place, log-based or PCT refresh is not feasible for the change scenarios.

  • automatic refresh is specified using the ON COMMIT clause.

• If a real-time materialized view is a nested materialized view that is defined on top of one or more base materialized views, then query rewrite occurs only if all the base materialized views are fresh. If one or more base materialized views are stale, then query rewrite is not performed using this real-time materialized view.

For more info on Real Time Materialized Views, how to convert existing Materialized Views into Real Time Materialized Views, including data dictionary views, improving real time materialized views performance, and more, check out the docs here

Until next time

-Killian