0

I'm new to oracle and materialized views. I have created a view for data that was producing a big bottleneck in our application. For reducing the complexity of the virtual view, the data were divided in respective part virtual views reflecting some business domain. I.e. the main virtual view which I want to use for the materialized view contains data joined from the part virtual views.

My question is, if I can create a materialized view from the main virtual view. Forther, II would like the data updated by each commit.

create materialized view log on main_view;
create materialized view main_view_mv refresh fast on commit
as select col_1 from main_view;
commit;

Is it possible to create the materialized view? Are these commands OK to refresh the materialized view?

Thnaks for the any hint.

Rubén
  • 427
  • 1
  • 9
  • 23

1 Answers1

1

To answer your question: no, you can't create materialized view logs on a view - only on tables. The requirements for a FAST refreshable materialized view are described in this answer

Materialized views aren't a magic tool to make slow views execute fast. You'll probably have to examine why the main view is slow.

Community
  • 1
  • 1
HAL 9000
  • 3,877
  • 1
  • 23
  • 29
  • Hi Hal 9000, actually, the data is extremly normalized. Using any orm-library makes no-sense sinse the data is been prepared e.g. there are many information providers for one data unit (jpa entity) and this information needs to be merged before giving this back. It is a huge number of entities that we need to return by each search. Two options 1) eager loading (makes no sense despite the object tree is very flat) 2) lazy loading (makes just as well no sense since each object needs to be prepared as commented before but not only this) For this reason we have created the views. – Rubén Dec 12 '14 at 15:07
  • Ok, but when the view is actually only a merge, then why is it a "bottleneck". – HAL 9000 Dec 12 '14 at 15:25
  • I really don't know. It maybe because each time the view is been called the query (i.e. joins and transpositions etc) for the view is also executed? I reading right now a book about Oracle and materialized views. Maybe I'll find a solution for this problem. I hope so. – Rubén Dec 16 '14 at 16:45