DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Materialized views or Views??? confused...

  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Materialized views or Views??? confused...

    Dear all-

    I have a question related to designing. Please consider the following scenario -

    Have some master tables for each transaction type-

    master_table_1 - col11 number primary key, col12 number, col13 number

    master_table_2 - col21 number primary key, col22 number, col23 number

    master_table_3 - col31 number primary key, col32 number, col33 number

    and have a transaction table that records every transaction

    trans_table_1 - coltt1 number primary key, col11 number references master_table_1(col11), col21 number references master_table_2(col21), col31 number references master_table_3(col31)...

    In the above table, only one foreign key will be populated at a given time i.e. col11 or col21 or col31 based on the type of transaction and the rest are NULLs. Couple of thousands records will be added to this table every day. So, it grows really fast.. and also bigger within few months of time.

    We will have a front end web application developed in Java to access these tables. The application is designed in such a way that it requires 3 objects (or DB tables/views) for these 3 transaction types.

    Now my question is, what would be the best to way to do this, whether to have 3 materialized views created on these tables using joins or simple views as shown below

    vw1 - tran_table_1 joined master_table_1
    vw2 - tran_table_1 joined master_table_2
    vw3 - tran_table_1 joined master_table_3

    Will there be any difference if we use materialized views instead of normal views???

    Thank you very much in advance.

    Srini

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Materialized views and regular views are completely and totally different. A materialized view is a SELECT statement with a bunch of other metadata attached to a table that holds the result of the query, and which is refreshable through the MV mechanism. A view is just the SELECT statement, which is "merged" into whatever select statement it is included in.

    What problem are you trying to solve here?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    We will have a front end web application developed in Java to access these tables. The application is designed in such a way that it requires 3 objects (or DB tables/views) for these 3 transaction types.
    Either 3 views or simple join between trans table and one of the masters table based on certain conditons will solve the problem.
    You need to know more about how the JAVA is generating the code.

    You do not need MV unless you see perf issue.

    Tamil

  4. #4
    Join Date
    Oct 2006
    Posts
    3
    Thank you all for your answers. I am going with Simple Views.

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