1. Using Oracle Database Actions for On-Premises Oracle Database
  2. The SQL and Data Modeler Pages
  3. Creating and Editing Database Objects
  4. The Table Properties Dialog
  5. Materialized View Pane

Materialized View Pane

Specifies options for a materialized view.

Query: Contains the SQL code for the query part of the view definition. Type or copy and paste the query.

General

  • On Pre-built Table: If Yes, an existing table is registered as a preinitialized materialized view. This option is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view, and the table should reflect the materialization of a subquery.

  • Reduced Precision: Yes authorizes the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by the subquery. If No , the precision of the table or materialized view columns must exactly match the precision returned by the subquery, or the create operation will fail.

  • For Update: Select Yes to allow a subquery, primary key, object, or rowid materialized view to be updated. When used in conjunction with Advanced Replication, these updates will be propagated to the master.

  • Real Time MV: Select Yes to create a real-time materialized view or a regular view. A real-time materialized view provides fresh data to user queries even when the materialized view is not in sync with its base tables due to data changes. Instead of modifying the materialized view, the optimizer writes a query that combines the existing rows in the materialized view with changes recorded in log files (either materialized view logs or the direct loader logs). This is called on-query computation.

  • Query Rewrite: If Enable, the materialized view is enabled for query rewrite, which transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.

  • Build: Specifies when to populate the materialized view. Immediate indicates that the materialized view is to be populated immediately. Deferred indicates that the materialized view is to be populated by the next refresh operation. If you specify Deferred, the first (deferred) refresh must always be a complete refresh; until then, the materialized view has a staleness value of unusable, so it cannot be used for query rewrite.

  • Use Index: If Yes, a default index is created and used to speed up incremental (fast) refresh of the materialized view. If No, this default index is not created. (For example, you might choose to suppress the index creation now and to create such an index explicitly later.)

  • Index Tablespace: Specifies the tablespace in which the materialized view is to be created. If a tablespace is not selected, the materialized view is created in the default tablespace of the schema containing the materialized view.

  • Cache: If Yes, the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This setting is useful for small lookup tables. If No, the blocks are placed at the least recently used end of the LRU list.

Refresh Clause

  • Refresh: Select Yes to enable refresh operations.

  • Refresh Type: The method of refresh operation to be performed:

    • Complete Refresh: Executes the defining query of the materialized view, even if a fast refresh is possible.

    • Fast Refresh: Uses the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table. The changes for direct-path INSERT operations are stored in the direct loader log.

    • Force Refresh: Performs a fast refresh if one is possible; otherwise, performs a complete refresh.

  • Action: The type of refresh operation to be performed:

    • On Demand: Performs a refresh when one of the DBMS_MVIEW refresh procedures are called.

    • On Commit: Performs a fast refresh whenever the database commits a transaction that operates on a master table of the materialized view. This may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

    • Specify: Performs refresh operations according to what you specify in the Start on and Next fields.

  • Start Date: Starting date and time for the first automatic refresh operation. Must be in the future.

  • Next Date: Time for the next automatic refresh operation. The interval between the Start on and Next times establishes the interval for subsequent automatic refresh operations. If you do not specify a value, the refresh operation is performed only once at the time specified for Start on.

  • With: Refresh type, which determines the type of materialized view:

    • Primary Key: Creates a primary key materialized view, which allows materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

    • Row ID: Creates a rowid materialized view, which is useful if the materialized view does not include all primary key columns of the master tables.

  • Default Storage: If Yes, DEFAULT specifies that Oracle Database will choose automatically which rollback segment to use. If you specify DEFAULT, you cannot specify the rollback_segment. DEFAULT is most useful when modifying, rather than creating, a materialized view.

  • Storage Type: MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view. LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. This is the default.

  • Rollback Segment: Enter the name of the rollback segment.

  • Using Constraint: If this option is checked, more rewrite alternatives can be used during the refresh operation, resulting in more efficient refresh execution. The behavior of this option is affected by whether you select Enforced or Trusted.

    • Enforced: Causes only enforced constraints to be used during the refresh operation.

    • Trusted: Enables the use of dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status.