Database Reference
In-Depth Information
Materialized views are used in replication environments, as well as in data
warehousing (to be discussed in chapter 24). In replication environments, the
materialized views commonly created are
primary key views
,
rowed views
,
object views
,
and
sub-query views
. For data warehousing purposes, the materialized views commonly
created are materialized aggregate views, single-table materialized aggregate views, and
materialized join views.
To create a materialized view in your own schema, you need the following system
privileges:
•
Create Materialized View
•
Create Table or Create Any Table
•
Select Any Table
To create a materialized view in another user's schema, you need the following
system privileges:
•
Create Any Materialized View
•
Create Table or Create Any Table
•
Select Any Table
If you desire to create a materialized view with QUERY REWRITE enabled (see syntax
below), then in addition to the above-mentioned privileges, the following must hold:
QUERY REWRITE
•
The owner of the master tables must have the
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 pre-built container
(
see syntax below
), 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.
13.3.1 Creating a Materialized View
The Create-Materialized-View statement is used for creating materialized views. The
syntax is shown in Figure
13-11
.
