Home » RDBMS Server » Performance Tuning » Materlized View (Oracle 11)
Materlized View [message #658097] Sat, 03 December 2016 12:25 Go to next message
meetmallela
Messages: 1
Registered: December 2016
Junior Member
hi All,

Currently we are using a view which reads data from some 50-55 BASE tables and appends data from each of these tables (tab_1 union all tab_2).

Each table may return 100-500 rows based on the selection's defined of view.

Existing View definition looks something like this:
####################################
create view v_temp as
select a.c1, a.c2 from tab_1 a union all
select b.c1, b.c2 from tab_2 b union all
select c.c1, c.c2 from tab_3 c ;
####################################

with increased in number of records in the base tables , we have noticed that time taken to fetch the data for the view is also increasing , now to address this issue we want to move to material view with REFRESH FAST ON COMMIT option instead of simple view. Data gets refreshed two times in a day in our base tables.

but we realized that its mandatory to have "An Oracle materialized view log must be present for each base table" , if we need to create a materials view, in our scenarios we will end up in creating materialized view log for all 50 tables, we are okay to invest onetime efforts for the same but we wanted to understand the following:
1) if this entire process will act as overhead on the data load time of the 50 tables ?
2) i don't have to join the master tables , i just have to union the results of 50 tables , dose this still make sense to go for Material view or is that better to wrote a the results to a table and write ETL routine to append new records after each batch execution of the data load in the 50 base tables.

appreciate any inputs.

regards,
Krishna
Re: Materlized View [message #658099 is a reply to message #658097] Sat, 03 December 2016 13:36 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If base tables are refreshed twice a day, why would you refresh a materialized view on commit? Schedule it, instead, using START WITH ... NEXT clause. More info here.
Re: Materlized View [message #658101 is a reply to message #658099] Sat, 03 December 2016 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 26203
Registered: January 2009
Location: SoCal
Senior Member
I question the validity of the whole data model.
I suspect that the data does does NOT conform to Third Normal Form.
Do each of the 50 tables have application data as part of their table_name?
Re: Materlized View [message #658104 is a reply to message #658097] Sat, 03 December 2016 14:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8890
Registered: November 2002
Location: California, USA
Senior Member
It is hard to say what is best without understanding why you have the design that you have. It may be that you need to change the design. In general, a regular view is just a stored query that does not store data, whereas a materialized view is more like a table. It may be that you just need appropriate indexes and current statistics to speed up the query of your regular view. You may want to set up a test environment and test various things.

If you decide to use a materialized view with refresh fast on commit, then you may find that there are things that you need or want. Please see the demonstration below, that shows tables, materialized view logs that require either primary key or rowid, materialized view with marker columns that are required when you use union, and query rewrite that may speed some queries.

SCOTT@orcl_12.1.0.2.0> create table tab_1 as select empno c1, ename c2 from emp where 1 = 2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> create table tab_2 as select empno c1, ename c2 from emp where 1 = 2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> create table tab_3 as select empno c1, ename c2 from emp where 1 = 2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> create materialized view log on tab_1 with rowid
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> create materialized view log on tab_2 with rowid
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> create materialized view log on tab_3 with rowid
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> create materialized view v_temp
  2  refresh fast on commit
  3  enable query rewrite
  4  as
  5  select a.c1, a.c2, a.rowid as rid, 1 as marker from tab_1 a union all
  6  select b.c1, b.c2, b.rowid as rid, 2 as marker from tab_2 b union all
  7  select c.c1, c.c2, c.rowid as rid, 3 as marker from tab_3 c
  8  /

Materialized view created.

SCOTT@orcl_12.1.0.2.0> select * from v_temp
  2  /

no rows selected

SCOTT@orcl_12.1.0.2.0> insert into tab_1 select empno, ename from emp where deptno = 10
  2  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> insert into tab_2 select empno, ename from emp where deptno = 20
  2  /

5 rows created.

SCOTT@orcl_12.1.0.2.0> insert into tab_3 select empno, ename from emp where deptno = 30
  2  /

6 rows created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> select * from v_temp
  2  /

        C1 C2         RID                    MARKER
---------- ---------- ------------------ ----------
      7499 ALLEN      AAAkO9AAGAAAAPGAAA          3
      7521 WARD       AAAkO9AAGAAAAPGAAB          3
      7654 MARTIN     AAAkO9AAGAAAAPGAAC          3
      7698 BLAKE      AAAkO9AAGAAAAPGAAD          3
      7844 TURNER     AAAkO9AAGAAAAPGAAE          3
      7900 JAMES      AAAkO9AAGAAAAPGAAF          3
      7369 SMITH      AAAkO8AAGAAAAO+AAA          2
      7566 JONES      AAAkO8AAGAAAAO+AAB          2
      7788 SCOTT      AAAkO8AAGAAAAO+AAC          2
      7876 ADAMS      AAAkO8AAGAAAAO+AAD          2
      7902 FORD       AAAkO8AAGAAAAO+AAE          2
      7782 CLARK      AAAkO7AAGAAAAO2AAA          1
      7839 KING       AAAkO7AAGAAAAO2AAB          1
      7934 MILLER     AAAkO7AAGAAAAO2AAC          1

14 rows selected.

Re: Materlized View [message #658107 is a reply to message #658097] Sun, 04 December 2016 02:08 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Tuning the view would be an alternative I would investigate first. However:

Quote:
1) if this entire process will act as overhead on the data load time of the 50 tables ?
2) i don't have to join the master tables , i just have to union the results of 50 tables , dose this still make sense to go for Material view or is that better to wrote a the results to a table and write ETL routine to append new records after each batch execution of the data load in the 50 base tables.

1. There will certainly be overhead on DML against the detail tables, as the materialized view updates are synchronous with the transaction(s). Scheduling the refresh to be asynchronous might be a better solution.
2. Another capability you could consider would be Change Data Capture. Or capturing and applying changes using triggers and advanced queues (in effect, roll-your-own CDC).


By the way, I wish you would not say "record" when you mean "row".
Re: Materlized View [message #658119 is a reply to message #658107] Mon, 05 December 2016 02:59 Go to previous message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're routinely unioning data from separate tables together like that it does strongly suggest the separate tables should be combined into one single table. If you can do that it would probably fix your performance issues.
Previous Topic: In-database archival
Next Topic: ORA-01555 error during expdp
Goto Forum:
  


Current Time: Sat Oct 20 16:48:03 CDT 2018