Dimensions Guideline 1

Dimensions should either be denormalized (each dimension contained in one table) or the joins between tables in a normalized or partially normalized dimension should guarantee that each child-side row joins with exactly one parent-side row.

You can enforce this condition by adding FOREIGN KEY and NOT NULL constraints on the child-side join keys and PRIMARY KEY constraints on the parent-side join keys.

Dimensions Guideline 2

If dimensions are denormalized or partially denormalized, hierarchical integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its parent key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calling the VALIDATE_DIMENSION procedure of the DBMS_DIMENSION package.

Dimensions Guideline 3

Fact and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition must be declared, and optionally enforced, by adding FOREIGN KEY and NOT NULL constraints on the fact key column(s) and PRIMARY KEY constraints on the dimension key column(s), or by using outer joins. In a data warehouse, constraints are typically enabled with the NOVALIDATE and RELY clauses to avoid constraint enforcement performance overhead.

Dimensions Guideline 4

After each load and before refreshing your materialized view, use the VALIDATE_DIMENSION procedure of the DBMS_DIMENSION package to incrementally verify dimensional integrity.

Incremental Loads Guideline

Incremental loads of your detail data should be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct-path interface. This includes INSERT ... AS SELECT with the APPEND or PARALLEL hints, where the hints cause the direct loader log to be used during the insert.

Partitions Guideline

Range/composite partition your tables by a monotonically increasing time column if possible (preferably of type DATE).

Time Dimensions Guideline

If a time dimension appears in the materialized view as a time column, partition and index the materialized view in the same manner as you have the fact tables.

If you are concerned with the time required to enable constraints and whether any constraints might be violated, then use the ENABLE NOVALIDATE with the RELY clause to turn on constraint checking without validating any of the existing constraints. The risk with this approach is that incorrect query results could occur if any constraints are broken. Therefore, as the designer, you must determine how clean the data is and whether the risk of incorrect results is too great.

You cannot create both a materialized view and a prebuilt materialized view on the same table. For example, If you have a table costs with a materialized view cost_mv based on it, you cannot then create a prebuilt materialized view on table costs. The result would make cost_mv a nested materialized view and this method of conversion is not supported.

You should keep in mind the following restrictions:

• The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).

• The query cannot contain any references to LONG or LONG RAW data types or object REFs.

• If the materialized view was registered as PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.

• The defining query cannot contain any references to objects or XMLTYPEs.

• A materialized view is a noneditioned object and cannot depend on editioned objects unless it mentions an evaluation edition in which names of editioned objects are to be resolved.

• A materialized view may only be eligible for query rewrite in a specific range of editions. The query_rewrite_clause in the CREATE or ALTER MATERIALIZED VIEW statement lets you specify the range of editions in which a materialized view is eligible for query rewrite.

You should keep in mind the following restrictions:

• A query can reference both local and remote tables. Such a query can be rewritten as long as an eligible materialized view referencing the same tables is available locally.

• Neither the detail tables nor the materialized view can be owned by SYS.

• If a column or expression is present in the GROUP BY clause of the materialized view, it must also be present in the SELECT list.

• Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

• CONNECT BY clauses are not allowed.

The defining query of the materialized view is restricted as follows:

• The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

• The materialized view must not contain references to RAW or LONG RAW data types.

• It cannot contain a SELECT list subquery.

• It cannot contain analytic functions (for example, RANK) in the SELECT clause.

• It cannot reference a table on which an XMLIndex index is defined.

• It cannot contain a MODEL clause.

• It cannot contain a HAVING clause with a subquery.

• It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

• It cannot contain a [START WITH …] CONNECT BY clause.

• It cannot contain multiple detail tables at different sites.

• ON COMMIT materialized views cannot have remote detail tables.

• Nested materialized views must have a join or aggregate.

• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

• It cannot be based on a remote view. Only complete refresh and force refresh is supported for materialized views based on remote views.

  If fast refresh is required, then create the materialized view based on the remote table on which the remote view is based.

Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

• All restrictions from "General Restrictions on Fast Refresh".

• They cannot have GROUP BY clauses or aggregates.

• Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

• Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

• You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.

