If a test-case paints a thousand words why can’t …
… more people get into the habit of producing them.
Addendum (5th April 2006)
I had a wonderful experience recently with Vikas Atrey of i-flex Solutions who had emailed me about a problem he was having with materialized views, and one particular view that was causing an error on a fast refresh. We exchanged a couple of emails with descriptions, observations and suggestions for things to check but everything he said seemed to indicate that there should be no problem. Then all went quiet for about a month until I got a note that said:
Fast refresh doesn’t work if we swap the values in a column of a unique key.
This, of course, sounds completely bizarre – but to me it had that strange degree of impossible surrealism that meant that there really might be some reason why this very specific case would cause a fast refresh to fail. (Sometimes you see things which are so ridiculous you feel they just have to be true). Before reading on, just take a moment to think about this statement and see if you can recognize why it might be true. My first interpretation of it was completely wrong.
My normal response to this suggestion would have been to build a test case (and if I had, it wouldn’t have behaved as Vikas had suggested) - luckily I didn’t need to, because the next line in the email said:
Here’s a complete test case.
Oh joy, oh bliss, oh frabjous day – someone doing the right thing.
Not only was there a complete test case – it was a short, absolutely minimalist, test case. Here it is (with table and column names changed).
drop table t1;
drop materialized view t1_mv ;
create table t1 (
create unique index t1_u1 on t1 (n1, v1, n2);
insert into t1 values( 1, 'x' , 1) ;
insert into t1 values( 1, 'x' , 2) ;
create materialized view log on t1
with sequence, rowid including new values ;
create materialized view t1_mv
refresh fast on demand
select * from t1 ;
create unique index t1_mv_u1 on t1_mv(n1,v1,n2) ;
n2 = decode(n2, 1, 2, 2, 1)
n2 in (1 , 2)
exec dbms_mview.refresh('t1_mv' , 'f') ;
exec dbms_mview.refresh('t1_mv' , 'c') ;
See the update statement – it updates two rows by swapping the values in one of the key columns – just like the email said, and sure enough, when I ran the test case (after checking that it wasn’t going to do anything disastrous to my database), the fast refresh failed, the complete refresh worked.
But the error message was very informative, and showed me something that had either not been mentioned, or which I had simply not recognized, in our previous exchange of email. This is what the error was:
BEGIN dbms_mview.refresh('t1_mv' , 'f') ; END;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (TEST_USER.T1_MV_U1) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Right on target! When I first read the email, I had thought the unique key being discussed was on the base table, but the unique constraint with the problem was on the materialized view. Remember my comment that if I had built my own test case, it would not have failed – that’s because I hadn’t realized that the unique constraint had to be on the materialized view, I would have built a table with a unique constraint and then said “where’s the problem”.
This is the guilty line:
create unique index t1_mv_u1 on t1_mv(n1,v1,n2) ;
Change this to:
alter table t1_mv add constraint t1_mv_u1 unique (n1,v1,n2) deferrable;
Note particularly the deferrable – when we do our fast refresh there is a moment where we have to have a duplicate row in the table – either we change the 1 to a 2 first, or we change the 2 to a 1 first – the refresh updates one row at a time, it doesn’t do an array update. By making the constraint deferrable we get a non-unique index protecting the constraint, and Oracle only re-checks the validity of the constraint on the commit, not for each update statement. I believe there is something in the data warehouse manuals about making unique (and primary key) constraints on materialized views deferrable for exactly this reason.
When you’ve got a problem, stripping it back to the simplest scripted test case is often enough to make you understand what the problem is and how to fix it. But even if you can’t spot the problem, you’ve got a rock-solid, unambiguous statement of the problem to pass on to someone else. Maybe the problem will be obvious to them the moment they read, or run, the script.
In this case, we started with words, discussing the possibilities, and got nowhere after a couple of email messages (possibly because a detail was omitted, or misinterpreted, in the course of exchanging words). With such an excellent test script in front of me, though, I spotted the problem in less than one minute.
A test case is worth a thousand words – (which means it has to be quite short, to be less than 1,000 words).
I got an email in response to the note asking why it was only the constraint on the materialized view that caused a problem on this update, whilst the update to the base table did not. It’s a good question, and one which I should probably have addressed in the note.
When I update the base table with an array update (my single update statement does both rows) Oracle has a special constraint checking strategy that checks for uniqueness only after the entire statement has run to completion. (I recall that in some early version of Oracle – like 6.0.27 – this didn’t happen, and you could get “duplicate key” errors with statement like “update t1 set unique_column = unique_column + 1” if some critical, overlapping, values happened to be stored in ascending order in the table).
But the materialized view fast refresh is made possible by a row-level trigger that logs changes to each row in the base table, and the trigger fires one for each row that I update, producing a log entry for each row.
The fast refresh then operates as two separate update statements, and the constraint checking is done on each statement, unless I have declared the constraint to be deferrable and the internal code has made a call to “set constraint deferred”. If the constraint is deferred, the check is postponed to the end of the transaction (allowing both update statements to complete successfully), or until the explicitly makes the constraint immediate.