11

I have a very complex Oracle view based on other materialized views, regular views as well as some tables (I can't "fast refresh" it). Most of the time, existing records in this view are based on a date and are "stable", with new record sets having new dates.

Occasionally, I receive back-dates. I know what those are and how to deal with them if I were maintaining a table, but I would like to keep this a "view". A complete refresh would take around 30 minutes, but it only takes 25 seconds for any given date.

Can I specify that only a portion of a materialized view should be updated (i.e. the affected dates)?

Do I have to scrap the view and use a table and a procedure to populate or refresh a given date in that table?

Galghamon
  • 2,012
  • 18
  • 27

4 Answers4

8

Partition by date as in answer 3 (skaffman).

You could just do the refresh of a normal mv(table_refreshed below) and than use the exchange keyword i.e.

ALTER TABLE all_partitions
  EXCHANGE PARTITION to_calculate
  WITH TABLE table_refreshed
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Conrad
  • 81
  • 1
  • 2
3

After more reading and judging by the lack of answers to this question, I come come to the conclusion that it is not possible to refresh a single partition of a materialized view.

If you can give a syntax example that proves otherwise, I will happily mark your answer the accepted one.

To others who might find this questions useful in the future: you might also want to know that in Oracle 10g, refreshing a partition (or any mview) will cause Oracle to issue DELETE, followed by INSERT.

If this is giving you performance problems (like me), there is an option to use atomic_refresh => false, which will TRUNCATE, then INSERT /*+APPEND*/.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Galghamon
  • 2,012
  • 18
  • 27
2

I have been able to refresh a single partition of a materialized view with partition change tracking.

It seems to require that the view is created with REFRESH FAST WITH ROWID option and DBMS_MVIEW.REFRESH is called with 'P' method.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Visitor
  • 21
  • 1
1

You can partition materialized views just as you can with normal tables. Partition your mview by date, and then you can refresh only the required partition.

skaffman
  • 398,947
  • 96
  • 818
  • 769
  • 2
    Just bear in mind that Partitioning is a chargeable extra to the Enterprise Edition license. So make sure you have licensed it before going into production. – APC Nov 23 '09 at 14:29
  • 1
    Are you referring to partition change tracking? It seems like a long shot that the OP would be able to leverage that if the MV query is as complex as stated. – David Aldridge Nov 23 '09 at 14:37
  • Ok, but how do I actually refresh just the one partition? I can't seem to find any syntax examples of this... – Galghamon Nov 23 '09 at 15:31