Materalized View / Dimensions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Materalized View / Dimensions

  1. #1
    Join Date
    Aug 2001
    Posts
    134
    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...

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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 :-)





  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    60 is decent considering they are updated frequently though

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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)

  9. #9
    Join Date
    Jun 2001
    Posts
    316
    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....

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width