Note 741478 - FAQ: Materialized views

1. 1. What are materialized views?

1. 2. What are the differences between views and materialized views?

1. 3. What can I use materialized views for?

1. 4. Are materialized views used in the SAP environment?

1. 5. What are the prerequisites for materialized views?

1. 6. How do I create a materialized view?

1. 7. How can I refresh a materialized view?

1. 8. How do I create a materialized view?

1. 9. Which Oracle DDIC views exist for materialized views?

1. 10. What internal objects are created that relate to materialized views?

1. 11. How are materialized views used in online reorganizations?

1. 12. What problems can occur with materialized views?

1. 13. Where do I find more information about materialized views?

This note provides answers to Frequently Asked Questions (FAQs).

1. 1. What are materialized views?

              Materialized views or snapshots are segments that contain the current or previous data of a table, a view or a join. You can update the data that was changed in the source objects since the creation of the materialized view immediately, at specific times or not at all.

              By default, materialized views as read-only.

1. 2. What are the differences between views and materialized views?

• A view always returns the current information from the source objects while a materialized view can also contain a previous state of the source objects, depending on the refresh mode.

• In a regular view, the data must be retrieved from the source objects at runtime, which can lead to long runtimes for complex joins. A materialized view, however, can be compared with a table from which the system can read the data directly without overhead for joins.

1. 3. What can I use materialized views for?

• With materialized views, you can replicate data automatically to other systems.

• The online table reorganization with DBMS_REDEFINITION is based on materialized views.

• You can cache expensive queries as materialized views and refresh them periodically (which can be compared with the table buffering in R/3).

1. 4. Are materialized views used in the SAP environment?

              Materialized views are not used in the SAP environment. The only exception is the online table reorganization with BRSPACE which uses materialized views implicitly.

              Since materialized views are not used in the R/3 system, they cannot be created in the ABAP Dictionary.

1. 5. What are the prerequisites for materialized views?

• By default, materialized views are based on primary key constraints. This means that a primary key constraint must be created on the table for this type of materialized view - otherwise ORA-12014 occurs. Alternatively, you can generate a materialized view based on the ROWID.

• If you want to refresh the materialized view automatically, you must set the JOB_QUEUE_PROCESSES parameter to 1 or a higher value.

• To create a materialized view, the database user must have the CREATE MATERIALIZED VIEW authorization.

1. 6. How do I create a materialized view?

              You can create a simple materialized view with the following command:

CREATE MATERIALIZED VIEW MY_MAT_VIEW
<refresh_mode>
AS <select_statement>;

              <select_statement> contains the query that is used to create the materialized view.

              Refer to the Oracle online documentation for more complex definitions of materialized views. The possible values for <refresh_mode> are listed in the answer to the next question.

1. 7. How can I refresh a materialized view?

              There are multiple refresh options that you can specify in <refresh_mode>. First of all, there are FAST, COMPLETE and FORCE refresh operations:

• FAST: The data that you change in the source objects is logged in a log file for the materialized view. When you refresh, the system only needs to transfer the data to the materialized view. This method is used in the online reorganization. For a FAST refresh, the materialized view query must not be too complex and a log file for the materialized view must exist.

• COMPLETE: The materialized view is rebuilt completely during the refresh operation.

• FORCE: If possible, the system performs a FAST refresh, otherwise, it executes a COMPLETE refresh (default).

              In addition, you can specify a time for the refresh operation. For example:

• NEVER REFRESH: The refresh is not performed automatically.

• REFRESH FAST ON COMMIT: A FAST refresh is performed with every Commit.

• REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/48: A COMPLETE refresh is performed as of now every 30 minutes (30 minutes equals 1 day multiplied by 1/48).

• REFRESH FORCE START WITH TRUNC(SYSDATE) NEXT TRUNC(SYSDATE)+1/4: A FORCE refresh is performed at 12 am, 6 am, 12 pm and 6 pm.

1. 8. How do I create a materialized view?

              You can use the following command to create a materialized view log for a table:

CREATE MATERIALIZED VIEW LOG ON <table>;

1. 9. Which Oracle DDIC views exist for materialized views?

• DBA_MVIEWS: Overview over the materialized views.

• DBA_MVIEW_LOGS: Overview over the log files for the materialized views.

1. 10. What internal objects are created that relate to materialized views?

              If you create a primary key constraint that is not supported by an existing index as the basis for a materialized view, Oracle creates an index internally with the prefix SYS_C.

              When you create a materialized view log, the system creates the objects MLOG$_<table> and RUPD$_<table>. MLOG$_<table> is the materialized view log which records all changes to the underlying table.

              When you create a ROWID based materialized view, the system creates an I_SNAP$_<table> index for the materialized view, which receives the column M_ROW$$.

