Materialized Views have the ability to speed up queries (even dramatically) while being transparent for the queries in a similar way as indexes resp. partitioned tables are. That means that we do not have to modify our queries in order to benefit from these structures. Unlike an ordinary view which is only a stored select statement that runs if we use the view, a materialized view stores the result set of the select statement as a container table. Let me demonstrate the benefit of that. I have a (for my little database) relatively large table and do an aggregation query on it:
SQL> desc sales Name Null? Type ----------------------------------------- -------- ---------------------------- PROD_ID NOT NULL NUMBER CUST_ID NOT NULL NUMBER TIME_ID NOT NULL DATE CHANNEL_ID NOT NULL NUMBER PROMO_ID NOT NULL NUMBER QUANTITY_SOLD NOT NULL NUMBER(10,2) AMOUNT_SOLD NOT NULL NUMBER(10,2) SQL> select count(*) from sales; COUNT(*) ---------- 7350744 SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES'; MB ---------- 286 SQL> show sga Total System Global Area 373293056 bytes Fixed Size 1219496 bytes Variable Size 134218840 bytes Database Buffers 234881024 bytes Redo Buffers 2973696 bytes SQL> set timing on SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002085 9 2219410.08 Elapsed: 00:00:04.51
As you can see, the aggregation takes round 5 seconds. Now I will speed that up using a Materialized View:
SQL> create materialized view mv1 enable query rewrite 2 as select channel_id,sum(amount_sold) from sales group by channel_id; Materialized view created. Elapsed: 00:00:05.69 SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002085 9 2219410.08 Elapsed: 00:00:00.01
The very same statement now takes way less time! Why is that so?
SQL> set autotrace on explain SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002085 9 2219410.08 Execution Plan ---------------------------------------------------------- Plan hash value: 2958490228 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 | 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 4 | 104 | 3 (0)| 00:00:01 --------------------------------------------------------------------------------
Because the optimizer transparently rewrote the query on the Materialized View! But what if we now change the content of the base table sales?
SQL> update sales set amount_sold=amount_sold+1 where rownum commit; Commit complete.
The materialized view is now stale and will no longer be used for query rewrite (as we can already determine by query runtime):
SQL> select mview_name,staleness from user_mviews; MVIEW_NAME STALENESS ------------------------------ ------------------- MV1 NEEDS_COMPILE SQL> set timing on SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002086 9 2219410.08 Elapsed: 00:00:04.52
In order to get statements rewritten against the materialized view again, we must refresh it by some method.
One method is on demand with a procedure call like in this example:
SQL> exec dbms_mview.refresh(list=>'MV1',method=>'C') PL/SQL procedure successfully completed. Elapsed: 00:00:04.62 SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002086 9 2219410.08 Elapsed: 00:00:00.01
Now the MV1 can get used again, as we see by runtime. That was a complete refresh – which can take a long time. We would like to get this done faster.
One comfortable method to get a fast refreshable materialized view is the usage of the package DBMS_ADVISOR:
SQL> vari t varchar2(50) SQL> begin 2 dbms_advisor.tune_mview(task_name=>:t, 3 mv_create_stmt=>'create materialized view mv1' 4 || ' refresh fast as' 5 || ' select channel_id,sum(amount_sold)' 6 || ' from sales group by channel_id'); 7 end; 8/ PL/SQL procedure successfully completed. SQL> set long 5000 SQL> select statement from user_tune_mview where task_name=:t order by action_id; STATEMENT -------------------------------------------------------------------------------- CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID ","AMOUNT_SOLD") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "ADAM"."SALES" ADD ROWID, SEQUENCE ("CHANNE L_ID","AMOUNT_SOLD") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST WITH ROWID DISABLE QUERY REWRIT E AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUN T("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SA LES.CHANNEL_ID STATEMENT -------------------------------------------------------------------------------- DROP MATERIALIZED VIEW ADAM.MV1 SQL> exec dbms_advisor.delete_task(:t) PL/SQL procedure successfully completed.
Usually, we need Materialized View Logs on the base tables and special expressions in the materialized view query to be able to do a fast refresh.
I simply take the coding from above now:
SQL> CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; 2 3 Materialized view log created. SQL> CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST WITH ROWID enable QUERY REWRITE AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SALES.CHANNEL_ID; Materialized view created.
After again modifying the base table sales, I will then try a fast refresh of the MV1:
SQL> set timing off SQL> update sales set amount_sold=amount_sold*1 where rownum commit; Commit complete. SQL> select mview_name,staleness from user_mviews; MVIEW_NAME STALENESS ------------------------------ ------------------- MV1 NEEDS_COMPILE SQL> set timing on SQL> exec dbms_mview.refresh('MV1','F') PL/SQL procedure successfully completed. Elapsed: 00:00:00.62
It worked! But do I always have to refresh manually? No, we can also schedule a regular refresh like this:
SQL> alter materialized view mv1 refresh start with sysdate next sysdate + interval '1' minute; Materialized view altered. SQL> alter session set nls_date_format='hh24:mi:ss'; Session altered. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 10:40:05 SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 10:41:04
Of course, a one minute refresh interval is extraordinary short. An other possibility to refresh the MVs very comfortably is on commit:
CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST on commit WITH ROWID enable QUERY REWRITE AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SALES.CHANNEL_ID; Materialized view created. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 COMPLETE 11:16:28 SQL> update sales set amount_sold=amount_sold*1 where rownum commit; Commit complete. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 11:19:56
But this may (although done in asynchronous fashion) slow down the DML on the base table, so it requires testing whether the slow down is noticeable or not. So far for now about Materialized Views. There is of course much more to say, but I think that this is enough for a brief introduction of that topic.
#1 von moses am Juli 9, 2009 - 23:36
#2 von arshad am September 9, 2009 - 06:37
Excellent step by step explanation!!! I exactly understand the purpose and use of materialized views now.
#3 von Arun am Oktober 7, 2009 - 12:09
Really Useful Stuff.
Explained very Clearly with Examples..
Great Job Man..
Keep try to post Useful Stuffs.
#4 von Ramesh Babu am Februar 11, 2010 - 21:27
Very good explanation , helped me in understanding the materialized view refresh concepts easily.
#5 von Uwe Hesse am Februar 12, 2010 - 09:19
Thank you guys for your positive feedback! It’s what keeps Bloggers blogging, I think 🙂
#6 von Tamas Kocsis am März 2, 2010 - 23:22
I’m doing Oracle trainings nowadays and the teacher is good, the curriculum is good, but I wish it all would be so clear and distinct as your explanation and examples.
+ 1 subscription 🙂
#7 von Uwe Hesse am März 3, 2010 - 08:39
thanks for the compliment! And I wish you a nice & rewarding learning experience. Hope you take a course from Oracle University 🙂
#8 von Mili am Juli 29, 2010 - 07:26
Really nice …can u provide me the link on which u publish articles/tutorials.
#9 von Muhammad Adnan am Dezember 22, 2010 - 21:01
Great method of teaching.
#10 von Murali M am Januar 5, 2011 - 03:55
Thanks, really a nice explanation.
#11 von Uwe Hesse am Januar 5, 2011 - 17:55
You’re welcome 🙂
#12 von deepssharma am Januar 14, 2011 - 11:57
really good one :)…
when i started searching the material for materialized views I read the reasons why materialized views are used as follows:
• Ease Network Loads
• Create a Mass Deployment Environment
• Enable Data Subsetting
• Enable Disconnected Computing
but they speed up the queries…I came to know from your article only.
#13 von Vikas am März 13, 2011 - 03:12
Nice article. Thoroughly explained with examples. Thanks for publishing this article.
#14 von Uwe Hesse am März 14, 2011 - 16:49
you’re welcome 🙂
Thanks for the nice feedback!
#15 von Bora Yüret am April 1, 2011 - 08:38
A very good teaching style, thanks for the tutorial.
#16 von Uwe Hesse am April 1, 2011 - 08:54
you’re welcome 🙂
#17 von Sridhar am Mai 17, 2011 - 12:35
Explanation was really Excellent
Can please give me some examples on how to implement Application Contexts in Oracle.
#18 von Uwe Hesse am Mai 18, 2011 - 13:08
thank you for the positive feedback! I have no plans to cover Application Contexts for now, though. The topic seems even a little boring to me, sorry 🙂
#19 von Md. Rajibul Islam am Mai 25, 2011 - 13:09
Your teaching is really helpful for me and I understand the topic clearly. Many many thanks to you.
#20 von Uwe Hesse am Mai 26, 2011 - 08:10
You’re welcome, I am happy if you find it helpful 🙂
#21 von Krishna Kumar am Mai 27, 2011 - 22:56
Awesome tutorial. Just Googled to know the definition difference between views and materialized view… But came to know more stuffs from your blog.. Nice work.. Thank you…
#22 von Uwe Hesse am Mai 30, 2011 - 08:51
Thank you for the nice feedback, Krishna!
#23 von Ezekiel Filane am Juni 2, 2011 - 11:47
Excellent! 100%….ilike 🙂
#24 von Uwe Hesse am Juni 3, 2011 - 08:52
Ezekiel, you’re welcome 🙂
#25 von totalz am Juli 4, 2011 - 10:06
Is there a way to refresh on commit with „complex“ query, like multiple tables in from clause? I have created MV logs on those master tables, but same “
ERROR at line 1:
ORA-12015: cannot create a fast refresh materialized view from a complex query
#26 von Uwe Hesse am Juli 4, 2011 - 16:33
Unfortunately, it is not always possible to create fast refreshable MVs – it depends on your Database version, though: 11g may be able to support fast refresh where 10g doesn’t.
It is often not suffcient to have MV Logs only. The MV query must be customized accordingly.
Did you try DBMS_ADVISOR.TUNE_MVIEW already?
#27 von Jeannie Brown am Juli 14, 2011 - 23:01
If the table gets cleared that the materialized view is pointing to will it still have the data in the view as long as it hasn’t been refreshed?
#28 von Uwe Hesse am Juli 15, 2011 - 08:32
Yes, it will stay the same as before until the refresh.
#29 von G SElvam am August 27, 2011 - 09:10
Thanks for GOod explaination and reason to use M Views.
One my clinet facining ora-03113 frequently…..what is reason on Oracle 11g – The same was working well on Oracle 10g
#30 von Uwe Hesse am September 1, 2011 - 18:38
Thank you for the nice words! I have no clue about the cause of your error, though. Suggest that you invoke Oracle Support for this kind of problems: They are very good with this and can even make a living from it 🙂
If you have no support contract, you may try
#31 von neerajvasudeva am Dezember 28, 2011 - 22:16
Concept very nicely explained.
However I have a very small doubt, let’s say after my user ‚X‘ created MV, now as i understood from your blog post, if user ‚Y‘ invokes query on source/base table , it would internally hit MV How can user ‚Y‘ avoid hitting internally MV , can i assume once we remove „query rewrite“ clause which was used during creation of MV it would not hit MV even if someone is just querying on base / source table ??
Will try it out once i have a demo system, but appreciate if u can clear my doubt in the meantime.
#32 von Uwe Hesse am Dezember 29, 2011 - 15:13
Generally, there should be no reason why user Y will not want to benefit from the speedup, accompanied by the internal rewrite on the MV.
If for any reason, the rewrite is not desired, you can switch it off on the session level (alter session set query_rewrite_enabled=false;) or on the statement level with a hint (/*+ NO_REWRITE */)
There should be no need to change the rewrite clause of the MV, since that would disable the rewrite for every session.
#33 von C Franklin am Januar 11, 2012 - 16:57
We are using MVs to move data from one schema (owned by another group) to our schema to minimize the need for constantly hitting their data. We now would like to have the ability to backup the prod MVs and move them down to a DEVL/Test environment but this invalidates the MVs. Is there a way just to backup the PROD MV tables (not the definitions) and move them down and have the ability to write to them (to get rid of personal data)? Otherwise we have to backup the entire schema owned by the other group and rerun all ETL in the lower environment. Your help is appreciated.
#34 von C Franklin am Januar 11, 2012 - 16:58
Sorry… didn’t press „Notify me…“
#35 von Uwe Hesse am Januar 12, 2012 - 12:18
I am not sure I understood your issue. Why not just do a create table as select * from MV for the desired schema?
#36 von C Franklin am Januar 13, 2012 - 17:18
That’s what we are going to have the DBAs perform for us. But this means the lower environments won’t really be like production. So if the SysTest or the UAT testers need to test ETL they can’t because the MVs are now tables and our MV refresh code won’t work. If we CTAS/backup from the other schemas‘ prod into the lower environment matching schema to perform ETL from there, then we have issues because we share the lower environments with the other groups too and we can corrupt each others testing. Was hoping there was a way to backup the MVs and plop them into the lower environments without corrupting them, but so far we haven’t found such a way. Also, we are unable to update the records in the MVs to mask the personal data. MVs are cool, but have issues. Thanks for you help…
#37 von vijaymukthavijay am Februar 27, 2012 - 06:54
This is very very clear in understanding.
The demonstration is very easier to understand.
Thank you very much for helping us in gaining the knowledge..
#38 von Ajinkya K. Soitkar am April 18, 2012 - 16:53
Hello Uwe! It’s a great note! Thanks for a detailed explanation. I just had a query,
Are mview logs dropped from the mview log table once they are used for fast refresh? Also, if the mview log size is very high for e.g. 2-3 GB’s does it mean it mview has stale data? Or the Mviews are not being used at all?
Would appreciate your inputs here. Thanks!
Ajinkya S, DBA
#39 von Dharmendra am April 19, 2012 - 07:57
I learn lot of things
#40 von Dharmendra am April 19, 2012 - 07:58
Can we refresh our mv using trigger
#41 von Uwe Hesse am April 19, 2012 - 08:28
Ajinkya, thank YOU for the feedback 🙂 To your questions: MV logs are not dropped after a refresh, but their rows get removed when all dependent MVs have been refreshed. Very large MV logs that never get their rows deleted indicate that no (fast) refresh has been done for all dependent MVs for a long time. An easy way to see whether your MVs are beneficial at all for your workload is the SQL Access Advisor.
#42 von Uwe Hesse am April 19, 2012 - 08:30
Dharmendra, you’re welcome. Instead of using a trigger on the base table, better use refresh on commit. It basically does the same but better 🙂
#43 von yugen am Mai 7, 2012 - 06:29
great one sir
#44 von umesh chandra seth am Juni 4, 2012 - 10:54
thanks sir .its really very easy to understand..
#45 von Uwe Hesse am Juni 4, 2012 - 15:42
You’re welcome & thanks for leaving this nice feedback 🙂
#46 von Vladimiras am Juni 13, 2012 - 07:41
thank you very much for interesting information.
I’d like to ask — can materialized view (as normal table) will be created with primary key?
#47 von umesh chandra seth am Juni 13, 2012 - 07:45
can we create Materialized Views with delete script,
suppose we have some delete script i want faster deletion of data from the table
at that situation can we create m.v.
#48 von Uwe Hesse am Juni 13, 2012 - 08:46
Vladimiras, you can create a materialized view as an index organized table (that always has a primary key) if the base table also has a primary key. See here:
#49 von Uwe Hesse am Juni 13, 2012 - 08:48
umesh chandra seth, I don’t think so, because I don’t see how the MV could possibly speed up the delete statement.
#50 von umesh chandra seth am Juni 14, 2012 - 09:34
hello sir thanks for your reply,
we have some delete script like
delete from emp;
delete from empma;
delete from stud;
like that we are treating above all delete as a script ,i am using toad for delete ,at that situation this is take much time reason behind million data in each table,so i ant fast deletion of data .
what can we use?
#51 von Uwe Hesse am Juni 14, 2012 - 12:01
When you delete ALL the rows from the tables, it would be much faster to truncate them:
#52 von Saleem Qureshi am Juli 22, 2012 - 13:28
I have read you blog and found very much detail and very information, I need you help about my problem I m running a database with almost 100 materialized views now i need to revoke start with statement from every MV and want update with script.
I need a alter Materialized view command to remover start with line from MV.
#53 von karthikeyan am August 8, 2012 - 11:04
amazing……., good explanation on step by step process.
#54 von oracle dba architecture am August 15, 2012 - 22:36
Now i came to very clearly what Materialized View is….
Thanks friend for your material
#55 von Bhaskar am August 17, 2012 - 04:49
Good explanation, and good approach of teaching, Straight teaching what is what and what is needed.
I have a question, Why don’t we create physical table for aggregation for which materialized view is created. Anyway Materialized view also takes storage and refresh . Why can’t create table and post the updates as and when required.
And materialized view is invoked only when that query fired, its if its a different query it won’t be referenced right.
#56 von abdul_ora am August 17, 2012 - 08:08
O.M.G really you gave me assets like knowledge. once again thank you thank you v. much 🙂
#57 von karthik am August 21, 2012 - 05:51
#58 von Uwe Hesse am August 25, 2012 - 08:33
Thank you, karthikeyan, oracle_dba, Abdul and karthik for your nice feedback, very much appreciated 🙂
Saleem, I don’t have such a script – you might try OTN Database forum (see LINKS on the right) for that.
Bhaskar, you are right, you could create & maintain your own self cooked ‚Materialized Views‘. But why bother when we have that feature already in place ready for use? For sure it would be very hard to replicate Query Rewrite
#59 von Stavan Shah am September 26, 2012 - 11:34
It was great reading the article and practically understanding the concept…yeah with examples it makes it lot simpler.
I have a case where in the MV has partitions and goes on accumulating data.Since the data volume is huge I need to drop the partitions on a daily basis from the MV.
But after PMOP operations are performed I am unable to do a fast refresh (as expected ORA-32320).
Doing a complete refresh on MV is taking long time and is impacting the inserts/updates executed during that time.
Can you suggest any alternative such that the Fast refresh can be done in above scenario or such that the insert/updates remain unimpacted.
Thanks once again for this wonderful article…!!
#60 von anirban sengupta(calcutta)(india) am Oktober 5, 2012 - 18:51
sir your posting is unparalleled and much easy to get into the inner concepts.
#61 von Uwe Hesse am Oktober 9, 2012 - 21:30
Thanks for the nice feedback!
#62 von Taimur khan am Dezember 18, 2012 - 14:18
sir , a really helpful post on Materialized Views and such an easy and detailed topic. very helpful
#63 von Uwe Hesse am Dezember 19, 2012 - 09:38
Taimur khan, you’re welcome 🙂
#64 von pnkj am Dezember 25, 2012 - 21:03
Thank u very much,it was very nice and easily understandable.initially i was very confused about the materialised view concept but this makes my concept very clear….thank you!
#65 von Uwe Hesse am Januar 6, 2013 - 11:19
pnkj, I’m glad you have found it helpful 🙂
#66 von Dheeraj am Januar 11, 2013 - 12:13
I have two questions regarding materialized view
1. I have a materialized view that refreshes after every 10 seconds (where is data is from two tables). An insert trigger is written on the materialized view where i insert the data into a new table. I have a situation where a data is entered into the base table and it is immediately updated before the next refresh occurs. In this case I will be loosing the first data in the trigger table. How can get the original record.
2.On the materialized view does the Update trigger make sense as whenever you update a record in the base table, the materialized view refreshes and the insert trigger will be called.
Please advise on the above.
Thanks in advance.
#67 von Uwe Hesse am Januar 12, 2013 - 13:40
Dheeraj, the whole idea of putting triggers on the MV container table sounds a bit strange to me. I have too little insight – and too little interest, frankly – in your application design to advice anything specific. You may get better answers in an open forum. See LINKS on the right. Good luck 🙂
#68 von etched laser am Januar 17, 2013 - 08:29
I’ve learn several just right stuff here. Certainly value bookmarking for revisiting. I wonder how a lot attempt you put to create one of these great informative site.
#69 von Naren am Februar 6, 2013 - 16:36
Its just great…
#70 von Sudipta am März 8, 2013 - 11:02
realy nice metarial
#71 von Uwe Hesse am März 14, 2013 - 16:43
Sudipta, you’re welcome 🙂
#72 von Khudadad am März 26, 2013 - 11:02
Nice & perfect.
#73 von Uwe Hesse am April 2, 2013 - 07:44
Khudadad, you’re welcome 🙂
#74 von Ramesh am April 14, 2013 - 18:10
Really nice one. Presentation and concept is very good sir.Without reading second time i understood the concept.Thanks a lot.
#75 von Uwe Hesse am April 16, 2013 - 08:38
Thank YOU, Ramesh, for taking the time to leave such a nice feedback – very much appreciated 🙂
#76 von sdfsdfsdfds am Juli 9, 2013 - 21:25
Excellent tutorial thanks.
#77 von subramanyam am Juli 15, 2013 - 11:51
thq sir i easily understood this concept
#78 von Raju am Juli 19, 2013 - 20:37
real good article and nice explanation. I am eager to read other topics from this site. could you please share the links for other articles on parallization, partitioning, ET as well.
#79 von Waqas am Juli 31, 2013 - 03:26
A Comprehensive Article! Thanks
#80 von Uwe Hesse am August 13, 2013 - 09:45
Thank you guys for taking the time to leave such a nice feedback! Very much appreciated 🙂
#81 von sudhakar amineni am August 13, 2013 - 14:26
Its Great Explanation…..step by step.
#82 von suman am August 30, 2013 - 00:09
YOUR TEACHING IS EXCELLENT.
But i have a doubt, with out creating Materialized view log we also create the materialized view so why we create the Materialized view log on ?
#83 von Uwe Hesse am August 30, 2013 - 14:25
Suman, thank you for the nice words 🙂 MV Logs are (for most cases) required for the fast refresh of the MV
#84 von suman am August 30, 2013 - 17:45
how to calculate the value of PCTFREE parameter of a table ?
#85 von Uwe Hesse am September 4, 2013 - 08:44
Suman, your question is not much related to the posting, is it?
As a guideline for setting PCTFREE, take this formular
PCTFREE = 100 * UPD / (average row length)
UPD = Average amount added by updates, in bytes. This is determined by subtracting the average row length after the insert from the current average row length
Average row length = After gathering statistics on the table, get this value from the avg_row_len column of dba_tables
#86 von suman am September 4, 2013 - 18:35
thank you sir.
#87 von RAJ KUMAR am Oktober 11, 2013 - 12:10
very understandable and amazing explanation…… one can very easily understand the concepts.
Creative way of explanation.
#88 von varun am April 9, 2014 - 08:50
thank you so much sir……excellent way to understand the topic….
#89 von Uwe Hesse am April 9, 2014 - 13:50
Thank YOU for stopping by and taking the time to leave a nice comment 🙂
#90 von NT am Mai 11, 2014 - 00:29
Excellent explanation with example.
#91 von santoshi am Juli 7, 2014 - 06:51
really impressive explanation,,Thank you
#92 von srinivas am Juli 15, 2014 - 05:08
First of all Thanks for your brief explanation. I hope you will be post more concepts
#93 von Rajkumar am September 15, 2014 - 12:01
Your are really great. i have tried to learn the same concept from oracle docs but i could not understand the use of this mviews. Now im clear about the point which you teached above. thanks a lot.
#94 von Dulcie am September 23, 2014 - 12:11
You really make it seem so easy with your
presentation but I find this topic to be actually something that I think I would never understand.
It seems too complex and very broad foor me. I am looking forward for your next post, I’ll try to
get the hang of it!
#95 von madhav am Dezember 1, 2014 - 15:58
This is very very clear in understanding.
The demonstration is very easier to understand.
Thank you very much for helping us in gaining the knowledge..
#96 von Prabhakar K am Februar 26, 2015 - 15:31
Thank you very much for your article on Materialized views. One small request. You have explained on commit method. what about on Demand method mv.s . Please give me an example when we have to go for on commit and on demand. what is the difference b/w them. ?
Please provide me an example on this.
#97 von Omesh am April 5, 2015 - 13:27
Good Teaching. I tried to create a MV using the option ‚WITH PRIMARY KEY‘. The underlying table contained a PRIMARY KEY. Now I tried building a MV on union of Selects from the same Table. The MV got created but the primary key was not seen in the MV just created. Where as when I created a MV with Primary KEY option on a table with primary key using the ‚Select * from Table1, the MV did contained the Primary Key.
What is the reason behind it that in first case the Primary Key did not get created.
#98 von Uwe Hesse am April 29, 2015 - 15:41
Prabhu, you see examples for both refresh fast on demand and on commit in the article. A refresh on commit may impact the performance of the DML that was done before the commit, though. This has been a concern especially for older versions and may no longer show up significantly from 11g on, though. Still it should be tested with that focus before taking refresh on commit into production.
#99 von Uwe Hesse am April 29, 2015 - 15:47
Omesh, please take that question (together with the coding of the MVs and the output you worry about) into an open forum like https://community.oracle.com/community/database/general_questions or to support.oracle.com
#100 von lubinska agencja reklamowa am Mai 12, 2015 - 14:18
An impressive share! I’ve just forwarded this onto a colleague who has been doing a little homework on this.
And he in fact bought me breakfast because I found it for him…
lol. So allow me to reword this…. Thanks for the
meal!! But yeah, thanx for spending the time to discuss this issue here on your web page.
#101 von Sreaky Sri am Juli 17, 2015 - 14:01
Hi Sir, Its a clear show all the way. Just need some more help on the same. We are loading 17 Crores of data in our Fact table through the concept of Partition concept in oracle. So our reporting tool is BO, which will read the data from FACT and DIMENSION and will help in generating the reports. But Its taking huge time for generation in spite of creating the index over the fact table. Can you Please help in tuning the performance while report generation which actually at the blackened taking a hell lot of time in reading from the FACT??
#102 von Guilherme Mesquita am August 7, 2015 - 20:59
Thank you very much for the tutorial. Now it’s clear the concept and use of materialized views and how to maintain them.
#103 von devpriyo am August 19, 2015 - 14:13
Excellent explanation Uwe. I have a question. I have my client’s application running on an OLTP system. There is a database with some master tables which is getting updated every second. Now, our reporting solution needs us to create a Materialized View so that the reports can pick the data from the view instead of hitting the actual tables. Now, as per my understanding, FAST REFRESH will not work as our sql queries have multiple selects, unions, joins etc. Do you think REFRESH COMPLETE will be successful in this environment ?
#104 von Kumar am August 23, 2015 - 15:11
Great explanation Sir, I have searched many articles on MV it is the BEST one. I have two doubts, please clarify
1. How come Elapsed time decreased significantly by simply issuing CREATE MATERIALIZED VIEW statment? could you please explain what exactly done at backend.
2. what does „ON PREBUILT TABLE CLAUSE“ mean in materialized view?
#105 von Anvi am September 18, 2015 - 20:46
All i could say is it’s just fab explanation.
#106 von Uwe Hesse am Oktober 16, 2015 - 09:39
Kumar, the MV is a very small table compared to the base table that is selected from in the query. The optimizer realizes that the MV can be accessed instead of the base table. Therefore the speedup. ON PREBUILT TABLE means that the container table of the MV exists already and shall now be used to speed up queries transparently. So only the dictionary information about the MV is created, not the container table itself, which can be very time consuming.
#107 von Narasimha am Oktober 16, 2015 - 11:59
only on one cloumn or multiple columns are possible to create the materialized views,Please clarify to me on this.
which is the best refresh method to use the materialized views in production database.
Thanks for your time.
#108 von Arthur Brasil am Dezember 11, 2016 - 18:33
Great article Uwe Hesse, thanks. I’m just confused why I’m having the error below:
This is my command:
vari t varchar2(200)
mv_create_stmt=>’CREATE MATERIALIZED VIEW MV_FILTER_TYPES REFRESH FAST AS
select l.log_id, l.name as log_name, mt.type_id msg_type_id, mt.name msg_type_name, t.thread_id, t.name thread_name
from logs l
join logs_info li on li.log_id = l.log_id
join message_types mt on mt.type_id = li.type_id
join threads t on t.thread_id = li.thread_id
group by l.log_id, l.name, mt.type_id, mt.name,
And this is the error:
Error report –
ORA-13600: error encountered in Advisor
QSM-00781: the specified value MVIEW is invalid for parameter ANALYSIS_SCOPE. Valid keywords are FULL, ALL, EVALUATION, INDEX
ORA-06512: at „SYS.PRVT_ADVISOR“, line 4062
ORA-06512: at „SYS.DBMS_ADVISOR“, line 348
ORA-06512: at „SYS.PRVT_TUNE_MVIEW“, line 506
ORA-06512: at „SYS.PRVT_TUNE_MVIEW“, line 970
ORA-06512: at „SYS.DBMS_ADVISOR“, line 760
ORA-06512: at line 2
13600. 00000 – „error encountered in Advisor\n%s“
*Cause: An error occurred in the Advisor.
This message will be followed by a second message giving
more details about the nature of the error.
*Action: See the Advisor documentation
for an explanation of the second error message.
I was „googling“ about it, but couldn’t find out what is wrong. Do you have some suggestions? Thanks.