Also, the refresh method you choose will not be optimally efficient if:

• The defining query uses an outer join that behaves like an inner join. If the defining query contains such a join, consider rewriting the defining query to contain an inner join.

• The SELECT list of the materialized view contains expressions on columns from multiple tables.

Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:

• All restrictions from "General Restrictions on Fast Refresh".

Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:

• All tables in the materialized view must have materialized view logs, and the materialized view logs must:

  • Contain all columns from the table referenced in the materialized view.

  • Specify with ROWID and INCLUDING NEW VALUES.

  • Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

• Only AVG, BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG, COUNT, KURTOSIS_POP, KURTOSIS_SAMP, MIN, MAX, SKEWNESS_POP, SKEWNESS_SAMP, STDDEV, SUM, and VARIANCE are supported for fast refresh.

• You must specify COUNT(*).

• Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

• For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that you specify SUM(expr).

• If you specify VARIANCE(expr) or STDDEV(expr), you must also specify COUNT(expr) and SUM(expr). Oracle recommends that you specify SUM(expr *expr).

• If you specify KURTOSIS_POP, KURTOSIS_SAMP, SKEWNESS_POP, or SKEWNESS_SAMP, you must also specify COUNT(expr) and SUM(expr). For SKEWNESS_POP, and SKEWNESS_SAMP, you must also specify VARIANCE(expr) and COUNT(*).

• The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

• The SELECT list must contain all GROUP BY columns.

• The materialized view is not based on one or more remote tables.

• If you use a CHAR data type in the filter columns of a materialized view log, the character sets of the master site and the materialized view must be the same.

• If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

  • Materialized views with MIN or MAX aggregates

  • Materialized views which have SUM(expr) but no COUNT(expr)

  • Materialized views without COUNT(*)

  Such a materialized view is called an insert-only materialized view.

• A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

  The max/min fast refresh after delete or mixed DML does not have the same behavior as the insert-only case. It deletes and recomputes the max/min values for the affected groups. You need to be aware of its performance impact.

• Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, see Oracle Database SQL Language Reference.

• If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.

• Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

• For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the following restrictions apply:

  • The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.

  • GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

Materialized views with the UNION ALL set operator support the REFRESH FAST option if the following conditions are satisfied:

• The defining query must have the UNION ALL operator at the top level.

  The UNION ALL operator cannot be embedded inside a subquery, with one exception: The UNION ALL can be in a subquery in the FROM clause provided the defining query is of the form SELECT * FROM (view or subquery with UNION ALL) as in the following example:

  CREATE VIEW view_with_unionall AS
  (SELECT c.rowid crid, c.cust_id, 2 umarker
   FROM customers c WHERE c.cust_last_name = 'Smith'
   UNION ALL
   SELECT c.rowid crid, c.cust_id, 3 umarker
   FROM customers c WHERE c.cust_last_name = 'Jones');
  
  CREATE MATERIALIZED VIEW unionall_inside_view_mv
  REFRESH FAST ON DEMAND AS
  SELECT * FROM view_with_unionall;
  

  Note that the view view_with_unionall satisfies the requirements for fast refresh.

• Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.

  The appropriate materialized view logs must be created on the tables as required for the corresponding type of fast refreshable materialized view.

  Note that the Oracle Database also allows the special case of a single table materialized view with joins only provided the ROWID column has been included in the SELECT list and in the materialized view log. This is shown in the defining query of the view view_with_unionall.

• The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block. See "UNION ALL Marker and Query Rewrite" for more information regarding UNION ALL markers.

• Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with UNION ALL. Note, however, that materialized views used in replication, which do not contain joins or aggregates, can be fast refreshed when UNION ALL or remote tables are used.

• The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.

A nested materialized view is considered to be fresh as long as its data is synchronized with the data in its detail tables, even if some of its detail tables could be stale materialized views.

You can refresh nested materialized views in two ways: DBMS_MVIEW.REFRESH with the nested flag set to TRUE and REFRESH_DEPENDENT with the nested flag set to TRUE on the base tables. If you use DBMS_MVIEW.REFRESH, the entire materialized view chain is refreshed and the coverage starting from the specified materialized view in top-down fashion. That is, the specified materialized view and all its child materialized views in the dependency hierarchy are refreshed in order. With DBMS_MVIEW.REFRESH_DEPENDENT, the entire chain is refreshed from the bottom up. That is, all the parent materialized views in the dependency hierarchy starting from the specified table are refreshed in order.

