Materalized View / Dimensions

I have few questions regarding:

1. What is the Materialized View ; and its applications, Advantages and Dis-Advantages.

2. Whare are Dimensions and its applications, Advantages and Dis-Advantages.

Thanx...

Materialized View are like aggregated table they are used in Data Warehousing, Advantage is it improves the performance.

Dimensions are part of Data Warehousing as well, Advantage you must have it disadvantage you can not have a Warehouse without it you have to use CUBE technology if not using Dimintions

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

Little bit more:
because MVs (or snapshorts) are "secondary" and, as a rule, very updatable tables, you should be carifull when you will write pctused, pctfree parameters.

Quote:

---

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 :-)

60 is decent considering they are updated frequently though

Quote:

---

Originally posted by pando
60 is decent considering they are updated frequently though

---

Absolutely right.

(By the way, I have one table with PCTFREE 95)

Hi guys..
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....

you have to refresh it with dbms_mview, mv is static, it´s physical, it occupies disk space where as a view doesnt, it´s something logical, it depends on base tables.

Bundle of thanx
When i create materialized view
conn system/manager
create materialized view abc
build immediate
refresh complete
as select * from scott.emp;
It creates successfully, but not refresh when i use
begin
DBMS_MVIEW.REFRESH('ABC');
END;
OR
begin
DBMS_MVIEW.REFRESH('SCOTT.EMP');
END;
OR
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS;
END;
There is a package error of
SYS.DBMS_SNAPSHOT.........
Is there any need of scrip file or generate a packge with other parameters.

when should I use query_rewrite in MV?
Thanx Again

Quote:

---

Originally posted by helpme
Hi guys..
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....

---

Hey people again,
I'm back.

You can update all the mv after an update or insert into a table which is used for the MV.

So we make following:

CREATE MATERIALIZED VIEW MV_D_BASE2
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS

But for the fast refresh on demand there are any rules. You must look in the datawarehouse guide from Oracle.

So long
Thomas Schmidt