Performance Tuning   «Prev  Next»

Lesson 3Understanding the syntax of creating a materialized view
ObjectiveDescribe the parameters and what they mean in the CREATE MATERIALIZED VIEW command.

Understanding the "CREATE MATERIALIZED VIEW" Command in Oracle 13c

I am working as an Oracle DBA using Oracle 13c. Describe the parameters and what they mean in the "CREATE MATERIALIZED VIEW" command.
In Oracle 13c, a materialized view is a database object that contains the results of a query. Unlike a regular view, which does not store data, a materialized view contains the data produced by the query at the time the materialized view is refreshed. This can significantly improve query performance by precomputing and storing aggregate data, such as sums and averages.
The `CREATE MATERIALIZED VIEW` command is used to create a materialized view. This command comes with a plethora of parameters that allow fine-tuning of the materialized view's behavior and characteristics.
  1. Syntax
    CREATE MATERIALIZED VIEW [schema.]materialized_view_name
[ { OF [schema.]object_type
   | ( {column_datatype [,column_datatype]...} ) }
   [ {physical_properties}
   [TABLESPACE tablespace]
   [STORAGE storage_clause]
   [USING INDEX [physical_attributes_clause] [TABLESPACE tablespace]]
   [ {REFRESH [FAST | COMPLETE | FORCE]
      [START WITH date] [NEXT {date | expr}]
      [WITH {PRIMARY KEY | ROWID} | WITH OBJECT ID]}
   | {ON DEMAND | ON COMMIT} ]
   [ {ENABLE | DISABLE} QUERY REWRITE ]
   [PARALLEL integer]
   [BUILD {IMMEDIATE | DEFERRED}]
   [WITH {REDUCED PRECISION | FULL}
   [AS subquery];
```
  2. Key Parameters and Their Meanings
    • OF [schema.]object_type: Specifies the object type for the materialized view's rows.
    • physical_properties: Physical attributes of the materialized view.
    • TABLESPACE tablespace: Specifies the tablespace in which the materialized view is to be stored.
    • STORAGE storage_clause: Specifies the storage characteristics of the materialized view.
    • USING INDEX: Specifies the storage characteristics of the index associated with the materialized view.
    • REFRESH [FAST | COMPLETE | FORCE]: Determines how the materialized view will be refreshed:
      1. FAST: Incrementally updates the materialized view by identifying the changes made to the base tables.
      2. COMPLETE: Completely recalculates the materialized view using the associated query.
      3. FORCE: Tries a `FAST` refresh; if that's not possible, it does a `COMPLETE` refresh.
    • START WITH date: Specifies the first automatic refresh time.
    • NEXT {date | expr}: Specifies the interval between automatic refreshes.
    • WITH {PRIMARY KEY | ROWID} | WITH OBJECT ID: Specifies how rows in the materialized view are identified with rows in the master table.
    • ON DEMAND | ON COMMIT: Determines when the materialized view is refreshed:
      1. ON DEMAND: Refreshed manually by the DBA.
      2. ON COMMIT: Refreshed automatically upon commit in the master table.
    • ENABLE | DISABLE QUERY REWRITE: Determines whether the materialized view can be used by the query optimizer to rewrite the SQL query.
    • PARALLEL integer: Specifies the degree of parallelism for the creation of the materialized view.
    • BUILD {IMMEDIATE | DEFERRED}: Determines when the materialized view is populated:
      1. IMMEDIATE: Populated immediately upon creation. -
      2. DEFERRED: Populated the next time the materialized view is refreshed.
    • WITH {REDUCED PRECISION | FULL}: Specifies whether the datatype of the columns in the materialized view should have the same precision as the master table or reduced precision.
    • AS subquery: The SQL query whose result populates the materialized view.
The `CREATE MATERIALIZED VIEW` command in Oracle 13c offers a robust set of parameters that provide DBAs with granular control over the behavior, storage, and refresh mechanisms of the materialized view. Properly configured materialized views can significantly enhance query performance and provide efficient data access patterns for complex analytical workloads.

Understanding the Syntax of Creating Materialized View

You create a materialized view by using Data Definition Language (DDL), a form of SQL that is used for defining all types of database objects.

Materialized View Syntax

The basic syntax for creating a materialized view is shown in the following tooltip:
Apply, Filter, Sort 
CREATE MATERIALIZED VIEW name
  storage_clause
  [PARALLEL]
  build_clause
  refresh_clause refresh_type
  ENABLE/DISABLE QUERY REWRITE
AS
  SELECT_statement
  1. Required keywords
  2. An optional keyword that specifies if parallelism is allowed for queries on this materialized view.
  3. A unique name for the materialized view
  4. A clause that describes the storage characteristics of the view.
  5. A clause that describes how and how often a materialized view will be refreshed.
  6. Keywords that specify if a materialized view will be used for automatic query rewrites.
  7. A required keyword.
  8. The SELECT statement used to populate the materialized view.
  9. A clause that defines when a materialized view will be built.

Syntax Materialized View Creation
The following table explains the meaning of the different clauses used in the CREATE MATERIALIZED VIEW statement:

Clause Meaning
storage_clause Specifies the storage characteristics of the materialized view, which can be the same as the characteristics of a normal view or table.
build_clause Specifies if the materialized view is to be built as soon as the CREATE statement is committed with the value of BUILD IMMEDIATE, or if the building of the view is deferred until a later time when it is specifically invoked with the value of BUILD DEFERRED.
refresh_clause / refresh_type Specifies how and how often the materialized view is refreshed from the base tables. refresh_clause indicates when a materialized view should be refreshed. The acceptable values for this part of the clause are ON COMMIT, which forces a refresh each time a change to one of the tables is committed, or ON DEMAND, which will update the view only when specifically requested and is the default.
refresh_clause also includes a refresh type. A materialized view can be refreshed in four basic ways: COMPLETE, which will cause all the values for the view to be refreshed; FAST, which refreshes a view with incremental changes since the last refresh; FORCE, which will try to perform a FAST refresh and, if that is not possible, will do a COMPLETE refresh; and NEVER, which will prevent a materialized view from ever being refreshed.
ENABLE/DISABLE QUERY REWRITE Specifies whether to enable or disable automatic query rewrites for queries that could be fulfilled with this view. Query rewrites are discussed in detail later in this module. DISABLE QUERY REWRITE is the default for this choice.

Required System Privileges

To create a materialized view, you must have the privileges needed to create the underlying objects it will use. You must have the CREATE MATERIALIZED VIEW privilege, as well as the CREATE TABLE or CREATE ANY TABLE system privilege. In addition, you must have either the UNLIMITED TABLESPACE system privilege or a sufficient specified space quota in a local tablespace. To create a refresh-on-commit materialized view, you must also have the ON COMMIT REFRESH system privilege on any tables you do not own, or the ON COMMIT REFRESH system privilege. Materialized views of remote tables require queries of remote tables; therefore, you must have privileges to use a database link that accesses the remote database. The link you use can be either public or private. If the database link is private, you need to have the CREATE DATABASE LINK system privilege to create the database link.
If you are creating materialized views to take advantage of the query rewrite feature (in which the optimizer dynamically chooses to select data from the materialized view instead of the underlying table), you must have the QUERY REWRITE privilege. If the tables are in another user's schema, you must have the GLOBAL QUERY REWRITE privilege. If the materialized view is created with on commit refresh specified, you must have the ON COMMIT REFRESH system privilege or the ON COMMIT REFRESH object privilege on each table outside your schema.

Note: As of Oracle 11g, queries that reference remote tables can support query rewrite.

Create Materialized View Command - Exercise

Click the link below to match clauses with their purposes.
Create Materialized View Command - Exercise

In the next lesson, you will learn how to create a materialized view for summary data.
SEMrush Software