Materialized views for 53 tables

[Roshan]

Hi Erman,

Oracle Database 12.1.0.2

Red Hat linux 6



 

I am following

 

https://oracle-base.com/articles/misc/materialized-views

 

to setup materialized view for 53 tables. I would like to know if I need to create 53 materialized views(1 for each tables) or only 1 materialized view will be enough.

 

The materialized view will be used to query tables for reporting.

MVtables.txt
 

Thanks,

Roshan

[ErmanArslansOracleBlog]

You can have multiple MWs.. But you can put them into 1 refresh group to make them be refereshed in one go.

[Roshan]

Hi Erman,

thanks for update.

can you please advise if primary key is necessary for each table in order to create the materialized views?

We have primary key enabled for only 20 tables. Is there a way I can bypass the primary key in he create materialized view log statement?

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

[ErmanArslansOracleBlog]

You are talking about the WITH Clause, right?

So, if you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default and if you don't have primary key on master table, then you will end up with ORA-12014.

Read this: Differences Between Rowid & Primary Key Materialized Views (Doc ID 254593.1)

Well. You can also use rowid rather than primary key but; note that,  without a primary key, you cannot do a materialized view fast refresh.