Re: I'm right to say you really don't need oracle materialized views? and we can do it manually?

Hello, the reason to do this is because I have standard one edition :), and I don't have the full features of replication

and because there is no a way to get fast replication(transactional) through triggers. :)

2016-08-01 22:27 GMT-04:00 Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>:

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>

--
http://www.freelists.org/webpage/oracle-l