Materialized View vs View

Question

May I know the difference for these two items?

Data in materialized view can be refresh but so as view when we use select statement. Why not just use view instead of materialized view?

Farhad Jabiyev · Accepted Answer · 2014-04-23T08:25:46.120

13

When you need performance on data that don't need to be up to date to the very second, materialized views are better, but your data will be older than in a standard view.

While creating Materialized view Oracle creates two objects, a table where the results are actually materialized and a materialized view that has all the metadata (the query, the attributes, etc.).

But while creating View Oracle creates only one object, which has all the metadata(the query, the attributes, etc.)

edited Apr 23 '14 at 08:25

answered Apr 23 '14 at 08:17

Farhad Jabiyev

26,014
8
72
98

My main table contains few millions of data. If I use Materialized View rather than regular view, the data will be duplicated x2? – 4 Leave Cover Apr 23 '14 at 08:19
@TanSiongZhe I don't know what you mean by x2, but of course it will save datas again unlike view. – Farhad Jabiyev Apr 23 '14 at 08:24
Sorry by x2 I mean times two. Lets say my database currently holds 6 million data, by using MV it will become 12 millions data? – 4 Leave Cover Apr 23 '14 at 08:26
@TanSiongZhe If the result of your query holds 6 millions data, then yes, it will create a new table which has same name with MV and holds 6 million data. – Farhad Jabiyev Apr 23 '14 at 08:29

score 2 · Answer 2 · answered Apr 23 '14 at 08:10

You use materialized views for performance reasons mainly.

According to the Oracle docs:

A materialized view is a replica of a target master from a single point in time.

A regular view loads data 'on demand' and can 'automatically' change when the underlying data changes.

Materialized View vs View

2 Answers2