| Materlized View [message #658097] |
Sat, 03 December 2016 12:25  |
 |
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 #658104 is a reply to message #658097] |
Sat, 03 December 2016 14:26  |
 |
Barbara Boehmer
Messages: 9104
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 |
John Watson
Messages: 8964
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 |
cookiemonster
Messages: 13963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
