| Oracle&
reg; Database Data Warehousing Guide 10g Release 1 (10.1) Part Number B10736-01 |
|
|
View PDF |
| Oracle&
reg; Database Data Warehousing Guide 10g Release 1 (10.1) Part Number B10736-01 |
|
|
View PDF |
This chapter discusses advanced topics in using materialized views:
Because of the l arge volume of data held in a data warehouse, partitioning is an extremely useful option when designing a database. Partitioning the fact tables improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficie ntly rebuilt. Partitioning the fact tables also improves the opportunity of fast refreshing the materialized view because this may en able Partition Change Tracking (PCT) refresh on the materialized view. Partitioning a materialized view also has benefits for refresh , because the refresh procedure can then use parallel DML in more scenarios and PCT-based refresh can use truncate partition to effic iently maintain the materialized view. See Chapter 5, " Parallelism and Partitioning in Data Warehouse s" for further details about partitioning.
It is possible and advantageous to track freshness to a finer grain than the entire materialized view. The ability to identify which rows in a materialized view are affected by a certain detail table p artition, is known as Partition Change Tracking (PCT). When one or more of the detail tables are partitioned, it may be possible to i dentify the specific rows in the materialized view that correspond to a modified detail partition(s); those rows become stale when a partition is modified while all other rows remain fresh.
You can use PCT to identify which materialized view rows correspond t o a particular partition. PCT is also used to support fast refresh after partition maintenance operations on detail tables. For insta nce, if a detail table partition is truncated or dropped, the affected rows in the materialized view are identified and deleted.
Identifying which materialized view rows are fresh or stale, rather than considering the entire materialized view as stale, allows
query rewrite to use those rows that are fresh while in QUERY_REWRITE_INTEGRITY=ENFORCED or TRUSTED<
/code> modes. Oracle does not rewrite against partial stale materialized view if partition change tracking on the changed table is en
abled by the presence of join dependent expression in the materialized view. See "Join Dependent Expression".
To support PCT, a materialized view must satisfy the following requirements:
At least one of the detail tables referenced by the materialized view must be partitioned.
Partitioned tables must use eith er range, list or composite partitioning.
The top level partition key must consist of only a single colu mn.
The materialized view must contain either the partition key column or a partition marker or ROWID or
join dependent expression of the detail table. See
PL/SQL Packages and Types Reference for details regarding the DBMS_MVIEW.PMARKER function.
If you use a GROUP BY clause, the partition key column or the partition marker or ROWID<
/code> or join dependent expression must be present in the GROUP BY clause.
If
you use an analytic window function or the MODEL clause, the partition key column or the partition marker or ROWI
D or join dependent expression must be present in their respective PARTITION BY subclauses.
Data modifications can only occur on the partitioned table. If PCT refresh is being done for a table which has j oin dependent expression in the materialized view, then data modifications should not have occurred in any of the join dependent tabl es.
The COMPATIBILITY initialization parameter must be a minimum of 9.0.0.0.0.
PCT is not supported for a materialized view that refers to views, remote tables, or outer joins.
PCT-based refresh is not supported for UNION ALL materialized views.
Partition change tracking requires sufficient information in the materialized view to be able to correlate a detail row in the s
ource partitioned detail table to the corresponding materialized view row. This can be accomplished by including the detail table par
tition key columns in the SELECT list and, if GROUP BY is used, in the GROUP
Consider an example of a materialized view storing daily customer sales. The following example uses the sales, products, and times to create the
materialized view. sales table is partitioned by time_id column and products is partitioned b
y the prod_id column. times is not a partitioned table.
Example 9-1 Partition Ke y
The detail tables must have materialized view logs for FAST REFRESH. The foll
owing is an example:
CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID
(prod_id, time_id, quantity_so
ld, amount_sold) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH ROWID
(prod_id, prod_name, prod_desc) INCLUD
ING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TIMES WITH ROWID
(time_id, calendar_month_name, calendar_year) INCLUDING NEW VALUE
S;
CREATE MATERIALIZED VIEW cust_dly_sales_mv
BUILD DEFERRED REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT s.time_id, p.prod
_id, p.prod_name, COUNT(*),
SUM(s.quantity_sold), SUM(s.amount_sold),
COUNT(s.quantity_sold), COUNT(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
GROUP BY s.time_id, p.prod_id, p.prod_name;
For cust_dly_sales_mv, PCT is enabled on both the sales table and products table because their respective pa
rtitioning key columns time_id and prod_id are in the materialized view.
An expression consisting of colum ns from tables directly or indirectly joined through equijoins to the partitioned detail table on the partitioning key and which is e ither a dimensional attribute or a dimension hierarchical parent of the joining key is called a join dependent expression. The set of tables in the path to detail table are called join dependent tables.
SELECT s.time_id, t.calendar_mont h_name FROM sales s, times t WHERE s.time_id = t.time_id;
In this query, times table is a join dependent ta
ble since it is joined to sales table on the partitioning key column time_id. Moreover, calendar_mont
h_name is a dimension hierarchical attribute of times.time_id, because calendar_month_name is an att
ribute of times.mon_id and times.mon_id is a dimension hierarchical parent of times.time_id. H
ence, the expression calendar_month_name from times tables is a join dependent expression. Let's look at an
other example:
SELECT s.time_id, y.calendar_year_name FROM sales s, times_d d, times_m m, times_y y WHE RE s.time_id = d.time_id AND d.day_id = m.day_id AND m.mon_id = y.mon_id;
Here, times table is denormalized
into times_d, times_m and times_y tables. The expression calendar_year_name from
times_y table is a join dependent expression and the tables times_d, times_m and times_y
are join dependent tables. This is because times_y table is joined indirectly through times_m and <
code>times_d tables to sales table on its partitioning key column time_id.
This lets users create material ized views containing aggregates on some level higher than the partitioning key of the detail table. Consider the following example o f materialized view storing monthly customer sales.
Example 9-2 Join Dependent Expression
Assuming the presence of materialized view logs defined earlier, the materialized view can be created using the following DDL:
C
REATE MATERIALIZED VIEW cust_mth_sales_mv
BUILD DEFERRED REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_name
, p.prod_id, p.prod_name, COUNT(*),
SUM(s.quantity_sold), SUM(s.amount_sold),
COUNT(s.quantity_sold), COUNT(s.amount_sold)
FR
OM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
GROUP BY t.calendar_month_name, p.prod_id, p.p
rod_name;
Here, you can correlate a detail table row to its corresponding materialized view row using the join dependent
table times and the relationship that times.calendar_month_name is a dimensional attribute determined by sales table. In addition to this, PCT is enabled on
products table because of presence of its partitioning key column prod_id in the materialized view.
The DBMS_MVIEW.PMARKER function is designed to signifi
cantly reduce the cardinality of the materialized view (see Example 9-3 for an example). The function returns
a partition identifier that uniquely identifies the partition for a specified row within a specified partition table. Therefore, the
DBMS_MVIEW.PMARKER function is used instead of the partition key column in the SELECT and GROUP BY clauses.
Unlike the general case of a PL/SQL function in a materialized view, use of the DBMS_MVIEW.P
MARKER does not prevent rewrite with that materialized view even when the rewrite mode is QUERY_REWRITE_INTEGRITY=ENFORC
ED.
As an example of using the PMARKER function, consider calculating a typical number, such as revenue ge
nerated by a product category during a given year. If there were 1000 different products sold each month, it would result in 12,000 r
ows in the materialized view.
Example 9-3 Partition Marker
Consider an example
of a materialized view storing the yearly sales revenue for each product category. With approximately hundreds of different products
in each product category, including the partitioning key column prod_id of products table in the materiali
zed view would substantially increase the cardinality. Instead, this materialized view uses the DBMS_MVIEW.PMARKER funct
ion, which increases the cardinality of materialized view by a factor of the number of partitions in the products table.
CREATE MATERIALIZED VIEW prod_yr_sales_mv
BUILD DEFERRED
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE A
S
SELECT DBMS_MVIEW.PMARKER(p.rowid), p.prod_category, t.calendar_year, COUNT(*),
SUM(s.amount_sold), SUM(s.quantity_sold),
COUNT(s.amount_sold), COUNT(s.quantity_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.
prod_id
GROUP BY DBMS_MVIEW.PMARKER (p.rowid), p.prod_category, t.calendar_year;
prod_yr_sales_mv includes
the DBMS_MVIEW.PMARKER function on the products table in its SELECT list. This enables partiti
on change tracking on products table with significantly less cardinality impact than grouping by the partition key colum
n prod_id. In this example, the desired level of aggregation for the prod_yr_sales_mv is to group by
products.prod_category. Using the DBMS_MVIEW.PMARKER function, the materialized view cardinality is increased onl
y by a factor of the number of partitions in the products table. This would generally be significantly less than the car
dinality impact of including the partition key columns.
Please note that partition change tracking is enabled on sales table because of presence of join dependent expression calendar_year in the SELECT list.
A subsequent INSERT statement adds a new row to
the sales_part3 partition of table sales. At this point, because cust_dly_sales_mv has PCT av
ailable on table sales using a partition key, Oracle can identify the stale rows in the materialized view cust_dly
_sales_mv corresponding to sales_part3 partition (The other rows are unchanged in their freshness state). Query r
ewrite cannot identify the fresh portion of materialized views cust_mth_sales_mv and prod_yr_sales_mv becau
se PCT is available on table sales using join dependent expressions. Query rewrite can determine the fresh portion of a materialized
view on changes to a detail table only if PCT is available on the detail table using a partition key or partition marker.
Parti
tioning a materialized view involves defining the materialized view with the standard Oracle partitioning clauses, as illustrated in
the following example. This statement creates a materialized view called part_sales_mv, which uses three partitions, can
be fast refreshed, and is eligible for query rewrite.
CREATE MATERIALIZED VIEW part_sales_mv
PARALLEL
PARTITION BY RANGE (time_id)
(PARTITION month1
VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 9
9
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE sf1,
PARTITION month2
VALUES LESS THAN (TO_DATE('31-12-1
999', 'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE sf2,
PARTITION m
onth3
VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCT
INCREASE 0)
TABLESPACE sf3)
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT s.cust_id, s.time_id,
SUM(s.amount_sol
d) AS sum_dol_sales, SUM(s.quantity_sold) AS sum_unit_sales
FROM sales s GROUP BY s.time_id, s.cust_id;
Alternative ly, a materialized view can be registered to a partitioned prebuilt table as illustrated in the following example:
CREATE TABLE part_sales_tab(time_id, cust_id, sum_dollar_sales, sum_unit_sale)
PARALLEL PARTITION BY RANGE (time_id)
(PAR
TITION month1
VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT
16k PCTINCREASE 0)
TABLESPACE sf1,
PARTITION month2
VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY'))
PCTFRE
E 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE sf2,
PARTITION month3
VALUES LESS THAN (TO_D
ATE('31-12-2000', 'DD-MM-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE sf3)
AS
SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales,
SUM(s.quantity_sold) AS sum_unit_sales
FROM sales s GROUP
BY s.time_id, s.cust_id;
CREATE MATERIALIZED VIEW part_sales_tab_mv
ON PREBUILT TABLE
ENABLE QUERY REWRITE AS
SELECT s.time_id, s
.cust_id, SUM(s.amount_sold) AS sum_dollar_sales,
SUM(s.quantity_sold) AS sum_unit_sales
FROM sales s GROUP BY s.time_id, s.cu
st_id;
In this example, the table part_sales_tab has been partitioned over three months and then the materia
lized view was registered to use the prebuilt table. This materialized view is eligible for query rewrite because the ENABLE QUERY REWRITE clause has been included.
When a materialized view is partitioned on the partitioning key column or join dependent expressions of the detail t
able, it is more efficient to use a TRUNCATE PARTITION statement to remove one or more partitions of the ma
terialized view during refresh and then repopulate the partition with new data. Oracle Database uses this variant of fast refresh (ca
lled PCT refresh) with partition truncation if the following conditions are satisfied in addition to other conditions described in "Partition Change Tracking".
The materialized view is partitioned on the partition ing key column or join dependent expressions of the detail table.
If PCT is enabled using either the par titioning key column or join expressions, both the materialized view should be range or list partitioned.
When a data warehouse or data mart contains a time dimension, it is often desirable to archive the oldest information and then reuse the storage for new information. This is called the rolling window scenario. If the fact tables or materialized views include a time dimension and are horizontally partitioned by the time attribute, then management of rolling materialized views can be reduced to a f ew fast partition maintenance operations provided the unit of data that is rolled out equals, or is at least aligned with, the range partitions.
If you plan to have rolling materialized views in your data warehouse, you should determine how frequently you pla n to perform partition maintenance operations, and you should plan to partition fact tables and materialized views to reduce the amou nt of system administration overhead required when old data is aged out. An additional consideration is that you might want to use da ta compression on your infrequently updated partitions.
You are not restricted to using range partitions. For example, a compo site partition using both a time value and a key value could result in a good partition solution for your data.
See Chapter 15, " Maintaining the Data Warehouse" for further details regarding CONSIDER FRES
H and for details regarding compression.
This section discusses certain OLAP concepts and how relational databases can handle OLAP queries. Next, it recommends an appro ach for using materialized views to meet OLAP performance needs. Finally, it discusses using materialized views with set operators, a common scenario for OLAP environments.
While data warehouse environments typically view data in the form of a star schema, OLAP environments view data in the form of a hierarchical cube. A hierarchical cube includes the data aggregated along the rollup hierarchy of each of its dimensions and these ag gregations are combined across dimensions. It includes the typical set of aggregations needed for business intelligence queries.
Example 9-4 Hi erarchical Cube
Consider a sales data set with two dimensions, each of which has a 4-level hierarchy:
Product, which contains (all products), di vision, brand, and item.
This means there are 16 aggregate groups in the hierarchical cube. This is because the four levels of time are multiplied by four levels of product to produce the cube. Table 9-1 shows the four levels of each dimension.
| ROLLUP By Time | ROLLUP By Product |
|---|---|
| year, quarter, month | division, brand, item |
| year, quarter | division, brand |
| year | division |
| all times | a ll products |
Note that as you increase the number of dimensions a nd levels, the number of groups to calculate increases dramatically. This example involves 16 groups, but if you were to add just two more dimensions with the same number of levels, you would have 4 x 4 x 4 x 4 = 256 different groups. Also, consider that a similar i ncrease in groups occurs if you have multiple hierarchies in your dimensions. For example, the time dimension might have an additiona l hierarchy of fiscal month rolling up to fiscal quarter and then fiscal year. Handling the explosion of groups has historically been the major challenge in data storage for OLAP systems.
Typical OLAP queries slice and d ice different parts of the cube comparing aggregations from one level to aggregation from another level. For instance, a query might find sales of the grocery division for the month of January, 2002 and compare them with total sales of the grocery divis ion for all of 2001.
Materialized views with multiple a ggregate groups will give their best performance for refresh and query rewrite when partitioned appropriately.
PCT refresh in
a rolling window scenario requires partitioning at the top level on some level from the time dimension. And, partition pruning for qu
eries rewritten against this materialized view requires partitioning on GROUPING_ID column. Hence, the most effective pa
rtitioning scheme for these materialized views is to use composite partitioning (range-list on (time, GROUPING_ID<
/code>) columns). By partitioning the materialized views this way, you enable:
PCT refresh, thereby impr oving refresh performance.
Partition pruning: only relevant aggregate groups will be accessed, thereby g reatly reducing the query processing cost.
If you do not want to use PCT refresh, you can just partition by list on
GROUPING_ID column.
You should consider dat
a compression when using highly redundant data, such as tables with many foreign keys. In particular, materialized views created with
the ROLLUP clause are likely candidates. See Oracl
e Database SQL Reference for data compression syntax and restrictions and "Storage And Table
Compression" for details regarding compression.
Oracle provides support for materialized views whose defining query involves set operators. Materialized views with set operators can now be created enable
d for query rewrite. You can refresh the materialized view using either ON COMMIT or ON
DEMAND refresh.
Fast refresh is supported if the defining query has the UNION ALL operator at
the top level and each query block in the UNION ALL, meets the requirements of a materialized view with ag
gregates or materialized view with joins only. Further, the materialized view must include a constant column (known as a UNION<
/code> ALL marker) that has a distinct value in each query block, which, in the following example, is columns 1 ma
rker and 2 marker.
See "Restrictions on Fast Refresh on Materialized Views
with UNION ALL" for detailed restrictions on fast refresh for materialized views with UNION ALL.
The following examples illustrate creation of fast refreshable materialized views in
volving UNION ALL.
Example 9-5 Materialized View Using UNION ALL with Two Join Views
To c
reate a UNION ALL materialized view with two join views, the materialized view logs must have the rowid col
umn and, in the following example, the UNION ALL marker is the columns, 1 marker and 2 m
arker.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON custome rs WITH ROWID; CREATE MATERIALIZED VIEW unionall_sales_cust_joins_mv REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 1 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Smith') UNION ALL (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 2 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Brown');
Example 9-6 Materialized View Using UNION ALL with Joins and Agg regates
The following example shows a UNION ALL of a materialized view with joi
ns and a materialized view with aggregates. A couple of things can be noted in this example. Nulls or constants can be used to ensure
that the data types of the corresponding SELECT list columns match. Also the UNION ALL marker
column can be a string literal, which is 'Year' umarker, 'Quarter' umarker, or 'Daily' umarker in the following example:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE
(amount_sold, ti
me_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID, SEQUENCE
(time_id, fiscal_year, fiscal_quarter_num
ber, day_number_in_week)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW unionall_sales_mix_mv
REFRESH FAST ON DEMAND AS
(SELECT 'Ye
ar' umarker, NULL, NULL, t.fiscal_year,
SUM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*)
FROM sales s, times t
WHERE
s.time_id = t.time_id
GROUP BY t.fiscal_year)
UNION ALL
(SELECT 'Quarter' umarker, NULL, NULL, t.fiscal_quarter_number,
S
UM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*)
FROM sales s, times t
WHERE s.time_id = t.time_id and t.fiscal_year = 2001
GRO
UP BY t.fiscal_quarter_number)
UNION ALL
(SELECT 'Daily' umarker, s.rowid rid, t.rowid rid2, t.day_number_in_week,
s.amount_s
old amt, 1, 1
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.time_id between '01-Jan-01' AND '01-Dec-31');
Models, which provide array-based computations in SQL, can be used in materializ
ed views. Because the MODEL clause calculations can be expensive, you may want to use two separate materialized views: o
ne for the model calculations and one for the SELECT ... GROUP BY query. For example, instead
of using one, long materialized view, you could create the following materialized views:
CREATE MATERIA
LIZED VIEW my_groupby_mv
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT country_name country, prod_name prod, calendar_year year,
SUM(
amount_sold) sale, COUNT(amount_sold) cnt, COUNT(*) cntstr
FROM sales, times, customers, countries, products
WHERE sales.time_id = t
imes.time_id AND
sales.prod_id = products.prod_id AND
sales.cust_id = customers.cust_id AND
customers.country_id =
countries.country_id
GROUP BY country_name, prod_name, calendar_year;
CREATE MATERIALIZED VIEW my_model_mv
ENABLE QUERY REWRITE AS
S
ELECT country, prod, year, sale, cnt
FROM my_groupby_mv
MODEL PARTITION BY(country) DIMENSION BY(prod, year)
MEASURES(sale s) IGNO
RE NAV
(s['Shorts', 2000] = 0.2 * AVG(s)[CURRENTV(), year BETWEEN 1996 AND 1999],
s['Kids Pajama', 2000] = 0.5 * AVG(s)[CURRENTV(),
year BETWEEN 1995 AND 1999],
s['Boys Pajama', 2000] = 0.6 * AVG(s)[CURRENTV(), year BETWEEN 1994 AND 1999],
...
<hundreds of other
update rules>);
By using two materialized views, you can incrementally maintain the materialized view my_groupb
y_mv. The materialized view my_model_mv is on a much smaller data set because it is built on my_groupby_mv<
/code> and can be maintained by a complete refresh.
Materialized views with models can use complete refresh or PCT refresh onl y, and are available for partial text query rewrite only.
See Chapter 22, " SQL for Modeling"< /a> for further details about model calculations.
Dependencies related to materi
alized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view dep
ends on the detail tables referenced in its definition. Any DML operation, such as a INSERT, or DELETE, ALTER MATERIALIZED VIEW COMPILE statement.
A material ized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transpare ntly revalidated. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that privilege has now been granted to the owner, you should use the follo wing statement to revalidate the materialized view:
ALTER MATERIALIZED VIEW mview_name COMPILE;
The state of a materialized view can be checked by querying the data dictionary views USER_MVIEWS or ALL_MVIE
WS. The column STALENESS will show one of the values FRESH, STALE, UNUSABLE, UNKNOWN, UNDEFINED, or NEEDS_COMPILE to indicate whether the materialized view can be used.
The state is maintained automatically. However, if the staleness of a materialized view is marked as NEEDS_COMPILE, you
could issue an ALTER MATERIALIZED VIEW ... COMPILE statement to validate the mat
erialized view and get the correct staleness state.
To create a material
ized view in your own schema, you must have the If the materialized view is on a prebuilt container, the creator, if different from the owner, must have If you continue to get a privilege error while trying to
create a materialized view and you believe that all the required privileges have been granted, then the problem is most likely due to
a privilege not being granted explicitly and trying to inherit the privilege from a role instead. The owner of the materialized view
must have explicitly been granted CREATE MATERIALIZED VIEW privilege and the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materia
lized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable que
ry rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.
SELECT GRANT privilege on the container table.SELECT access to the referenced tables if the tables are in a different schema.ON COMMIT REFRESH specified, then the owner o
f the materialized view requires an additional privilege if any of the tables in the defining query are outside the owner's schema. I
n that case, the owner requires the ON COMMIT REFRESH system privilege or the ON
COMMIT REFRESH object privilege on each table outside the owner's schema.
For all security conce rns, a materialized view serves as a view that happens to be materialized when you are directly querying the materialized view. When creating a view or materialized view, the owner needs to have the necessary permissions to access the underlying base relations of th e view or materialized view that they are creating. With these permissions, the owner can publish a view or materialized view that ot her users can access, assuming they have been granted access to the view or materialized view.
Using materialized views with V irtual Private Database (VPD) is similar. When you create a materialized view, there must not be any VPD policies in effect against t he base relations of the materialized view for the owner of the materialized view. However, the owner of the materialized view may es tablish a VPD policy on the new materialized view. Users who access the materialized view are subject to the VPD policy on the materi alized view. However, they are not additionally subject to the VPD policies of the underlying base relations of the materialized view , since security processing of the underlying base relations is performed against the owner of the materialized view.
When you access a materialized view using query rew rite, the materialized view serves as an access structure much like an index. As such, the security implications for materialized vie ws accessed in this way are much the same as for indexes: all security checks are performed against the relations specified in the re quest query. The index or materialized view is used to speed the performance of accessing the data, not provide any additional securi ty checks. Thus, the presence of the index or materialized view presents no additional security checking.
This holds true when you are accessing a materialized view using query rewrite in the presence of Virtual Private Database (VPD). The request query is su bject to any VPD policies that are present against the relations specified in the query. Query rewrite may rewrite the query to use a materialize view instead of accessing the detail relations, but only if it can guarantee to deliver exactly the same rows as if the rewrite had not occurred. Specifically, query rewrite must retain and respect any VPD policies against the relations specified in the request query. However, any VPD policies against the materialized view itself do not have effect when the materialized view is acces sed using query rewrite. This is because the data is already protected by the VPD policies against the relations in the request query .
Query rewrite does not use its full and partial text match modes with request queries that include relations with active V PD policies, but it does use general rewrite methods. This is because VPD transparently transforms the request query to affect the VP D policy. If query rewrite were to perform a text match transformation against a request query with a VPD policy, the effect would be to negate the VPD policy.
In addition, when you create or refresh a materialized view, the owner of the materialized view mus
t not have any active VPD policies in effect against the base relations of the materialized view, or an error is returned. The materi
alized view owner must either have no such VPD policies, or any such policy must return NULL. This is because VPD would
transparently modify the defining query of the materialized view such that the set of rows contained by the materialized view would n
ot match the set of rows indicated by the materialized view definition.
One way to work around this restriction yet still crea
te a materialized view containing the desired VPD-specified subset of rows is to create the materialized view in a user account that
has no active VPD policies against the detail relations of the materialized view. In addition, you can include a predicate in the
Six modific ations can be made to a materialized view. You can:
Change its refresh option (FAST/FORCE/COMPLETE
/NEVER).
Change its refresh mode (ON COMMIT/ON DEMAND).
Recompile it.
Enable or disable its use for query rewrite.
Consider it fresh.
Partition maintenance operations.
All other changes are a chieved by dropping and then re-creating the materialized view.
The COMPILE clause of the ALTER VIEW statement can be used when the materialized view has been invalidated. This compile process i
s quick, and allows the materialized view to be used by query rewrite again.
|
See Also: Oracle Database SQL Reference for further information about theALTER MATERIALIZED VIEW<
/code> statement and "Invalidating Materialized Views" |
|
 Copyright © 2001, 2003 Oracle Corporation All Rights Reserv ed. |
|
