Only one (is_current)

Kimball writes in The Date Warehouse Toolkit about slowly changing dimension type 6. Seen such structures with marked current values in a separate attribute. Like in wikipedia. If the dimension is storing transaction time as durations then the current indicator column might as well be a virtual column in 11g, evaluated to Y if the end day is set to end_date value. No need to update the current indicator column when populating new values for a dimension.

There should be at most one current indicator = Y row for each supplier. To satisfy this requirement it is possible to create a unique constraint to a virtual column. Actually I would replace the Y value with the supplier_key if the end of days is set in the row. And put the unique key to that column as show earlier. Adding such a column to an existing model might be problematic because the ETL or mainetenance software might populate new rows before updating the old ones. Should there exist atomic multi table merge? With 11g and virtual columns this is quite easy to overcome by setting the newly created unique constraint deferrable initially deferred.

But how about 10.2 without virtual columns. Lets dig into the only one, insert first and update then problem with an example without durations.

drop table di purge;

drop materialized view di_mv;

create table di(di_id number(4) primary key
               , sour number(4) not null
               , val number(4) not null
               , is_current number(1) not null);

Each sour should have only one current. This can be acheaved by creating a bad hack function based index.

create unique index d_only_one_u on di(case when is_current = 1 then sour end);

insert into di (di_id,sour,val,is_current) values (1,1,0,0);

insert into di (di_id,sour,val,is_current) values (2,1,1,1);

insert into di (di_id,sour,val,is_current) values (3,2,2,1);

Problematic ETL prosess might want to populate new values first and update the current status at the end of transaction.

insert into di (di_id,sour,val,is_current) values (4,2,3,1);
insert into di (di_id,sour,val,is_current) values (4,2,3,1)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.D_ONLY_ONE_U) violated

Unique index is checked immediately and now row is allowed to be inserted.
Glue on commit refreshable materialized view to the model.

drop index d_only_one_u;

create materialized view log on di with rowid; 

create materialized view di_mv refresh on commit as
select sour,is_current, rowid drid
  from di
 where is_current = 1
;

alter table di_mv
add constraint sour_u unique (sour);

insert into di (di_id,sour,val,is_current) values (5,2,3,1);

commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (RAFU.SOUR_U) violated

And it behaves like an deferrable initially deferred unique constraint. Uniquenes check is done at the end of a transaction.

insert into di (di_id,sour,val,is_current) values (6,2,3,1);

update di set is_current = 0 where di_id = 3;

commit;

Ok. The downside is that di_mv is using space. May be worth using, if your data population is beeing coded by humans.