Basic Replication (ReadOnly, Updatable) Materialized Views is included in
SE/SE1/S2
http://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109
So why would you want to write all that code yourself ?
Also, if the MV is in a separate database, you shouldn’t rely on a trigger
because it wouldn’t be able to handle network outage or remote instance
shutdown.
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Juan Carlos Reyes Pacheco
Sent: Tuesday, August 02, 2016 4:58 AM
To: ORACLE-L
Subject: I'm right to say you really don't need oracle materialized views? and
we can do it manually?
The free feature for Oracle to create materialized views in all
release<http://oraacle.blogspot.com/2016/08/the-free-feature-for-oracle-to-create.html>
Hello I'm asking if I'm right please.
I'm not sure, if we really need a special feature to create materialied views,
if we use an obvious methodology to create a materialized views like this, we
cna see we cna creaet even an option to update teh materialied view from a
trigger.
I dont understand clearly why to user oracle materialized views, to create a
materialized view, unless for standard and time saving, but at the end is the
same.
Because in this way we have more control of everything. Of course there are
more options but we cn add for ourselves.
Thank you :)
First we have the select, of one or more tables; and the table to store it
Second we create a procedure that insert the query in a table
Third we update it
1) if we want a transactional update, we create a procedure that inserts the
values for the new records in the trigger of the tables
2) if we want a log fast refresh, we create a log that stores id of records we
want to be updated
3) if we want a full update, we create job
Fourth
For the update process depending the option we have to create a procedure, add
to the trigger/job and that all.
Fifth
Aditionally we have a validation procedure that periodically if he found
differences can generate a full or partial refresh.
First
Create a package
XXX.PCK_MV_OWNER_TABLE
Procedure:FullRefresh
insert into table
select from tableA,tableB
-- note in this refresh you can put
Procedure:PartialRefreshperTrigger
This must be in a trigger update and or delete
insert into table
select from tableA,tableB
where column=:new.value and column2=:new.value2
Procedure:PartialRefreshperFunction
insert into table
select from tableA,tableB
where not exists in table
Procedure:PartialRefreshperFunction using log
This requires a table that stores ids of modified rows, and a trigger that
inserts the rowid when the needed columns of the table are modified, and
generates a refresh only of those records in the database.
insert into table
select from tableA,tableB
where not exists in table
Procedure:fast refresh fast
delete from;
FullRefresh;
Procedure:fast refresh online
truncate table;
FullRefresh;
Procedure:validation
with
a select from tableA,tableB
b select fro mtable
select count(*) from
(select from a
minus
select from b
union all
select from b
minus
select from a)
if count>0 then
fullrefresh; -fast or online it dependes on you
end if;
/
Jobs
create job executes validation periodically, depending the tables, etc.
create job if using log to update the materialized view periodically
optionally, create jobs makes partial insert, to reinforce validation.
optionally, makes a full refresh
:)
http://oraacle.blogspot.com/
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
