Which three options are true about MVIEWs?
A.
The defining query of an MVIEWs may be based on a populated table.
B.
Queries that are rewritten to an MVIEW will never obtain results from the result cache.
C.
All MVIEWS may be configured to support “refresh on demand”.
D.
The defining query of an MVIEW may be based on non_partitioned table.
E.
All MVIEWs may be configured to support “refresh on commit”
Explanation:
A: The defining query 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.
B: You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or
NOCACHE, CLUSTER, or ON PREBUILT TABLE.
C: 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.
Incorrect:
E: Materialized views can only refresh ON COMMIT in certain situations.
The materialized view cannot contain object types or Oracle-supplied types.
The base tables will never have any distributed transactions applied to them.
Note:
* Oracle uses materialized views (also known as snapshots in prior releases) to replicate data tonon-master sites in a replication environment and to cache expensive queries in a data warehouse
environment.
* A materialized view is a replica of a target master from a single point in time. The master can be
either a master table at a master site or a master materialized view at a materialized view site.
Whereas in multimaster replication tables are continuously updated by other master sites,
materialized views are updated from one or more masters through individual batch updates,
known as a refreshes, from a single master site or master materialized view site.
For me A,C,D are correct.
B is not correct because this:
15.1.1.1 Benefits of Using the Server Result Cache
The benefits of using the server result cache depend on the application. OLAP applications can benefit significantly from its use. Good candidates for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse. For example, you can use advanced query rewrite with equivalences to create materialized views that materialize queries in the result cache instead of using tables.
Source
https://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA618
Raka is right.
Also not E because:
Restrictions on Refreshing ON COMMIT
This clause is not supported for materialized views containing object types or Oracle-supplied types.
If you specify this clause, then you cannot subsequently execute a distributed transaction on any master table of this materialized view. For example, you cannot insert into the master by selecting from a remote table. The ON DEMAND clause does not impose this restriction on subsequent distributed transactions on master tables.