Hi Experts,
According to the docs, if the MV contains the following, MV is considered complex. I am wondering that is there any data dictionary view that shows the MV type? Because, is very easy to candidate a complex type mv. Because many queries contains the following properties.
http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm#i52501
http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm
Specifically, a materialized view is considered complex when the defining query of the materialized view contains:
- A
CONNECT BY clause
- An
INTERSECT, MINUS, or UNION ALL set operation
- he
DISTINCT or UNIQUE keyword
- In some cases, an aggregate function, although it is possible to have an aggregate function in the defining query and still have a simple materialized view
- In some cases, joins other than those in a subquery, although it is possible to have joins in the defining query and still have a simple materialized view
- In some cases, a
UNION operation
After I kept going reading I learned that when a MV is created, the refresh mode is specified according to the MV's type. That means, using ON DEMAND refresh mode is very common. The docs mentions that there isa also refresh option. My question is, is it possible to specify
ON DEMAND and FAST together?
Also what are the differences between COMPLETE and FAST. Does complete truncate all the table and populate again?
ON COMMIT
| Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMITprivilege is necessary to use this mode. |
ON DEMAND
| Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH,REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT). |
COMPLETE
| Refreshes by recalculating the materialized view's defining query. |
FAST
| Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation. |
FORCE
| Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh. |
NEVER
| Indicates that the materialized view will not be refreshed with refresh mechanisms. |
select * from v$version;
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Thanks