1. 11. How are materialized views used in online reorganizations?

              If you use BRSPACE to execute an online reorganization based on the Oracle package DBMS_REDEFINITION (refer to Note 646681), then materialized views are also implicitly used due to the implementation of DBMS_REDEFINITION. In general, what happens is this:

• The target table with the name conention <table>#$ (see Note 541538) is treated as a materialized view of the source table <table>.

• The program creates a materialized view log for the source table <table>. This means that the system creates the objects MLOG$_<table> and RUPD$_<table> implicitly.

• If a primary index exists for the table to be reorganized, the online reorganization is executed on the basis of a primary key constraint. This means that the materialized view is also created on the basis of a primary key constraint. In turn, this means that an I_SNAP$_<table>#$ index is not necessary.

• If no primary index exists, the reorganization takes place on the basis of a ROWID materialized view, so the I_SNAP$_<table>#$ is implicitly created.

1. 12. What problems can occur with materialized views?

• Problems with the table owner's default tablespace

           All implicit objects such as MLOG$_<table>, RUPD$_< table> and I_SNAP$_<table>#$ are created in the table owner's default tablespace. This may cause tablespace overflows and MAXEXTENTS problems under some circumstances, for example:

ORA-12008: error in materialized view refresh path
ORA-01632: max # extents (505) reached in index
          SAPR3.I_SNAP$_<table>
ORA-01652: unable to extend temp segment in tablespace <tsp>

           Therefore, take not of the following during the online reorganization:

• Ensure that there is sufficient free space available in the table owner's default tablespace, and that problems relating to MAXEXTENTS can be avoided (such as by setting MAXEXTENTS -> UNLIMITED).

• Avoid reorganizing large tables that have no primary index, since this means that the program must create large I_SNAP$ indexes implicitly.

• Avoid online reorganizations in parallel with massive data chagnes to the tables to be reorganized, since otherwise the MLOG$ table grows very large.

• Increased Redo log count

           For each periodic COMPLETE refresh of a materialized view, the system creates Redo information which can lead to a significant Redo log count.

• ORA-12014: table does not contain a primary key constraint

           If you create a primary key materialized view for a table without primary key constraint, the system issues an ORA-12014 error message.

           You can create a primary key constraint with the following command:

ALTER TABLE <table> ADD PRIMARY_KEY (<column_list>);

• ORA-12028: materialized view type is not supported by master site

           The system issues this error when you create materialized views with an Oracle databases Release 7 or earlier. If the system issues the error message when you reorganize a database version 9i or later online, parts of the Oracle DDICs may not have a current status yet or old Oracle packages may still exist under users such as SAPR3 or SAP<sid>.

• ORA-12091: cannot online redefine table with materialized views

           If a materialized view or a log file for a materialized view already exists for a table, an online reorganization will lead to an ORA-12091 error message. The error occurs if the online reorganization for a table is stopped improperly and you then try to restart the online reorganization. To solve the problem, you must clean up the rest of the previous online reorganization as follows:

brspace -u / -f tbreorg -t <table_name> -a cleanup

           If you want to clean up the remainder of a large quantity of tables, you can also use "-t". However, this causes long runtime for the cleanup.

• ORA-12092: cannot online redefine replicated table

           You are trying to reorganize a materialized view online. This is not possible.

• ORA-12096: error in materialized view log on <table>

           If problems occur when you try to access a log file for a materialized view, the system issues an ORA-12096 error message which is followed by the actual error. If the ORA-12096 error message is accompanied by an ORA-00942 message, the problem may be due to an online reorganization that was only partially undone. In this case, you can drop the log file for the materialized view manually (after you have verified that it is no longer required):

DROP MATERIALIZED VIEW LOG ON <table>;

• ORA-23413: table does not have a materialized view log

           If you try to create a FAST materialized view for a table without materialized view log, the system issues an ORA-23413 error message. The name of the materialized view log is always MLOG$_<table> and you can create the log with the following command:

CREATE MATERIALIZED VIEW LOG ON <table>;

1. 13. Where do I find more information about materialized views?

              Refer to the Oracle online documentation for detailed information about materialized views:

Oracle9i Advanced Replication
-> 3 Materialized view Concepts and Architecture

Oracle9i SQL Reference
-> CREATE MATERIALIZED VIEW

              Refer to note 646681 for information about the online reorganization with BRSPACE