Is it possible to partially refresh a materialized view in Oracle?

oracle data-warehouse partitioning materialized-views
Galghamon picture Galghamon · Nov 23, 2009 · Viewed 8.8k times · Source

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?

Answer

Conrad picture Conrad · Oct 22, 2012

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;

Related questions

Group by vs Partition by in Oracle

I am writing a query to fetch records from a Oracle warehouse. Its a simple Select Query with joins on few tables and i have few columns to be aggregated. Hence i end up using Groupby on rest of the …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More