This chapter describes how to use the materialized view provided by Tibero.
A refresh updates a materialized view to apply changes that occurred in the master table. After performing a refresh, data in the materialized view is synchronized with the result of the query. The refresh procedure can be invoked automatically based on conditions for generating a materialized view or manually invoked using the REFRESH function in the DBMS_MVIEW package.
There are two types of refresh operations: complete refresh and fast refresh.
A complete refresh deletes all existing data in a materialized view and then executes the query that defines the materialized view. The query result is saved as the materialized view.
There are no options when using a complete refresh.
A fast refresh only applies updates from the master table to a materialized view. This method is faster than a complete refresh.
A materialized view should meet certain constraints to use fast refresh.
A materialized view cannot contain:
Unrepeatable functions such as SYSDATE and ROWNUM.
RAW or LONG RAW data columns.
A subquery in select_list.
An analytic function in select_list.
A subquery in the HAVING clause.
The condition ANY, ALL, or NOT EXISTS.
A [START WITH] CONNECT BY clause.
Remote tables from multiple servers. All tables should be from a single server.
A remote table can be included only if the server is Tibero.
A SET command like UNION, etc
The query that defines the REFRESH ON COMMIT materialized view cannot contain remote tables.
All fast refresh general constraints apply.
All referenced tables for a materialized view must contain logs of the materialized view and logs for the materialized view should meet the following conditions:
Must contain all columns which are referred to by the materialized view.
Must contain the conditions SEQUENCE, ROWID and INCLUDING NEW VALUES.
Supports only the SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX functions.
COUNT(*) must always be included.
Aggregate functions must be located at the outer level of a statement; syntax such as AVG(COUNT(X)), COUNT(X) * COUNT(X) cannot be used.
AVG(expr) cannot be used without also using COUNT(expr).
| Aggregate function | Mandatory Aggregate function |
|---|---|
| COUNT(expr) | - |
| MIN(expr) | - |
| MAX(expr) | - |
| SUM(expr) | COUNT(expr) |
| NOT NULL in SUM(col), col | - |
| AVG(expr) | COUNT(expr) |
| STDDEV(expr) | SUM(expr), COUNT(expr) |
| VARIANCE(expr) | SUM(expr), COUNT(expr) |
select_list must contain every GROUP BY column.
CUBE and ROLLUP cannot be used.
The biggest advantage of a materialized view is the query rewrite function.
The query rewrite feature analyzes a given query and then generates a new query that uses the materialized view. If the materialized view is defined by a complicated join clause or is the result of aggregate functions, query rewrite can reduce response and processing time.
Because query rewrite is invoked by the query optimizer without user intervention, it can use the materialized view in the same way that is uses an index.
This function works with all SELECT statements except subqueries defined in INSERT, DELETE, UPDATE, and MERGE statements.
When viewing an execution plan generated by the EXPLAIN PLAN statement, the materialized view used by query rewrite is displayed as MV_REWRITE.
The following conditions must be met to enable query rewrite.
Individual materialized views must have the ENABLE QUERY REWRITE clause.
The initialization parameter QUERY_REWRITE_ENABLED must be set to TRUE or FORCE.
QUERY_REWRITE_ENABLED = {TRUE |
FORCE}
Query rewrite meet the requirements of the QUERY_REWRITE_INTEGRITY parameter.
QUERY_REWRITE_INTEGRITY = {ENFORCED |
STALE_TOLERATED}
The QUERY_REWRITE_INTEGRITY parameter controls the accuracy of query rewrite using the following parameters:
| Parameter | Description |
|---|---|
| ENFORCED | Only uses only the materialized view if it has updated data. This ensures that it will produce the exact same result as the original query. |
| STALE_TOLERATED | Uses the materialized view even if it has outdated data. As such, it does not guarantee the same result as the original query, but it does allow query rewrites to occur more often. |
The optimizer uses a number of different methods to rewrite a query. The first and most important step is to determine if all or part of the results requested by the query can be obtained from the precomputed results stored in a materialized view.
Currently, the following two types of matching methods are supported:
For full text match, the entire text of a query's select expression is compared with the entire text of a materialized view's definition while ignoring white space.
For example, suppose that there is a materialized view as defined below:
CREATE MATERIALIZED VIEW MV_SUM_SALARY
ENABLE QUERY REWRITE AS SELECT DNAME, SUM(SALARY) FROM DEPT, EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME;
If the following query occurs:
SELECT dname, SUM(salary) FROM dept,emp WHERE
DEPT.DEPTNO = EMP.DEPTNO GROUP BY dname;
The query will be rewritten as:
SELECT * FROM MV_SUM_SALARY;
For this method, the text starting from the FROM clause of a query to the beginning of the ORDER BY clause, if one exists, is compared to the text starting with the FROM clause of the materialized view's definition. Then, the compatibility of columns used in select_list and the ORDER BY clause is examined.
This method checks column compatibility as follows:
It checks rewrite integrity to determine if the columns in select_list of the original query can be obtained by combining columns from the materialized view. It then processes columns that match as a result of a join operation.
For example, suppose that there is a materialized view as defined below:
CREATE MATERIALIZED VIEW MV_JOIN_DEPT_EMP
ENABLE QUERY REWRITE AS SELECT ENAME, DNAME, DEPT.DEPTNO FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO;
If the following query occurs:
SELECT ENAME, DNAME, EMP.DEPTNO FROM DEPT, EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO;
The query will be rewritten as:
SELECT ENAME, DNAME, DEPTNO FROM
MV_JOIN_DEPT_EMP;
It then processes columns of identical queries using the commutative, distributive, and associative properties.
For example, suppose that there is a materialized view as defined below.
CREATE MATERIALIZED VIEW mymv ENABLE
QUERY REWRITE AS SELECT a*(c+4)+b c1, sum(c+a)+1 c2 FROM base GROUP BY
a*(c+4)+b, b+4*a+a*c;
If the following query occurs:
SELECT b+4*a+a*c+(1+sum(a+c))*7 FROM base GROUP BY
a*(c+4)+b, b+4*a+a*c;
The query will be rewritten as:
SELECT c1+c2*7 FROM mymv;
When using a aggregate function, the method for checking aggregate function compatibility follows:
Even if a certain aggregate function is unavailable, it is it still possible to use it by combining other aggregate functions. For example, AVG(SALARY) can be produced using a combination of SUM(SALARY) and COUNT(SALARY).
The compatibility of aggregate functions are shown in the following table:
| Target aggregate function | Necessary aggregate functions |
|---|---|
| AVG(expr) | SUM(expr), COUNT(expr) |
| SUM(expr) | AVG(expr), COUNT(expr) |
| STDDEV(expr) | VARIANCE(expr) |
| Any of STDDEV_SAMP(expr), STDDEV_POP(expr), VAR_SAMP(expr), and VAR_POP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) | |
| STDDEV_SAMP(expr) | VAR_SAMP(expr) |
| Any of STDDEV(expr), STDDEV_POP(expr), VARIANCE(expr), and VAR_POP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) | |
| STDDEV_POP(expr) | VAR_POP(expr) |
| Any of STDDEV(expr), STDDEV_SAMP(expr), VARIANCE(expr), and VAR_SAMP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) | |
| VARIANCE(expr) | STDDEV(expr) |
| Any of STDDEV_SAMP(expr), STDDEV_POP(expr), VAR_SAMP(expr), and VAR_POP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) | |
| VAR_SAMP(expr) | STDDEV_SAMP(expr) |
| Any of STDDEV(expr), STDDEV_POP(expr), VARIANCE(expr), and VAR_POP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) | |
| VAR_POP(expr) | STDDEV_POP(expr) |
| Any of STDDEV(expr), STDDEV_SAMP(expr), VARIANCE(expr), and VAR_SAMP(expr), or COUNT(expr) | |
| SUM(expr * expr), SUM(expr), COUNT(expr) |
Execution plans of the original query and the rewritten query are created using cost-based optimization. Between the two, the lowest cost alternative is selected.
Since the final selection in a query rewrite can vary depending on the cost, statistical information for both tables gets created one for the original query and the other for the materialized view.
This function is used to synchronize the base tables of a (different) database with Tibero
As described previously, assume that A is the database that has base tables and B is an Oracle server that has tables that store materialized views.
The following tasks must be completed before creating a materialized view.
Install the required PSM package by executing the $TB_HOME/scripts/mview_remote_install.sql script provided by Tibero with a user account in B.
Create a database link from A to B.
Since only the PREBUILT option is supported, storage tables must be created in advance.
A creates a materialized view, while B stores the actual tables to query.
Specify a database link from A to B by adding the AT dblink_name attribute when CREATE MATERIALIZED VIEW is used. For more information about the syntax, refer to “7.40. CREATE TABLE”
Refreshing a materialized view is performed in A.
The refresh results are stored in the storage tables of B.
Other operations are the same as general materialized views.
The following are the constraints of materialized views that have remote storage.
Does not support materialized views in which base tables are in one database while storage tables are in Tibero.
To enable fast refresh, materialized views must be based on a single base table that does not contain aggregate functions.
Other constraints are the same as general materialized views.