|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: CREATE LIBRARY to CREATE SPFILE, 3 of 16
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 replication term) or detail tables (a data warehouse term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.
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.
Oracle9i Replication for 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.
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:
VIEWsystem privilege and either the
SELECTobject privilege on each of the tables or through the
To create a materialized view in another user's schema:
TABLEsystem 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
SELECTobject privilege on each of the tables or through the
To create a refresh-on-commit materialized view (
REFRESH clause), in addition to the preceding privileges, you must have the
REFRESH object privilege on any master tables that you do not own or you must have the
REFRESH system privilege.
To create the materialized view with query rewrite enabled, in addition to the preceding privileges:
REWRITEsystem privilege or the
REWRITEobject privilege on each table outside your schema.
REWRITEprivilege or the
REWRITEobject privilege on each table outside the schema.
TABLE), you must have the
OPTIONon 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
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.
mapping_table_clauses: not supported with materialized views,
LOB_storage_clause::=, XMLType_column_properties: not supported for materialized views)
Specify the schema to contain the materialized view. If you omit
schema, Oracle creates the materialized view in your schema.
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.
type_name clause lets you explicitly create an object materialized view of type
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.
"SCOPE REF Constraints" for more information
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:
The keywords and parameters of the
index_org_table_clause have the same semantics as described in
TABLE, with the restrictions that follow.
Restrictions on index-organized materialized views:
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
segment_attributes_clause to establish values for the
MAXTRANS parameters (or, when used in the
INDEX clause, for the
MAXTRANS parameters only), the storage characteristics for the materialized view, to assign a tablespace, and to specify whether logging is to occur.
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.
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.
column_properties clause lets you specify the storage characteristics of a LOB, nested table, varray, or
Restriction on column_properties: The
object_type_col_properties are not relevant for a materialized view.
CREATE TABLE for detailed information about specifying the parameters of this clause
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.
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.
CREATE TABLE for information about specifying
CLUSTER clause to create the materialized view as part of the specified cluster. A clustered materialized view uses the cluster's space allocation. Therefore, do not specify the
physical_attributes_clause or the
TABLESPACE clause with 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.
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.
The syntax of the
NOPARALLEL for serial execution. This is the default.
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.
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
"Notes on the parallel_clause" for
build_clause lets you specify when to populate the materialized view.
IMMEDIATE to indicate that the materialized view is populated immediately. This is the default.
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.
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.
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.
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
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:
subquerymust correspond to a column in
table_name, and corresponding columns must have matching datatypes.
NULLconstraint for any column that is unmanaged (that is, not referenced in
subquery) unless you also specify a default value for that column.
INDEX clause lets you establish the value of
STORAGE parameters for the default index Oracle uses to maintain the materialized view's data. If
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
PCTFREE parameters in this clause.
INDEX to suppress the creation of the default index. You can create an alternative index explicitly by using the
INDEX statement. You should create such an index if you specify
INDEX and you are creating the materialized view with the incremental refresh method (
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.
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
If you specify
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.
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 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 (
FORCE is the default.
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 ON COMMIT: This clause is not supported for materialized views containing object types.
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
DEMAND is the default.
If you specify
DEMAND, you cannot also specify
Specify a date expression for the first automatic refresh time.
Specify a date expression for calculating the interval between automatic refreshes.
NEXT values must evaluate to a time in the future. If you omit the
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
WITH value but omit the
NEXT value, Oracle refreshes the materialized view only once. If you omit both the
NEXT values, or if you omit the
create_mv_refresh entirely, Oracle does not automatically refresh the materialized view.
KEY to create a primary key materialized view. This is the default, and should be used in all cases except those described for
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 WITH PRIMARY KEY: You cannot specify this clause for an object materialized view. Oracle implicitly refreshes object materialized
Oracle9i Replication for detailed information about primary key materialized views and "Creating Primary Key Materialized Views: Example"
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:
Rowid materialized views are not eligible for fast refreshed after a master table reorganization until a complete refresh has been performed.
Restriction on WITH ROWID: You cannot specify this clause for an object materialized view. Oracle implicitly refreshes object materialized
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 specifies that Oracle will choose automatically which rollback segment to use. If you specify
DEFAULT, you cannot specify
DEFAULT is most useful when modifying (rather than creating) a materialized view.
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.
Oracle9i Replication for information on specifying the local materialized view rollback segment using the
If you do not specify
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.
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
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.
REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.
ENABLE to enable the materialized view for query rewrite.
Restrictions on the ENABLE clause:
USER, sequence values (such as the
NEXTVALpseudocolumns), or the
SAMPLEclause (which may sample different rows as the contents of the materialized view change).
DISABLE to indicate that the materialized view is not eligible for use by query rewrite. However, a disabled materialized view can be refreshed.
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:
FROMclause of the defining subquery of the materialized view with the schema containing it.
"AS subquery" clause of
Restrictions on the Defining Subquery of a Materialized View:
SYS, but you cannot enable
REWRITEon such a materialized view.
BYclause cannot select from an index-organized table.
FASTrefresh, nor can you specify the
REWRITEclause in this statement.
FROMclause 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:
SYSDATE, remote tables, sequences, or PL/SQL functions that write or read database or package state.
If you want the materialized view to be eligible for fast refresh using a materialized view log, some additional restrictions may apply.
The following examples require the materialized logs that are created in the "Examples" section of CREATE MATERIALIZED VIEW.
The following statement creates and populates a materialized aggregate view on the sample
sh.sales table and specifies the default refresh method, 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;
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 demo 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;
The following statement creates a materialized aggregate view for the preexisting summary 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 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 following statement creates a subquery materialized view based on the
customers tables in the
sales schema at a remote database:
CREATE MATERIALIZED VIEW sales.orders FOR UPDATE AS SELECT * FROM email@example.com o WHERE EXISTS (SELECT * FROM firstname.lastname@example.org c WHERE o.c_id = c.c_id);
The following statement creates the primary-key materialized view
catalog on the sample table
CREATE MATERIALIZED VIEW catalog REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096 WITH PRIMARY KEY AS SELECT * FROM product_information;
The following statement creates a rowid materialized view on the sample table
The following statement creates the primary key materialized view
emp_data and populates it with data from the sample table
CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW emp_data PCTFREE 5 PCTUSED 60 TABLESPACE demo STORAGE (INITIAL 50K NEXT 50K) REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM employees;
The statement does not include a
WITH parameter, so Oracle determines the first automatic refresh time by evaluating the
NEXT value using the current
SYSDATE. A materialized view log was created for the employee table, 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.
The following statement creates the complex materialized view
all_emps that queries the employee tables in Dallas and Baltimore:
CREATE MATERIALIZED VIEW all_emps PCTFREE 5 PCTUSED 60 TABLESPACE users 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 fran.emp@dallas UNION SELECT * FROM marco.emp@balt;
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
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:
INDEXclause) establishes the sizes of the first and second extents of the index as 25 kilobytes each.
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.
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;
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
inventories tables using
WHERE conditions. The materialized view logs for
product_information were created in the "Examples" section of
LOG. This example requires a materialized view log on
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
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".
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