I can only tell you what is materialized view coz I havent used dimensions. MV is synonymous to snapshots, for example I have table emp and I create a mv mv_emp for it. In the instance of creation I populate the MV by including the clause BUILD IMMEDIATE (you can use DEFERRED to populate later), after populate the table it has same number of rows as EMP
If EMP has 14 rows my MVEMP will have 14 rows, MV is a snapshot of EMP in that instance, if during several days I update and insert and delete EMP these changes wont be reflected in MV because MV is a snapshot of EMP several days ago, if I want to refresh MV_EMP so it has same number of rows as EMP I can do it using several ways
1. Manually executing a package DBMS_MVIEW.REFRESH(TABLE_NAME)
2. Schedule the DBMS_MVIEW with DBMS_JOB
3. When creating MV_EMP specify clause START WITH... NEXT date, this is kind of like option 2, if I use this clause Oracle will automatically schedule a job with DBMS_JOB
Above is how you refresh the MV_EMP, now there are two mode of refresh, COMPLETE or FAST, complete as it's name suggest is like Oracle rebuilds the whole MV_EMP from scratch so it takes longer. If you want to use FAST option then you need to supply and create a materialized view log for this MV_EMP so all the changes occured in EMP will be recorded in this materialized view log. There are limitations using FAST refresh, if your subquery in materialzed view is a complex query (i.e contains WHERE clause) then you can only refresh the MV with complete option.
Anyway for detailed explanation check Oracle doco, datawarehouse guide and Oracle Replication
may be one disadvantage for some shops is that you need more disk space (not a problem in the shops I am now) because materialized views are physical segments, even they are called views they are really tables and they use space in the disk
Originally posted by pando may be one disadvantage for some shops is that you need more disk space (not a problem in the shops I am now) because materialized views are physical segments, even they are called views they are really tables and they use space in the disk
Moreover, their logs PCTFREE default value is 60 :-)
My fundas seems shaken a bit!!!
I tried using MV's few weeks back..and I found that even if i geive "BUILD IMMEDIATE " to it,it never updated the MV with the latest inserts in the table from which the MV has been created.....But a normal view did....
So we opted to use normal view rather than MV's...
Can any1 pls specify the xact diff between a normal view and MV....and it seems I did something really terible with the MV's created.
Thanx a lot....