Purpose
Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these are called master tables (a
detail tables (a data warehouse term). This reference replication term) or
uses \"master tables\" for consistency. The databases containing the master tables are called the master databases.
Note:
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. The copies can be updatable with the Advanced Replication feature and are read-only without this feature. You can select data from a materialized view as you would from a table or view. In replication environments, the materialized views commonly created are primary key, rowid, object, and subquery materialized views.
See Also:
Oracle9i Advanced Replicationfor information on the types
of materialized views used to support replication
For data warehousing purposes, the materialized views commonly created are materialized aggregate views, single-table materialized aggregate views, and materialized join views. All three types of materialized views can be used by query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.
See Also:
• •
ALTER MATERIALIZED VIEW
Oracle9i Data Warehousing Guidefor information on the types of
materialized views used to support data warehousing
Additional Topics
• • • •
Prerequisites Syntax Semantics Examples
Prerequisites
The privileges required to create a materialized view should be granted directly rather than through a role.
To create a materialized view in your own schema:
• •
You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create a materialized view in another user's schema:
• •
You must have the CREATE ANY MATERIALIZED VIEW system privilege.
The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database), and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.
To create the materialized view with query rewrite enabled, in addition to the preceding privileges:
• • •
The owner of the master tables must have the QUERY REWRITE system privilege.
If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege or the QUERY REWRITE object privilege on each table outside your schema. If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table
•
outside the schema.
If you are defining the materialized view on a prebuilt container (ON PREBUILT TABLE), you must have the SELECT privilege WITH GRANT OPTION on the container table.
The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the materialized view's master table and index or must have the UNLIMITED TABLESPACE system privilege. When you create a materialized view, Oracle creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle uses these objects to maintain the materialized view's data. You must have the privileges necessary to create these objects.
See Also:
• • •
CREATE TABLE, CREATE VIEW, and CREATE INDEX for information on these privileges
Oracle9i Advanced Replicationfor information about the prerequisites that apply to creating replication materialized views
Oracle9i Data Warehousing Guide for information about the prerequisites that apply to creating data warehousing materialized views
Syntax
create_materialized_view::=
Text description of create_materialized_view(scoped_table_ref_constraint::=, index_org_table_clause::=, materialized_view_props::=, physical_attributes_clause::=, create_mv_refresh::=, subquery::=)
physical_properties::=
Text description of physical_properties(segment_attributes_clause::=, data_segment_compression::=)
materialized_view_props::=
Text description of materialized_view_props(column_properties::=, table_partitioning_clauses--part of CREATE TABLE syntax, parallel_clause::=, build_clause::=)
scoped_table_ref_constraint::=
Text description of scoped_table_ref_constraintindex_org_table_clause::=
Text description of index_org_table_clause(mapping_table_clause: not supported with materialized views, key_compression::=, index_org_overflow_clause::=)
key_compression::=
Text description of key_compressionindex_org_overflow_clause::=
Text description of index_org_overflow_clause(segment_attributes_clause::=)
create_mv_refresh::=
Text description of create_mv_refreshsegment_attributes_clause::=
Text description of segment_attributes_clause(physical_attributes_clause::=, logging_clause::=)
physical_attributes_clause::=
Text description of physical_attributes_clause(logging_clause::=)
logging_clause::=
Text description of logging_clausedata_segment_compression::=
Text description of data_segment_compressioncolumn_properties::=
Text description of column_properties(object_type_col_properties::=, nested_table_col_properties::=, varray_col_properties::=, LOB_partition_storage::=, LOB_storage_clause::=, XMLType_column_properties: not supported for materialized views)
object_type_col_properties::=
Text description of object_type_col_properties(substitutable_column_clause::=)
substitutable_column_clause::=
Text description of substitutable_column_clausenested_table_col_properties::=
Text description of nested_table_col_properties(substitutable_column_clause::=, object_properties::=, physical_properties::=--part of CREATE TABLE syntax, column_properties::=)
varray_col_properties::=
Text description of varray_col_properties(substitutable_column_clause::=, LOB_parameters::=)
LOB_storage_clause::=
Text description of LOB_storage_clause(LOB_parameters::=)
LOB_parameters::=
Text description of LOB_parameters(storage_clause::=, logging_clause::=)
LOB_partition_storage::=
Text description of LOB_partition_storage(LOB_storage_clause::=, varray_col_properties::=)
parallel_clause::=
Text description of parallel_clausebuild_clause::=
Text description of build_clauseSemantics
schema
Specify the schema to contain the materialized view. If you omit schema, Oracle creates the materialized view in your schema. materialized_view
Specify the name of the materialized view to be created. Oracle generates names for the table and indexes used to maintain the materialized view by adding a prefix or suffix to the materialized view name. OF object_type
The OF type_name clause lets you explicitly create an object materialized view of type object_type.
See Also:
See the object_table clause of CREATE TABLE for more information on the OF type_name clause
scoped_table_ref_constraint
Use the SCOPE FOR clause to restrict the scope of references to a single table, scope_table_name. The values in the REF column or attribute point to objects in scope_table_name, in which object instances (of the same type as the REF column) are stored.
See Also:
\"SCOPE REF Constraints\" for more information
ON PREBUILT TABLE Clause
The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This 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.
If the materialized view is dropped, the preexisting table reverts to its identity as a table.
Caution:
This clause assumes that the table object reflects the materialization of a subquery. Oracle Corporation strongly recommends that you ensure that this assumption is true in order to ensure that the materialized view correctly reflects the data in its master tables.
WITH REDUCED PRECISION
Specify WITH REDUCED PRECISION to authorize 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 subquery.
WITHOUT REDUCED PRECISION
Specify WITHOUT REDUCED PRECISION to require that the precision of the table or materialized view columns match exactly the precision returned by subquery, or the create operation will fail. This is the default.
Restrictions on prebuilt_table_clause
• •
Each column alias in subquery must correspond to a column in table_name, and corresponding columns must have matching datatypes.
If you specify this clause, you cannot specify a NOT NULL constraint for any column that is unmanaged (that is, not referenced in subquery) unless you also specify a default value for that column.
See Also:
\"Creating Prebuilt Materialized Views: Example\"
physical_properties_clause
The components of the physical_properties_clause have the same semantics for materialized views that they have for tables, with exceptions and additions described in the sections that follow.
Restriction on the physical_properties_clause
You cannot specify ORGANIZATION EXTERNAL for a materialized view.
segment_attributes_clause
Use the segment_attributes_clause to establish values for the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters (or, when used in the USING INDEX clause, for the INITRANS and MAXTRANS parameters only), the storage characteristics for the materialized view, to assign a tablespace, and to specify whether logging is to occur.
TABLESPACE Clause
Specify the tablespace in which the materialized view is to be created. If you omit this clause, Oracle creates the materialized view in the default tablespace of the schema containing the materialized view.
See Also:
•
•
physical_attributes_clausefor a complete description of the parameters of the physical_attributes_clause, including default values
storage_clause for a complete description of the storage_clause, including default values
logging_clause
Specify LOGGING or NOLOGGING to establish the logging characteristics for the materialized view. The default is the logging characteristic of the tablespace in which the materialized view resides.
See Also:
logging_clause for a full description of this clause
data_segment_compression
Use the data_segment_compression clause to instruct Oracle whether to
compress data segments to reduce disk and memory use. The COMPRESS keyword enables data segment compression. The NOCOMPRESS keyword disables data segment compression.
See Also:
data_segment_compression clause of CREATE TABLE for more
information on data segment compression
index_org_table_clause
The ORGANIZATION INDEX clause lets you create an index-organized
materialized view. In such a materialized view, data rows are stored in an index defined on the primary key of the materialized view. You can specify index organization for the following types of materialized views:
• • •
Read-only and updatable object materialized views. You must ensure that the master table
has a primary key.
Read-only and updatable primary key based materialized views Read-only rowid materialized views.
The keywords and parameters of the index_org_table_clause have the same semantics as described in CREATE TABLE, with the restrictions that follow.
See Also:
the index_org_table_clause of CREATE TABLE
Restrictions on Index-organized Materialized Views
• •
You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or NOCACHE, CLUSTER, or ON PREBUILT TABLE. In the index_org_table_clause:
• You cannot specify the mapping_table_clause.
• You can specify COMPRESS only for a materialized view based on a composite
primary key. You can specify NOCOMPRESS for a materialized view based on either
a simple or composite primary key.
CLUSTER Clause
The ORGANIZATION CLUSTER clause lets you create the materialized view as part of the specified cluster. A clustered materialized view uses the cluster's
space allocation. Therefore, you do not specify physical attributes or the TABLESPACE clause with the CLUSTER clause.
Restriction on clustered materialized views
If you specify ORGANIZATION CLUSTER, you cannot specify the table_partitioning_clauses (in materialized_view_props). materialized_view_props
Use these property clauses to describe a materialized view that is not based on an existing table. To create a materialized view that is based on an existing table, use the ON PREBUILT TABLE clause.
column_properties
The column_properties clause lets you specify the storage characteristics of a LOB, nested table, varray, or XMLType column.
Restriction on Materialized View Columns
The object_type_col_properties are not relevant for a materialized view.
See Also:
CREATE TABLE for detailed information about specifying the parameters of this clause
table_partitioning_clauses
The table_partitioning_clauses let you specify that the materialized view is partitioned on specified ranges of values or on a hash function.
Partitioning of materialized views is the same as partitioning of tables.
See Also:
table_partitioning_clauses of CREATE TABLE
CACHE | NOCACHE
For data that will be accessed frequently, CACHE specifies that 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 attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.
Note:
NOCACHE has no effect on materialized views for which you
specify KEEP in the storage_clause.
See Also:
CREATE TABLE for information about specifying CACHE or
NOCACHE
parallel_clause
The parallel_clause lets you indicate whether parallel operations will be supported for the materialized view and sets the default degree of parallelism for queries and DML on the materialized view after creation.
Note:
The syntax of the parallel_clausesupersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior than that documented.
NOPARALLEL
Specify NOPARALLEL for serial execution. This is the default.
PARALLEL
Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
PARALLEL integer
Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
See Also:
\"Notes on the parallel_clause\" for CREATE TABLE
build_clause
The build_clause lets you specify when to populate the materialized view.
IMMEDIATE
Specify IMMEDIATE to indicate that the materialized view is populated immediately. This is the default.
DEFERRED
Specify DEFERRED to indicate that the materialized view will be populated by the next REFRESH operation. 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. USING INDEX Clause
The USING INDEX clause lets you establish the value of INITRANS, MAXTRANS, and STORAGE parameters for the default index Oracle uses to maintain the materialized view's data. If USING INDEX is not specified, then default values are used for the index. Oracle uses the default index to speed up incremental (\"fast\") refresh of the materialized view.
Restriction on USING INDEX clause
You cannot specify the PCTUSED parameter in this clause. USING NO INDEX Clause
Specify USING NO INDEX to suppress the creation of the default index. You can create an alternative index explicitly by using the CREATE INDEX statement. You should create such an index if you specify USING NO INDEX and you are creating the materialized view with the incremental refresh method (REFRESH FAST). create_mv_refresh
Use the create_mv_refresh to specify the default methods, modes, and times for Oracle to refresh the materialized view. If the master tables of a materialized view are modified, the data in the materialized view must be updated to make the materialized view accurately reflect the data
currently in its master tables. This clause lets you schedule the times and specify the method and mode for Oracle to refresh the materialized view.
Note:
This clause only sets the default refresh options. For instructions on actually implementing the refresh, refer to Oracle9i Advanced Replication and Oracle9i Data Warehousing Guide.
See Also:
\"Periodic Refresh of Materialized Views: Example\" and \"Automatic Refresh Times for Materialized Views: Example\"
FAST Clause
Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes are stored either in the materialized view log associated with the master table (for conventional DML changes) or in the direct loader log (for direct-path INSERT operations).
If you specify REFRESH FAST, the CREATE statement will fail unless
materialized view logs already exist for the materialized view's master tables. (Oracle creates the direct loader log automatically when a direct-path INSERT takes place. No user intervention is needed.) For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.
Materialized views are not eligible for fast refresh if the defining subquery contains an analytic function.
See Also:
• • •
Oracle9i Advanced Replication for restrictions on fast refresh in replication environments
Oracle9i Data Warehousing Guidefor restrictions on fast refresh in data warehouse environments \"Analytic Functions\"
•
\"Creating a Fast Refreshable Materialized View: Example\"
COMPLETE Clause
Specify COMPLETE to indicate the complete refresh method, which is
implemented by executing the materialized view's defining subquery. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
FORCE Clause
Specify FORCE to indicate that when a refresh occurs, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
ON COMMIT Clause
Specify ON COMMIT to indicate that a fast refresh is to occur whenever Oracle commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because Oracle performs the refresh operation as part of the commit process.
Restriction on Refreshing ON COMMIT
This clause is not supported for materialized views containing object types.
See Also:
Oracle9i Advanced Replication and Oracle9i Data Warehousing GuideON DEMAND Clause
Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.
See Also:
•
Oracle9i Supplied PL/SQL Packages and Types Reference for information on these procedures
•
Oracle9i Data Warehousing Guideon the types of materialized views you can create by specifying REFRESH ON DEMAND
If you specify ON COMMIT or ON DEMAND, you cannot also specify START WITH or NEXT.
START WITH Clause
Specify a date expression for the first automatic refresh time.
NEXT Clause
Specify a date expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, Oracle refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, Oracle does not automatically refresh the materialized view.
WITH PRIMARY KEY Clause
Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default, and should be used in all cases except those described for WITH ROWID. Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh. The master table must contain an enabled primary key constraint.
Restriction on Primary Key Materialized Views
You cannot specify this clause for an object materialized view. Oracle implicitly refreshes object materialized WITH OBJECT ID.
See Also:
Oracle9i Advanced Replication for detailed information
about primary key materialized views and \"Creating Primary Key Materialized Views: Example\"
WITH ROWID Clause
Specify WITH ROWID to create a rowid materialized view. Rowid materialized views provide compatibility with master tables in releases of Oracle prior to 8.0.
You can also use rowid materialized views if the materialized view does not include all primary key columns of the master tables. Rowid
materialized views must be based on a single table and cannot contain any of the following:
• • • • •
Distinct or aggregate functions GROUP BY or CONNECT BY clauses Subqueries Joins
Set operations
Rowid materialized views are not eligible for fast refreshed after a master table reorganization until a complete refresh has been performed.
Restriction on Rowid Materialized Views
You cannot specify this clause for an object materialized view. Oracle implicitly refreshes object materialized WITH OBJECT ID.
See Also:
\"Creating Materialized Aggregate Views: Example\" and \"Creating Rowid Materialized Views: Example\"
USING ROLLBACK SEGMENT Clause
Specify the remote rollback segment to be used during materialized view refresh, where rollback_segment is the name of the rollback segment to be used.
This clause is invalid if your database is in Automatic Undo Mode, because in that mode Oracle uses undo tablespaces instead of rollback segments.
DEFAULT
DEFAULT specifies that Oracle will choose automatically which rollback
segment to use. If you specify DEFAULT, you cannot specify rollback_segment.DEFAULT is most useful when modifying (rather than creating) a materialized
view.
See Also:
ALTER MATERIALIZED VIEW
MASTER
MASTER specifies the remote rollback segment to be used at the remote master
site for the individual materialized view.
LOCAL
LOCAL specifies the remote rollback segment to be used for the local refresh
group that contains the materialized view.
See Also:
Oracle9i Advanced Replication for information on
specifying the local materialized view rollback segment
using the DBMS_REFRESH package
If you do not specify MASTER or LOCAL, Oracle uses LOCAL by default. If you do not specify rollback_segment, Oracle automatically chooses the rollback segment to be used.
One master rollback segment is stored for each materialized view and is validated during materialized view creation and refresh. If the
materialized view is complex, the master rollback segment, if specified, is ignored.
See Also:
\"Specifying Rollback Segments for Materialized Views: Example\"
NEVER REFRESH Clause
Specify NEVER REFRESH to prevent the materialized view from being refreshed with any Oracle refresh mechanism or packaged procedure. Oracle will ignore any REFRESH statement on the materialized view issued from such a procedure. To reverse this clause, you must issue an ALTER MATERIALIZED VIEW ... REFRESH statement. FOR UPDATE Clause
Specify FOR UPDATE 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.
See Also:
Oracle9i Advanced ReplicationQUERY REWRITE Clause
The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.
ENABLE Clause
Specify ENABLE to enable the materialized view for query rewrite.
Restrictions on Enabling Query Rewrite
• •
You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).
Notes:
• •
Query rewrite is disabled by default, so you must specify this
clause to make materialized views eligible for query rewrite.
Be sure to analyze the materialized view after you create it.
Oracle needs the statistics generated by the ANALYZEoperation to optimize query rewrite.
See Also:
• •
Oracle9i Data Warehousing Guidefor more information on
query rewrite
CREATE FUNCTION
DISABLE Clause
Specify DISABLE to indicate that the materialized view is not eligible for use by query rewrite. However, a disabled materialized view can be refreshed. AS subquery
Specify the defining subquery of the materialized view. When you create the materialized view, Oracle executes this subquery and places the results in the materialized view. This subquery is any valid SQL subquery. However, not all queries are fast refreshable, nor are all subqueries eligible for query rewrite.
Notes on the Defining Query of a Materialized View
• •
Oracle does not execute the defining subquery immediately if you specify BUILD DEFERRED.
Oracle recommends that you qualify each table and view in the FROM clause of the defining subquery of the materialized view with the schema containing it. See Also:
\"AS subquery\" clause of CREATE TABLE for some additional caveats
Restrictions on the Defining Subquery of a Materialized View
•
• • •
The defining subquery of a materialized view can select from tables, views, or materialized views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.
Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.
Materialized views cannot contain columns of datatype LONG.
You cannot create a materialized view log on a temporary table. Therefore, if the defining subquery references a temporary table, this materialized view will not be eligible for FAST refresh, nor can you specify the QUERY REWRITE clause in this statement.
If the FROM clause of the defining subquery references another materialized view, then you must always refresh the materialized view referenced in the defining subquery before refreshing the materialized view you are creating in this statement.
•
If you are creating a materialized view enabled for query rewrite:
•
•
The defining subquery cannot contain (either directly or through a view) references to ROWNUM, USER, SYSDATE, remote tables, sequences, or PL/SQL functions that write or read database or package state.
Neither the materialized view nor the master tables of the materialized view can be
remote.
If you want the materialized view to be eligible for fast refresh using a materialized view log, some additional restrictions may apply.
See Also:
• • •
Oracle9i Data Warehousing Guidefor more information on restrictions relating to data warehousing
Oracle9i Advanced Replicationfor more information on restrictions relating to replication
\"Creating Materialized Join Views: Example\", \"Creating Subquery Materialized Views: Example\" and \"Creating a Nested Materialized View: Example\"
Examples
The following examples require the materialized logs that are created in the \"Examples\" section of CREATE MATERIALIZED VIEW.
Creating Materialized Aggregate Views: Example
The following statement creates and populates a materialized aggregate
sh.sales table and specifies the default refresh method, view on the sample
mode, and time. It uses the materialized view log created in \"Creating a Materialized View Log: Examples\", as well as the two additional logs shown here:
CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE (prod_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;
Creating Materialized Join Views: Example
The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the materialized view's query:
CREATE MATERIALIZED VIEW sales_by_month_by_state TABLESPACE example PARALLEL 4 BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc, c.cust_state_province, SUM(s.amount_sold) AS sum_sales FROM times t, sales s, customers c
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc, c.cust_state_province; Creating Prebuilt Materialized Views: Example
The following statement creates a materialized aggregate view for the preexisting summary table, sales_sum_table:
CREATE TABLE sales_sum_table
(month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2));
CREATE MATERIALIZED VIEW sales_sum_table ON PREBUILT TABLE WITH REDUCED PRECISION ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc AS month, c.cust_state_province AS state, SUM(s.amount_sold) AS sales FROM times t, customers c, sales s
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc, c.cust_state_province;
In this example, the materialized view has the same name as the prebuilt table and also has the same number of columns with the same datatypes as the prebuilt table. The WITH REDUCED PRECISION clause allows for between the precision of the materialized view columns and the precision of the values returned by the subquery.
Creating Subquery Materialized Views: Example
The following statement creates a subquery materialized view based on the customers and countries tables in the sh schema at the remote database:
CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE AS SELECT * FROM sh.customers@remote cu WHERE EXISTS
(SELECT * FROM sh.countries@remote co WHERE co.country_id = cu.country_id); Creating Primary Key Materialized Views: Example
The following statement creates the primary-key materialized view catalog on the sample table oe.product_information:
CREATE MATERIALIZED VIEW catalog
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096 WITH PRIMARY KEY
AS SELECT * FROM product_information; Creating Rowid Materialized Views: Example
The following statement creates a rowid materialized view on the sample table oe.orders:
CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID AS SELECT * FROM orders;
Periodic Refresh of Materialized Views: Example
The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees:
CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW emp_data PCTFREE 5 PCTUSED 60 TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K) REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM employees;
The statement does not include a START WITH parameter, so Oracle determines the first automatic refresh time by evaluating the NEXT value using the
SYSDATE. A materialized view log was created for the employee table, current
so Oracle performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.
Because the materialized view conforms to the conditions for fast refresh, Oracle will perform a fast refresh. The preceding statement also establishes storage characteristics that Oracle uses to maintain the materialized view.
Automatic Refresh Times for Materialized Views: Example
The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:
CREATE MATERIALIZED VIEW all_customers PCTFREE 5 PCTUSED 60 TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K) REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 AS SELECT * FROM sh.customers@remote UNION
SELECT * FROM sh.customers@local;
Oracle automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m.. The default refresh method is FORCE. all_emps contains a UNION operator, which is not supported for fast refresh, so Oracle will automatically perform a complete refresh. The preceding statement also establishes storage characteristics for both the materialized view and the index that Oracle uses to maintain it:
• •
The first establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.
The second (appearing with the USING INDEX clause) establishes the sizes of the first and second extents of the index as 25 kilobytes each.
Specifying Rollback Segments for Materialized Views: Example
The following statement creates the primary key materialized view sales_emp with rollback segment master_seg at the remote master and rollback segment snap_seg for the local refresh group that contains the materialized view.
Note:
This example is not relevant if your database is in
Automatic Undo Mode, because in that mode Oracle uses undo tablespaces instead of rollback segments.
CREATE MATERIALIZED VIEW sales_emp
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7 USING MASTER ROLLBACK SEGMENT master_seg LOCAL ROLLBACK SEGMENT snap_seg AS SELECT * FROM bar;
The following statement is incorrect and generates an error because it specifies a segment name with a DEFAULT rollback segment:
/* The following statement is invalid. */ CREATE MATERIALIZED VIEW order_mv
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7 USING DEFAULT ROLLBACK SEGMENT mv_seg AS SELECT * FROM orders;
Creating a Fast Refreshable Materialized View: Example
The following statement creates a fast-refreshable materialized view that selects columns from the order_items table in the sample oe schema, using the UNION set operator to restrict the rows returned from the
product_information and inventories tables using WHERE conditions. The
materialized view logs for order_items and product_information were created in the \"Examples\" section of CREATE MATERIALIZED VIEW LOG. This example requires a materialized view log on oe.inventories.
CREATE MATERIALIZED VIEW LOG ON inventories WITH (quantity_on_hand);
CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS SELECT order_id, line_item_id, product_id FROM order_items o WHERE EXISTS
(SELECT * FROM inventories i WHERE o.product_id = i.product_id
AND i.quantity_on_hand IS NOT NULL) UNION
SELECT order_id, line_item_id, product_id FROM order_items WHERE quantity > 5;
This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column). See \"Specifying Filter Columns for Materialized View Logs: Example\" and \"Specifying Join Columns for Materialized View Logs: Example\".
Creating a Nested Materialized View: Example
The following example uses the materialized view from the preceding example as a master table to create a materialized view tailored for a particular sales representative in the sample oe schema:
CREATE MATERIALIZED VIEW my_warranty_orders
AS SELECT w.order_id, w.line_item_id, o.order_date FROM warranty_orders w, orders o WHERE o.order_id = o.order_id AND o.sales_rep_id = 165;
CREATE MATERIALIZED VIEW LOG
Purpose
Use the CREATE MATERIALIZED VIEW LOG statement to create a materialized view log, which is a table associated with the master table of a materialized view.
Note:
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
When DML changes are made to the master table's data, Oracle stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master
table. This process is called an incremental or fast refresh. Without a materialized view log, Oracle must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh. A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
To fast refresh a materialized join view (a materialized view containing a join), you must create a materialized view log for each of the tables referenced by the materialized view.
See Also:
•
• • •
CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, Oracle9i Database Concepts, Oracle9i Data Warehousing Guide, and Oracle9i Advanced Replicationfor information on materialized views in general
ALTER MATERIALIZED VIEW LOGfor information on modifying a materialized view log
DROP MATERIALIZED VIEW LOGfor information on dropping a materialized view log
Oracle9i Database Concepts and Oracle9i Database Utilities for information on using direct loader logs
Prerequisites
The privileges required to create a materialized view log directly relate to the privileges necessary to create the underlying objects associated with a materialized view log.
• •
If you own the master table, you can create an associated materialized view log if you have the CREATE TABLE privilege.
If you are creating a materialized view log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE privileges, as well as either the SELECT privilege for the master table or SELECT ANY TABLE.
In either case, the owner of the materialized view log must have sufficient quota in the tablespace intended to hold the materialized view log or must have the UNLIMITED TABLESPACE system privilege.
See Also:
Oracle9i Data Warehousing Guide for more information about
the prerequisites for creating a materialized view log
Syntax
create_materialized_vw_log::=
Text description of create_materialized_vw_log(physical_attributes_clause::=, logging_clause::=, parallel_clause::=, table_partitioning_clauses (in CREATE TABLE), new_values_clause::=)
physical_attributes_clause::=
Text description of physical_attributes_clause(storage_clause)
logging_clause::=
Text description of logging_clauseparallel_clause::=
Text description of parallel_clausenew_values_clause::=
Text description of new_values_clauseSemantics
schema
Specify the schema containing the materialized view log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the materialized view log in the schema of its master table. You cannot create a materialized view log for a table in the schema of the user SYS. table
Specify the name of the master table for which the materialized view log is to be created.
Restriction on Master Tables of Materialized View Logs
You cannot create a materialized view log for a temporary table or for a view.
See Also:
\"Creating a Materialized View Log: Examples\"
physical_attributes_clause
Use the physical_attributes_clause to establish values for physical and storage characteristics for the materialized view log.
See Also:
• •
physical_attributes_clausefor a complete description of the parameters of the physical_attributes_clause
storage_clause for a complete description of the storage_clause, including default values
TABLESPACE Clause
Specify the tablespace in which the materialized view log is to be created. If you omit this clause, Oracle creates the materialized view log in the default tablespace of the schema of the materialized view log. logging_clause
Specify either LOGGING or NOLOGGING to establish the logging characteristics for the materialized view log. The default is the logging characteristic of the tablespace in which the materialized view log resides.
See Also:
logging_clause for a full description of this clause
CACHE | NOCACHE
For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this log 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 attribute is useful for small lookup tables.
NOCACHE specifies that the blocks are placed at the least recently used
end of the LRU list. The default is NOCACHE.
Note:
NOCACHE has no effect on materialized view logs for which
you specify KEEP in the storage_clause.
See Also:
CREATE TABLE for information about specifying CACHE or
NOCACHE
parallel_clause
The parallel_clause lets you indicate whether parallel operations will be supported for the materialized view log.
Note:
The syntax of the parallel_clausesupersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior than that documented.
NOPARALLEL
Specify NOPARALLEL for serial execution. This is the default.
PARALLEL
Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
PARALLEL integer
Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
See Also:
\"Notes on the parallel_clause\" for CREATE TABLE
table_partitioning_clauses
Use the table_partitioning_clauses to indicate that the materialized view log is partitioned on specified ranges of values or on a hash function. Partitioning of materialized view logs is the same as partitioning of tables.
See Also:
table_partitioning_clauses of CREATE TABLE
WITH Clause
Use the WITH clause to indicate whether the materialized view log should record the primary key, the rowid, object ID, or a combination of these row identifiers when rows in the master are changed. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records.
This clause also specifies whether the materialized view log records additional columns that might be referenced as filter columns
(non-primary-key columns referenced by subquery materialized views) or join columns (non-primary-key columns that define a join in the subquery WHERE clause).
If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then Oracle stores primary key values by default. However, Oracle does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time. A primary key log, created either explicitly or by default, performs additional checking on the primary key constraint.
OBJECT ID
Specify OBJECT ID to indicate that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log.
Restriction on OBJECT ID
You can specify OBJECT ID only when creating a log on an object table, and you cannot specify it for storage tables.
PRIMARY KEY
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log.
ROWID
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.
SEQUENCE
Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.
See Also:
Oracle9i Data Warehousing Guidefor more information on the
use of sequence numbers in materialized view logs and for examples that use this clause
column
Specify the columns whose values you want to be recorded in the
materialized view log for all rows that are changed. Typically these columns are filter columns (non-primary-key columns referenced by materialized views) and join columns (non-primary-key columns that define a join in the WHERE clause of the subquery).
Restrictions on the WITH Clause
• •
You can specify only one PRIMARY KEY, one ROWID, one OBJECT ID, and one column list for each materialized view log.
Primary key columns are implicitly recorded in the materialized view log. Therefore, you cannot specify either of the following combinations if column contains one of the primary
• • •
key columns:
WITH ... PRIMARY KEY ... (column) WITH ... (column) ... PRIMARY KEY WITH (column) See Also:
• • •
CREATE MATERIALIZED VIEWfor information on
explicit and implicit inclusion of materialized view log values
Oracle9i Advanced Replicationfor more information about
filter columns and join columns
\"Specifying Filter Columns for Materialized View Logs: Example\" and \"Specifying Join Columns for Materialized View Logs: Example\"
NEW VALUES Clause
The NEW VALUES clause lets you indicate whether Oracle saves both old and new values in the materialized view log.
See Also:
\"Including New Values in Materialized View Logs: Example\"
INCLUDING
Specify INCLUDING to save both new and old values in the log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify INCLUDING.
EXCLUDING
Specify EXCLUDING to disable the recording of new values in the log. This is the default. You can use this clause to avoid the overhead of recording new values. However, do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on this table.
Examples
Creating a Materialized View Log: Examples
The following statement creates a materialized view log on the oe.customers table that specifies physical and storage characteristics:
CREATE MATERIALIZED VIEW LOG ON customers
PCTFREE 5
TABLESPACE example
STORAGE (INITIAL 10K NEXT 10K);
This materialized view log supports fast refresh for primary key
materialized views only. The following statement creates another version of the materialized view log with the ROWID clause, which enables fast refresh for more types of materialized views:
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;
This materialized view log makes fast refresh possible for rowid
materialized views and for materialized join views. To provide for fast
SEQUENCE refresh of materialized aggregate views, you must also specify the
and INCLUDING NEW VALUES clauses, as shown in the next statement.
Specifying Filter Columns for Materialized View Logs: Example
The following statement creates a materialized view log on the sh.sales table, and is used in \"Creating Materialized Aggregate Views: Example\". It specifies as filter columns all of the columns of the table referenced in that materialized view.
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id) INCLUDNG NEW VALUES;
Specifying Join Columns for Materialized View Logs: Example
The following statement creates a materialized view log on the order_items table of the sample oe schema. The log records primary keys and product_id,
\"Creating a Fast Refreshable Materialized which is used as a join column in
View: Example\".
CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id); Including New Values in Materialized View Logs: Example
The following example creates a materialized view log on the oe.product_information table that specifies INCLUDING NEW VALUES:
CREATE MATERIALIZED VIEW LOG ON product_information WITH ROWID, (list_price, min_price, category_id) INCLUDING NEW VALUES;
You could create the following materialized aggregate view to use the product_information log:
CREATE MATERIALIZED VIEW products_mv REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id FROM product_information GROUP BY category_id;
This materialized view is eligible for fast refresh because the log it uses includes both old and new values.
因篇幅问题不能全部显示，请点此查看更多更全内容