Posts Tagged ‘materialized views’

Suppose I have an 8 million row master table sitting in a database next to my desk in sunny Sydney. I stick a materialized view log on it, and create a fast refreshable materialized view on that master table in a database sitting thousands of kilometres away somewhere. Imagine further that the link between my desk and this remote is not exactly brilliant: copying the original 8 million rows across when creating the materialized view in the first place would have taken several hours. Let us say, therefore that it’s not a copying exercise I would be keen to do again, if I could help it!

Finally, imagine that on attempting a fast refresh on the remote materialized view you see the dreaded error message:

  ERROR at line 1:
  ORA-12034: materialized view log on "LOCALDB"."BIGTABLE" younger than last refresh

There are several and varied reasons you might get that particular error message: if you drop and re-create the local materialized view log as part of a migration of that particular database from Windows to Linux, for example; so would purging the log. Whatever the reason, the usual advice at this point is to simply drop the remote materialized view and re-create it from scratch. But in this case, that course of action would result in a multi-hour data-copying-over-a-flakey-network nightmare writ large, so it’s not advice I would particularly want to follow.

Happily, there is a way around the drama that doesn’t involve multi-Gigabyte network copying.

1. Create a table as a copy of the current materialized view in the remote database. Since the copying of a table is entirely taking place within the remote database, no network traffic is involved:

  create table mvbackup as select * from actual_mv;

2. Drop the existing materialized view in the remote database:

  drop materialized view actual_mv;
  drop table actual_mv purge;

The drop table command is in there, just in case the materialized view was originally created on a prebuilt table. If it wasn’t, then the command will produce an error because no such table exists. Just ignore it if that happens, then.

3. Create a new table with the ‘right’ name as a copy of the backup taken at step 1:

  create table actual_mv as select * from mvbackup;

4. Create a new materialized view on this newly created table, using the ‘on prebuilt table’ clause:

  create materialized view actual_mv
  on prebuilt table
  refresh fast on demand
  as select * from master_table@australian_db

Now, at this point, there is a reference to the remote database -and there’s no doubt that the remote database goes across that link to check for the existence of the master table and so on. But the ‘on prebuilt table’ clause means that it doesn’t fetch any data across the link, because it knows that all its required data is stored locally. The net result is that the new materialized view is created in less than a couple of seconds, even with that reference to the remote database.

Of course, this technique only really works because I happen to know for certain that the master table is not undergoing any DML as I’m doing all this mucking about. It also helps that I know for a fact that no DML has taken place on the master table since the last refresh of the remote materialized view. I checked that was indeed the case with this simple query:

  select * from master_table@australian_db
  minus
  select * from mvbackup;

Had anything been returned by that query, I would have inserted it into my backup table before starting step 2:

  insert into mvbackup select * from master_table@australian_db where primary_key_col in
  (select primary_key_col from master_table@australian_db
  minus
  select primary_key_col from mvbackup);

That would have made the MV built at step 4 as up-to-date as its master, and would therefore have involved fetching data across the wire, slowly. However, at least it would only have been a tiny subset of the total data -a matter of a few tens of minutes at most, and not the multiple hours required to fetch the entire data set as would have been the case if I’d dropped and re-created the materialized view in its entirety.

We have a multi-million row master table in one country and a materialized view built on that table in another country, refreshing fast over a network link that’s not very good. When you have that sort of situation, performing a complete refresh of the materialized view is not something you want to have to do very often! In our case, a complete refresh would take days, always assuming it would ever actually finish without bombing out with network-related problems half-way through.

Today, when I tried to perform a fast refresh of the view, I got an ORA-12034 error message (the materialized view log is younger than the last refresh of the materialized view itself). The message is suggesting that if a fast refresh was allowed, the materialized view might end up missing some records. However, I was able to assure myself that that couldn’t possibly be true by simply counting the records in the master table and MV (they were identical) and also knowing precisely that only one process, which wasn’t running, is allowed to insert records into the master table (because I wrote the code in question!)

When you get a 12034 error, the books tell you that the only cure is to perform a complete refresh. But, as I’ve explained, that simply isn’t a viable option for us. So here’s the workaround:

create table hjr_temp_tab as select * from my_materialized_view;
drop materialized view my_materialized_view;
alter table hjr_temp_tab rename to my_materialized_view;

create materialized view my_materialized_view
on prebuilt table
refresh fast
on demand
as select  * from master_table@remote_database;

Since I knew the MV had the right number of records, I simply copied it to a regular ‘backup’ table. I then droppped the MV itself, renamed the backup table so that it acquired the same name as the old MV, then created a new MV on a prebuilt table. That last bit is the key: it means the many millions of rows that a complete refresh would have to pull over the database link from the master table will be found already housed in a local table. The creation of the materialized view (which, effectively, ‘captures’ the existing table and turns it into a materialized view) then takes place in mere seconds. If my ‘backup table’ had not had the right number of records, I would have brought it up-to-date with an insert into my_table select * from (select * from master@remote_db minus select * from my_table). That is, work out what records were missing with a MINUS test and then add only them to the table before turning it into the materialized view. That would have been quite slow (especially over our network links!), but nowhere near as slow as trying to bring the entire set of records over the wire!

Once the new MV exists, you simply do:

begin 
dbms_mview.refresh('my_materialized_view');
end;

…and this time, there are no ORA-12034s. So, at the cost of quite a bit of disk space (temporarily, my many millions of rows exist both in the original, broken MV and in the ‘backup table’), I am able to re-initiate fast refreshes without having to perform a complete refresh first: pretty useful to us, that’s for sure!