Creating and Editing Materialized Views

Go Up to Creating and Editing Database Dependent Objects

The performance of data warehousing and decision support system tools can be significantly increased when materialized views are used. Materialized views increase the speed of queries that access many records, allowing you to query terabytes of data in seconds. A materialized view is a pre-computed table comprising aggregated or joined data from fact and possibly dimension tables which is also known as a summary or aggregate table. An example of such a pre-defined query would be the sum of sales by region for a specific period of time. The storage requirements of the pre-computed summations and data joins of a materialized view are small compared to the original source data. A materialized view creates a real table that can be indexed and analyzed.

The following database platforms support materialized views:

• Microsoft Azure Synapse Analytics
• Oracle

Note: The Oracle Materialized View Wizard and Oracle Materialized View Editor share the same options, except for PreSQL & PostSQL, Permissions, and Attachment Bindings options which are available only in the editor.

• Snowflake

Create a Materialized View

1. In the Data Model Explorer, expand the Physical Main Model, right-click the Materialized Views node, and then click New Materialized View.
2. Complete the Oracle Materialized View Wizard and then click Finish to create the materialized view.

Tip: Once you have created the materialized view, you can edit it by right-clicking the materialized view you want to change, and then clicking Edit Materialized View.

Name page/tab

Users already defined in the logical or physical model can be selected from the list or owners for the materialized view.

Refresh page/tab

• How should the materialized view be refreshed? Choose how the materialized view is synchronized with respect to the base tables it was created from. The query defining the materialized view determines which refresh method can be used; the materialized view can always be rebuilt from scratch but it may not always be possible to perform a fast refresh on it. For more information, see Requirements for the Fast Refresh Method.
  • Complete: Build from scratch.
  • Fast: Apply only the changes.
  • Force: Perform a fast refresh if possible, if not possible perform a complete refresh.
  • Never: Do not refresh the view automatically.
• Choose a refresh mechanism: Choose when the materialized view is refreshed.
  • ON DEMAND: If selected, you can run a build-in Oracle procedure to refresh the materialized view such as DBMS_MVIEW.REFRESH (refreshes specific materialized view), DBMS_MVIEW.REFRESH_DEPENDENT (refreshes materialized views associated with specific base tables) or DBMS_MVIEW.REFRESH_ALL_MVIEWS (refreshes all materialized views).
  • ON COMMIT: If selected, the materialized view is refreshed when a transaction is committed that updates tables referenced in the materialized view.
  • Automatically: If selected, Oracle refreshes the materialized view at the specified date and time or the specified frequency.
• Select a refresh method: If this materialized view is based on the Primary Key, it allows the master tables of the view to be reorganized without affecting the eligibility of the view for fast refresh. In this case the master table must contain a primary key constraint. Select Rowid if the materialized view is organized based on the RowID, which is useful if the view does not include all primary key columns of the master tables.
• Primary key: If selected, the materialized view master tables can be reorganized without affecting its ability to be refreshed using the Fast refresh method. Select this method only if the master table contains an enabled primary key constraint

Query page/tab

• Select a pre-defined tablespace from the list or specify the name of another tablespace where you want the materialized view placed.
• Specify Default for the rollback segment if you want Oracle to select the rollback segment to use. Default is most useful when modifying a materialized view.
• One Master Rollback Segment is stored for each materialized view and is validated when the materialized view is created and refreshed. The master rollback segment is ignored if the materialized view is complex.
• What is the materialized view query? Enter the SQL query to be used to populate and to refresh the materialized view. The SQL must be valid to create the materialized view.

Transaction page/tab

• Initial transactions: (INITTRANS) Specifies the number of DML transactions for which space is initially reserved in the data block header. Oracle stores control information in the data block to indicate which rows in the block contain committed and uncommitted changes. The amount of history that is retained is controlled by this parameter. If too many transactions concurrently modify the same data block in a very short period, Oracle may not have sufficient history information to determine whether a row has been updated by a 'too recent' transaction. Specify a higher value for materialized views based on tables that may experience many transactions updating the same blocks.
• Max transactions: (MAXTRANS) Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of any available free space. Once allocated, this space effectively becomes a permanent part of the block header. This parameter limits the number of transaction entries that can concurrently use data in a data block and therefore limits the amount of free space that can be allocated for transaction entries in a data block.
• Percent Free: (PCTFREE) Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that will not be updated should have this value set to 0.
• Percent Used: (PCTUSED) Specifies the maximum percentage of available space in each data block before re-adding it to the list of available blocks. When deletes take place and the room available in a block falls below this value, the block is made available for new inserts to take place. Materialized views that will not be updated should have this value set to 99. The default value is 40% means that blocks are available for insertion when they are less than 40% full.

