Brief introduction into Materialized Views

This week, I am teaching an Oracle Database 11g Data Warehouse Administration course in Munich. One of the focus areas of that course are Materialized Views, and I have developed some examples for that course that I like to share with the Oracle community. Other themes of that four days course are Partitioning, ETL and Parallelization.

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<2;

1 row updated.

SQL> 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<2;

1 row updated.

SQL> 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

Materialized View, Query Rewritw, MV Log, Refresh FAST

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<2;

1 row updated.

SQL> 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 neglectable. 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 theme.

Recommend this posting: In case you found this post useful, why not recommend it to your Twitter Followers? Look how easy I’ve made that for you: Just click here and a Twitter Status Box will open with the (fully customizable) text you see when you hold the cursor above the link. Thank you :)

About these ads

, ,

  1. #1 by moses on July 9, 2009 - 23:36

    great teaching.

  2. #2 by arshad on September 9, 2009 - 06:37

    Excellent step by step explanation!!! I exactly understand the purpose and use of materialized views now.

    Thanks
    Arshad

  3. #3 by Arun on October 7, 2009 - 12:09

    Really Useful Stuff.
    Explained very Clearly with Examples..

    Great Job Man..
    Keep try to post Useful Stuffs.

  4. #4 by Ramesh Babu on February 11, 2010 - 21:27

    Very good explanation , helped me in understanding the materialized view refresh concepts easily.

    Thanks
    Ramesh

  5. #5 by Uwe Hesse on February 12, 2010 - 09:19

    Thank you guys for your positive feedback! It’s what keeps Bloggers blogging, I think :-)

  6. #6 by Tamas Kocsis on March 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 :)

    Thank You
    Tamas

  7. #7 by Uwe Hesse on March 3, 2010 - 08:39

    Tamas,
    thanks for the compliment! And I wish you a nice & rewarding learning experience. Hope you take a course from Oracle University :-)

  8. #8 by Mili on July 29, 2010 - 07:26

    Really nice …can u provide me the link on which u publish articles/tutorials.

  9. #9 by Muhammad Adnan on December 22, 2010 - 21:01

    Great method of teaching.

  10. #10 by Murali M on January 5, 2011 - 03:55

    Thanks, really a nice explanation.

    -Murali*

  11. #11 by Uwe Hesse on January 5, 2011 - 17:55

    You’re welcome :-)

  12. #12 by deepssharma on January 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.
    Thanks!

  13. #13 by Vikas on March 13, 2011 - 03:12

    Nice article. Thoroughly explained with examples. Thanks for publishing this article.

  14. #14 by Uwe Hesse on March 14, 2011 - 16:49

    Vikas,
    you’re welcome :-)
    Thanks for the nice feedback!

  15. #15 by Bora Yüret on April 1, 2011 - 08:38

    A very good teaching style, thanks for the tutorial.

    Bora Yüret

  16. #16 by Uwe Hesse on April 1, 2011 - 08:54

    Bora,
    you’re welcome :-)

  17. #17 by Sridhar on May 17, 2011 - 12:35

    Hi,

    Explanation was really Excellent
    Can please give me some examples on how to implement Application Contexts in Oracle.

    thanks,
    Sridhar

  18. #18 by Uwe Hesse on May 18, 2011 - 13:08

    Sridhar,
    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. #19 by Md. Rajibul Islam on May 25, 2011 - 13:09

    Your teaching is really helpful for me and I understand the topic clearly. Many many thanks to you.

  20. #20 by Uwe Hesse on May 26, 2011 - 08:10

    You’re welcome, I am happy if you find it helpful :-)

  21. #21 by Krishna Kumar on May 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. #22 by Uwe Hesse on May 30, 2011 - 08:51

    Thank you for the nice feedback, Krishna!

  23. #23 by Ezekiel Filane on June 2, 2011 - 11:47

    Excellent! 100%….ilike :-)

  24. #24 by Uwe Hesse on June 3, 2011 - 08:52

    Ezekiel, you’re welcome :-)

  25. #25 by totalz on July 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. #26 by Uwe Hesse on July 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. #27 by Jeannie Brown on July 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. #28 by Uwe Hesse on July 15, 2011 - 08:32

    Yes, it will stay the same as before until the refresh.

  29. #29 by G SElvam on 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. #30 by Uwe Hesse on 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

    http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0

  31. #31 by neerajvasudeva on December 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. #32 by Uwe Hesse on December 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. #33 by C Franklin on January 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. #34 by C Franklin on January 11, 2012 - 16:58

    Sorry… didn’t press “Notify me…”

  35. #35 by Uwe Hesse on January 12, 2012 - 12:18

    C Franklin,
    I am not sure I understood your issue. Why not just do a create table as select * from MV for the desired schema?

  36. #36 by C Franklin on January 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. #37 by vijaymukthavijay on February 27, 2012 - 06:54

    SIr,
    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. #38 by Ajinkya K. Soitkar on 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. #39 by Dharmendra on April 19, 2012 - 07:57

    Thanks
    I learn lot of things

  40. #40 by Dharmendra on April 19, 2012 - 07:58

    Can we refresh our mv using trigger

  41. #41 by Uwe Hesse on 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. #42 by Uwe Hesse on 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. #43 by yugen on May 7, 2012 - 06:29

    great one sir

  44. #44 by umesh chandra seth on June 4, 2012 - 10:54

    thanks sir .its really very easy to understand..

  45. #45 by Uwe Hesse on June 4, 2012 - 15:42

    You’re welcome & thanks for leaving this nice feedback :)

  46. #46 by Vladimiras on June 13, 2012 - 07:41

    Hello,

    thank you very much for interesting information.
    I’d like to ask — can materialized view (as normal table) will be created with primary key?

    Thanks.

    Vladimiras

  47. #47 by umesh chandra seth on June 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.
    thanks

  48. #48 by Uwe Hesse on June 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:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6002.htm#i2063793

  49. #49 by Uwe Hesse on June 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. #50 by umesh chandra seth on June 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?
    thanks!

  51. #51 by Uwe Hesse on June 14, 2012 - 12:01

    When you delete ALL the rows from the tables, it would be much faster to truncate them:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10007.htm#SQLRF01707

  52. #52 by Saleem Qureshi on July 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.

    Regard

    Saleem

  53. #53 by karthikeyan on August 8, 2012 - 11:04

    amazing……., good explanation on step by step process.

  54. #54 by oracle dba architecture on August 15, 2012 - 22:36

    Now i came to very clearly what Materialized View is….
    Thanks friend for your material

  55. #55 by Bhaskar on 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.

    Please explain.

  56. #56 by abdul_ora on August 17, 2012 - 08:08

    O.M.G really you gave me assets like knowledge. once again thank you thank you v. much :)

  57. #57 by karthik on August 21, 2012 - 05:51

    very nice

  58. #58 by Uwe Hesse on 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. #59 by Stavan Shah on 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. #60 by anirban sengupta(calcutta)(india) on October 5, 2012 - 18:51

    sir your posting is unparalleled and much easy to get into the inner concepts.

  61. #61 by Uwe Hesse on October 9, 2012 - 21:30

    Thanks for the nice feedback!

  62. #62 by Taimur khan on December 18, 2012 - 14:18

    sir , a really helpful post on Materialized Views and such an easy and detailed topic. very helpful

  63. #63 by Uwe Hesse on December 19, 2012 - 09:38

    Taimur khan, you’re welcome :)

  64. #64 by pnkj on December 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. #65 by Uwe Hesse on January 6, 2013 - 11:19

    pnkj, I’m glad you have found it helpful :)

  66. #66 by Dheeraj on January 11, 2013 - 12:13

    Hello Sir,
    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. #67 by Uwe Hesse on January 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. #68 by etched laser on January 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. #69 by Naren on February 6, 2013 - 16:36

    Its just great…

  70. #70 by Sudipta on March 8, 2013 - 11:02

    realy nice metarial

  71. #71 by Uwe Hesse on March 14, 2013 - 16:43

    Sudipta, you’re welcome :-)

  72. #72 by Khudadad on March 26, 2013 - 11:02

    Nice & perfect.

  73. #73 by Uwe Hesse on April 2, 2013 - 07:44

    Khudadad, you’re welcome :-)

  74. #74 by Ramesh on April 14, 2013 - 18:10

    Hi Sir,

    Really nice one. Presentation and concept is very good sir.Without reading second time i understood the concept.Thanks a lot.

    Thanks
    Ramesh D

  75. #75 by Uwe Hesse on April 16, 2013 - 08:38

    Thank YOU, Ramesh, for taking the time to leave such a nice feedback – very much appreciated :)

  76. #76 by sdfsdfsdfds on July 9, 2013 - 21:25

    Excellent tutorial thanks.

  77. #77 by subramanyam on July 15, 2013 - 11:51

    thq sir i easily understood this concept

  78. #78 by Raju on July 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. #79 by Waqas on July 31, 2013 - 03:26

    A Comprehensive Article! Thanks

  80. #80 by Uwe Hesse on August 13, 2013 - 09:45

    Thank you guys for taking the time to leave such a nice feedback! Very much appreciated :)

  81. #81 by sudhakar amineni on August 13, 2013 - 14:26

    Really Thankfull,

    Its Great Explanation…..step by step.

  82. #82 by suman on 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. #83 by Uwe Hesse on 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. #84 by suman on August 30, 2013 - 17:45

    how to calculate the value of PCTFREE parameter of a table ?

  85. #85 by Uwe Hesse on September 4, 2013 - 08:44

    Suman, your question is not much related to the posting, is it?
    However:
    As a guideline for setting PCTFREE, take this formular
    PCTFREE = 100 * UPD / (average row length)
    where
    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. #86 by suman on September 4, 2013 - 18:35

    thank you sir.

  87. #87 by RAJ KUMAR on October 11, 2013 - 12:10

    very understandable and amazing explanation…… one can very easily understand the concepts.

    Creative way of explanation.

  88. #88 by varun on April 9, 2014 - 08:50

    thank you so much sir……excellent way to understand the topic….

  89. #89 by Uwe Hesse on April 9, 2014 - 13:50

    Thank YOU for stopping by and taking the time to leave a nice comment :-)

  90. #90 by NT on May 11, 2014 - 00:29

    Excellent explanation with example.

  91. #91 by santoshi on July 7, 2014 - 06:51

    really impressive explanation,,Thank you

  92. #92 by srinivas on July 15, 2014 - 05:08

    First of all Thanks for your brief explanation. I hope you will be post more concepts

  93. #93 by Rajkumar on 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. #94 by Dulcie on 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!

  1. Blogroll Report 03/07/2009 – 10/07/2009 « Coskan’s Approach to Oracle
  2. Posting about Materialized Views got published in OU EMEA Newsletter! « The Oracle Instructor
  3. Merry Christmas and a Happy New Year! « The Oracle Instructor
  4. Materialized Views Introduction « Little Bit Technical…!!!
  5. Merry Christmas & A Happy New Year 2012! « The Oracle Instructor
  6. Merry Christmas & A Happy New Year 2012! | Oracle Administrators Blog - by Aman Sood
  7. Oracle Technology Software Index
  8. Materialized Views & Partition Change Tracking « The Oracle Instructor
  9. Top 10 postings in 2012 « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,754 other followers

%d bloggers like this: