0

Is the computational cost of updating a stored procedure materialized view, in Oracle, based on the query execution or the result set? More specifically, does Oracle store the results of the query in such a way that contributes significantly to the time required to refresh the view?

Of course, queries which take very long to execute as well as incredibly large or small result sets make this impossible to answer ubiquitously.

The question is more about how the view actually stores the result set (in memory, on disk) so I can think about how frequently to rebuild materialized views.

Sean Connolly
  • 5,692
  • 7
  • 37
  • 74
  • what do you mean, rebuild stored procedures? – Kirill Leontev Apr 25 '13 at 17:24
  • @beherenow sorry about that, I meant `materialized view` but had stored procedures on the brain I guess. I've rewritten the question, thanks!.. though I'm still not sure 'refresh' or 'rebuild' are the correct words. – Sean Connolly Apr 25 '13 at 17:31

2 Answers2

2

Materialized view is basically a table combined with an algorithm to update it.

01:37:23 HR@sandbox> create materialized view mv_dual as select dummy from dual;

Materialized view created.

Elapsed: 00:00:00.52
01:37:56 HR@sandbox> select object_name, object_type from user_objects where object_name = 'MV_DUAL';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
MV_DUAL         TABLE
MV_DUAL         MATERIALIZED VIEW

Elapsed: 00:00:00.01

You can also create materialized views on prebuilt tables.

If we talk about refresh - there are two options: fast refresh and complete refresh.

Complete refresh just re-executes MV query, while fast refresh performs incremental updates.

http://docs.oracle.com/cd/E16338_01/server.112/e10706/repmview.htm#i29858

Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49
1

there are two types of mviews

Complete refresh mview - the entier mview will be rebuild every refresh. similar to delete and insert (notice: if you specify atomic = F or have version < 9 it will be truncate / insert append).

Fast refresh mview - oracle will create a table to store incremental changes. when refreshing, the changes stored in the side table will be applied to the mview.

fast refresh is faster on refresh but slows down dml operations on the base table.

when you consider your refresh strategy you should consider how much changes are applied to the base table and how often you need to refresh the mview.

haki
  • 9,389
  • 15
  • 62
  • 110
  • so @haki, if I would refresh the materialized view once a day, would be more adviseable to do the complete refresh? – chip Mar 22 '16 at 01:00