Friday 3 June 2016

Can we create triggers on materialized views in oracle? If so, is a good practice to use them?

Yes we can create On updatable materialized views , if they use the "I_AM_A_REFRESH" function so as to not fire during a refresh.
I_AM_A_REFRESH Function
This function returns the value of the I_AM_REFRESH package state.

Syntax
DBMS_MVIEW.I_AM_A_REFRESH RETURN BOOLEAN;

Return Values
A return value of true indicates that all local replication triggers for materialized views are effectively disabled in this session because each replication trigger first checks this state. A return value of false indicates that these triggers are enabled.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#CEGHBIBJ

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref7906

Note:
If you create a trigger on a base table of a materialized view, then we need to ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE."

It means that for a read only materialized view, the only time the trigger would fire would be during the refresh process - and you would have logic that causes your trigger to just exit at that point (it would never do anything)

Triggers on a read only materialized view might appear to work - but you cannot rely on them working.
An update of a row in a materialized view might be done as a DELETE+INSERT.
A refresh of a materialized view might involve a delete + insert of every row.
A refresh might involve a truncate plus direct path load of ever row.

(the last two will probably happen sometime, you would lose anything your trigger had done in the past).

You do not know how we refresh, it would be a very shaky proposition to have a trigger on a read only materialized view. It was not recommended






Tuesday 10 May 2016

Oracle Materialized view

Materialized View

Materialized view is a database object which stores the query as well as the results in it.
Normal view will not store the results. Materialized view physically stored in the Database.

Using Materialized view we can
1. Pre-calculate Complex joins
2. Execute the aggregate function

Oracle automatically rewrites the SQL query, so the performance of the execution is faster.

Materialized view is created based on the base table. The table can be in the same database or a remote database.

There are 3 methods available to synchronize with base table
1. Refresh Fast
2. Refresh Complete
3. Refresh Force

There are two modes for Refresh Techniques
1.On Demand (need manual refresh procedure)
2.On Commit (automatic)


1.On Demand (need manual refresh procedure)

Create table learner (learner_id number(10), learner_name varchar2(100), topic varchar2(200), hours number(5) );

create materialized view mv_learner
as
select learner_id , learner_name  from learner;

(or)
create materialized view mv_learner
refresh on demand
as
select learner_id , learner_name  from learner;

Refresh procedure:
sql> execute dbms_mview.refresh('mv_learner ');

when we execute the above procedure materialized view ("mv_learner") will synchronize with the base table ("learner")

2.On Commit (automatic)

create materialized view mv_learner
on commit
as
select learner_id , learner_name  from learner;

Restrictions
Materialized views can only refresh ON COMMIT
  1. The materialized view cannot contain object types or Oracle-supplied types.
  2. The base tables will never have any distributed transactions applied to them.


Materialized view allows to copy the object from Remote Data base, so we can do the replication using the materialized view.