CODEWITHSUNDEEP
sqloracle-databaseview
user3248637
user3248637

Reputation: 73

Oracle - Materialized Views on Subquery

From documents i understood that we cant create materialized views on query that have subquery in it. So i created a normal oracle view and using that normal view i created a materialized view

CREATE MATERIALIZED VIEW  TEST_MV BUILD IMMEDIATE
REFRESH FORCE  AS 
SELECT * FROM "MAIN_LOOKUP"

This "MAIN_LOOKUP" view have many sub queries in it

Help me understand below point

  1. The "Main Look up" View have the tables that will be used real time and high chances of data change .Since the option is specified as FORCE will it automatically refreshed?
  2. i couldn't include the option ENABLE QUERY REWRITE its saying "expression not supported for query rewrite".if am not specifying the option will it be any problem to the MV created?
  3. Should we have the materialized view log created for all the underlying tables?

Thanks

Upvotes: 0

Views: 1138

Answers (1)

pmdba
pmdba

Reputation: 7033

Per the documentation:

Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh if fast refresh is not possible.

This specifies which type of refresh will happen: fast (incremental) refresh if possible, otherwise full refresh. It will not refresh anything automatically.

The ON COMMIT option will update the MV automatically, but will significantly impact application performance if the source tables are highly active:

Specify ON COMMIT to indicate that a refresh is to occur whenever the database 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 the database performs the refresh operation as part of the commit process.

In other words, this would serialize all transactions on all of your source tables, forcing an MV refresh for each commit on each table.

Query Rewrite is not enabled by default, so not including it won't affect anything. If you want to have it enabled, then it must meet the following criteria:

Enabling of query rewrite is subject to the following restrictions:

You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.

You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).

Create MV Logs on the source tables if your MV meets the criteria for FAST REFRESH. You will still have to refresh the MV manually, or using a scheduled job.

FAST refresh is subject to the following restrictions:

  • When you specify FAST refresh at create time, Oracle Database verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to FAST in an ALTER MATERIALIZED VIEW statement, Oracle Database does not perform this verification. If the materialized view is not eligible for fast refresh, then Oracle Database returns an error when you attempt to refresh this view.

  • Materialized views are not eligible for fast refresh if the defining query contains an analytic function or the XMLTable function.

  • Materialized views are not eligible for fast refresh if the defining query references a table on which an XMLIndex index is defined.

  • You cannot fast refresh a materialized view if any of its columns is encrypted.

There are several additional restrictions from the documentation on the use of fast refresh materialized views, many of which are conditional. Do your homework and read up on the requirements and exceptions to determine if they will work in your specific situation. For practical examples, you can also check a variety of online tutorials and examples, like this one.

Upvotes: 1

Related Questions