Extent page/tab

• Initial Extent: (INITEXTENT) Specifies the initial number of data blocks that Oracle should reserve. Oracle will reserve the number of data blocks that correspond to the initial extent for that table's rows.
• Next Extent: (NEXT) Specifies the size in kilobytes of the next extent. Monitor this figure against the largest available chunk of free space in the tablespace. If a table cannot allocate its next extent, it will no longer be able to extend and, therefore, cannot accept additional data.
• Percent Increase: (PCTINCREASE) Specifies the percentage by which the next extent should grow over the previous extent's size. Be careful when setting Percent Increase because it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace.
• Min Extents: (MINEXTENTS) Specifies the number of extents to allocate when the segment is created. Controls free space fragmentation by making sure that every used or free extent is at least as large as the value you specify.
• Max Extents: (MAXEXTENTS) Specifies the maximum number of extents that Oracle can allocate to the materialized view. Once this limit is reached, Oracle prevents further growth of the cluster and cannot accept additional data. As a result, you should carefully monitor the number extents already allocated to the table with this limit.

Options page/tab

• Do you want to register a prebuilt table to the view? If selected, registers and existing table as a pre-initialized materialized view, which is particularly useful when registering large materialized views in a data warehousing environment.
• Should the materialized view be immediately filled? If not selected, the materialized view is populated during the next refresh operation.
• Should data for the materialized view be cached? If selected, Oracle puts data accessed frequently at the most recently used end of the list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
• Is the materialized view eligible for query rewrite? Only enable query rewrite if expressions in the statement are repeatable.
• How many threads should be in a parallel operation? Enter an integer to set the default degree of parallelism for queries and DML on the materialized view after creation.
• Is the materialized view compressed? If selected, compresses data segments to reduce disk and memory use.
• What is the clustering expression?
• Is the materialized view secure? If selected, specifies that the view is secure.

The following are the two distribution options supported by Microsoft Azure Synapse Analytics:

• HASH. If HASH is selected, the textbox for the Distribution column name is enabled for editing.
• ROUND_ROBIN.

Definition page/tab

Enter or edit a definition for the materialized view. If the target database supports it, ER/Studio Data Architect adds this definition as a materialized view comment when generating SQL code.

DDL page/tab

Displays the CREATE MATERIALIZED VIEW statement needed to build the materialized view. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the materialized view.

PreSQL & PostSQL tab

Lets you enter SQL to be applied before or after the CREATE MATERIALIZED VIEW statement. The PreSQL and PostSQL scripts entered here are included in the script when you generate the physical database.

Permissions tab

Sets access roles and user permissions for the materialized view. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The Roles and Users must be previously assigned to appear in the Materialized View Editor. For more information, see Creating and Editing Database Roles and Creating and Editing Database Users.

Attachment Bindings tab

Bind an external piece of information, or attachment to the materialized view. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

Requirements for the Fast Refresh Method

Materialized View Characteristics	Fast Refresh Method is Supported When the Materialized View has:
	Only Joins	Joins and Aggregates	An Aggregate on a Single Table
Detail tables only	X	X	X
Single table only			X
Table appears only once in the FROM list	X	X	X
No non-repeating expressions like SYSDATE and ROWNUM	X	X	X
No references to RAW or LONG RAW	X	X	X
No GROUP BY	X
Rowids of all detail tables appear in the SELECT list of the query	X
Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same		X	X
Aggregates allowed but cannot be nested		X	X
AVG with COUNT		X	X
SUM with COUNT			X
VARIANCE with COUNT and SUM		X	X
STDDEV with COUNT and SUM		X	X
WHERE clause contains join predicates which can be ANDed bit not ORed.	X	X
No WHERE clause			X
No HAVING or CONNECT BY	X	X	X
No subqueries, inline views, or set functions like UNION or MINUS	X	X	X
COUNT(*) must be present			X
No MIN and MAX allowed			X
If outer joins, then unique constraints must exist on the join columns of the inner join table	X
Materialized view logs must exist and contain all columns referenced in the materialized view and have been created with the LOG NEW VALUES clause			X
Materialized view logs must exist with rowids of all the detail tables	X
Non-aggregate expression in SELECT and GROUP BY must be straight columns			X
DML to detail table	X		X
Direct path data load	X	X	X
ON COMMIT	X		X
ON DEMAND	X	X	X

See Also

• Creating and Editing Materialized Query Tables
• Creating and Editing Database Users
• Attaching External Documents to the Data Model