[vue Materialise] - On Commit

**jacquesh** · 21/11/2006, 17h31

Bonjour,

J'ai une question : suite à une recherche sur le METALINK, les bouquins DBA ORACLE PRESS, ... je n'arrive pas a trouver un exemple d'une vue matérialisé qui fonctionne avec l'option 'ON COMMIT'.

Quelques soit les options testés j'ai toujours une erreur ORA-12054.

Quelqu'un peut il me fournir une requête de création d'une vue matérialisé qui fonctionne avec l'option 'on commit'

Version : Oracle 8.1.7.4

une idée.

**orafrance** · 22/11/2006, 09h19

Tu ne dois pas respecter les conditions d'utilisation de cette option

Explanation
-----------

One of the mandatory requirements for creation of an on-commit materialized
view has not been satisfied.

ON-COMMIT :
Refresh occurs automatically when a transaction that modified one of the
materialized view's fact tables commits.
Can be used with materialized views on single table aggregates
and with materialized views containing joins only.

As can be seen from above, an ON-COMMIT can be used only under specific cases:
These are:

1. The M.V. should have a single table aggregate or
2. the M.V. should have a join only.
3. count(*) must be present for Single-Table Aggregates (see example above).
4. count(<col>) should be present.
Here, <col> stands for the column which is being aggregated.
Note: the only time that count(col)is not required is when
the aggregate itself is a count(col).
5. It should be possible perform a fast refresh on the materialized view.

Fast refresh by itself has a few restrictions.
These are as follows:
a)The FROM list must contain base tables only (that is, no views).
b)It cannot contain references to non-repeating expressions like
SYSDATE and ROWNUM.
c)It cannot contain references to RAW or LONG RAW data types.
d)It cannot contain HAVING or CONNECT BY clauses.
e)The WHERE clause can contain only joins and they must be equi-joins
(inner or outer) and all join predicates must be connected with
ANDs. No selection predicates on individual tables are allowed
f)It cannot have subqueries, inline views, or set functions like
UNION or MINUS.

In addition for M.V.'s with Single-Table Aggregates and Materialized Views
with Joins and Aggregates, there are some more conditions on refresh
to the ones mentioned above:

Single Table Aggregates:
=======================
i) They can only have a single table.
ii) The SELECT list must contain all GROUP BY columns.
iii) Expressions are allowed in the GROUP BY and SELECT
clauses provided they are the same.
iv) They cannot have a WHERE clause.
v) They cannot have a MIN or MAX function.
vi) A materialized view log must exist on the table and must contain all
columns referenced in the materialized view. The log must have been
created with the INCLUDING NEW VALUES clause.
vii) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
viii) If VARIANCE(expr) or STDDEV(expr) is specified,
you must have COUNT(expr) and SUM(expr).

Joins and Aggregates :
=====================

i)The WHERE clause can contain inner equi-joins only
(that is, no outer joins)
ii)Materialized views from this category are FAST refreshable after
Direct Load to the base tables; they are not FAST refreshable after
conventional DML to the base tables.
iii)Materialized views from this category can have only the
ON DEMAND option (so, the on-commit cannot be used for this category).