Materialized views or Views??? confused...

[tolearnalot]

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

[slimdave]

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?

[tamilselvan]

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

[tolearnalot]

Thank you all for your answers. I am going with Simple Views.