The following PL/SQL declarations that are made for you in the DBMS_MVIEW package show the order and data types of these parameters for explaining an existing materialized view and a potential materialized view with output to a table and to a VARRAY.

Explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE:

DBMS_MVIEW.EXPLAIN_MVIEW (mv           IN VARCHAR2,
                          stmt_id IN VARCHAR2:= NULL);

Explain an existing or potential materialized view with output to a VARRAY:

DBMS_MVIEW.EXPLAIN_MVIEW (mv          IN VARCHAR2,
                          msg_array   OUT SYS.ExplainMVArrayType);

One of the simplest ways to use DBMS_MVIEW.EXPLAIN_MVIEW is with the MV_CAPABILITIES_TABLE, which has the following structure:

CREATE TABLE MV_CAPABILITIES_TABLE 
(STATEMENT_ID      VARCHAR(30),   -- Client-supplied unique statement identifier
 MVOWNER           VARCHAR(30),   -- NULL for SELECT based EXPLAIN_MVIEW
 MVNAME            VARCHAR(30),   -- NULL for SELECT based EXPLAIN_MVIEW
 CAPABILITY_NAME   VARCHAR(30),   -- A descriptive name of the particular
                                  -- capability:
                                  -- REWRITE
                                  --   Can do at least full text match
                                  --   rewrite
                                  -- REWRITE_PARTIAL_TEXT_MATCH
                                  --   Can do at least full and partial
                                  --   text match rewrite
                                  -- REWRITE_GENERAL
                                  --   Can do all forms of rewrite
                                  -- REFRESH
                                  --   Can do at least complete refresh
                                  -- REFRESH_FROM_LOG_AFTER_INSERT
                                  --   Can do fast refresh from an mv log
                                  --   or change capture table at least
                                  --   when update operations are
                                  --   restricted to INSERT
                                  -- REFRESH_FROM_LOG_AFTER_ANY
                                  --   can do fast refresh from an mv log
                                  --   or change capture table after any
                                  --   combination of updates
                                  -- PCT
                                  --   Can do Enhanced Update Tracking on
                                  --   the table named in the RELATED_NAME
                                  --   column.  EUT is needed for fast
                                  --   refresh after partitioned
                                  --   maintenance operations on the table
                                  --   named in the RELATED_NAME column
                                  --   and to do non-stale tolerated
                                  --   rewrite when the mv is partially
                                  --   stale with respect to the table
                                  --   named in the RELATED_NAME column.
                                  --   EUT can also sometimes enable fast
                                  --   refresh of updates to the table
                                  --   named in the RELATED_NAME column
                                  --   when fast refresh from an mv log
                                  --   or change capture table is not
                                  --   possible.
                                  -- See Table 5-6
 POSSIBLE          CHARACTER(1),  -- T = capability is possible
                                  -- F = capability is not possible
 RELATED_TEXT      VARCHAR(2000), -- Owner.table.column, alias name, and so on
                                  -- related to this message. The specific 
                                  -- meaning of this column depends on the 
                                  -- NSGNO column. See the documentation for
                                  -- DBMS_MVIEW.EXPLAIN_MVIEW() for details.
 RELATED_NUM       NUMBER,        -- When there is a numeric value 
                                  -- associated with a row, it goes here.
 MSGNO             INTEGER,       -- When available, QSM message # explaining
                                  -- why disabled or more details when
                                  -- enabled.
 MSGTXT            VARCHAR(2000), -- Text associated with MSGNO.
 SEQ               NUMBER);       -- Useful in ORDER BY clause when 
                                  -- selecting from this table.

You can use the utlxmv.sql script found in the admin directory to create MV_CAPABILITIES_TABLE.

Table 5-6 lists explanations for values in the CAPABILITY_NAME column.

Table 5-7 lists the semantics for RELATED_TEXT and RELATED_NUM columns.