Not overlapping

Lets have a validity period in a table and a rule not to have overlapping periods. The correct solution is to have an another table having the concurrency lock and a combound trigger to handle the overlapping check. Or might there be an alternative way? A way that does not force us to rely that all code modifying the table remember to use the concurrency lock table before modifying the periods. Here it comes. A great abuse of the "bad hack" function based indexes. No need for the concurrency table or triggers.

drop table z cascade constraints purge;

create table z(z number(16) not null
             , validfrom number(4) not null
             , validtill number(4) not null
             , constraint fro2000 check (2000 < validfrom)
             , constraint til2050 check (validtill <= 2050)
             , constraint frotil check (validfrom <= validtill)
             );

create or replace function f(fro number,til number) 
return number 
deterministic
as
  n number(16);
begin
  n:=0;
  for i in fro..(til-1) loop
    n:= n + power(2,i-2001);
  end loop;
  return n;
end;
/


create unique index z1 on z (case when bitand(1,f(validfrom,validtill)) = 1 then z else null end); 

create unique index z2 on z (case when bitand(2,f(validfrom,validtill)) = 2 then z else null end); 

create unique index z3 on z (case when bitand(4,f(validfrom,validtill)) = 4 then z else null end); 

create unique index z4 on z (case when bitand(8,f(validfrom,validtill)) = 8 then z else null end); 

create unique index z5 on z (case when bitand(16,f(validfrom,validtill)) = 16 then z else null end); 

create unique index z6 on z (case when bitand(32,f(validfrom,validtill)) = 32 then z else null end); 

create unique index z7 on z (case when bitand(64,f(validfrom,validtill)) = 64 then z else null end); 

create unique index z8 on z (case when bitand(128,f(validfrom,validtill)) = 128 then z else null end); 

create unique index z9 on z (case when bitand(256,f(validfrom,validtill)) = 256 then z else null end); 

create unique index z10 on z (case when bitand(512,f(validfrom,validtill)) = 512 then z else null end); 

--You maybe got the idea. Left out indexes z11-z46 ... 

create unique index z47 on z (case when bitand(70368744177664,f(validfrom,validtill)) = 70368744177664 then z else null end); 

create unique index z48 on z (case when bitand(140737488355328,f(validfrom,validtill)) = 140737488355328 then z else null end); 

create unique index z49 on z (case when bitand(281474976710656,f(validfrom,validtill)) = 281474976710656 then z else null end); 

SQL> insert into z values(1,2001,2011);

1 row created.

SQL> insert into z values(1,2011,2011);

1 row created.

SQL> insert into z values(1,2010,2012);
insert into z values(1,2010,2012)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2049,2050);

1 row created.

SQL> insert into z values(2,2049,2050);
insert into z values(2,2049,2050)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z49) violated

SQL> insert into z values(2,2010,2012);

1 row created.

SQL> insert into z values(2,2001,2049);
insert into z values(2,2001,2049)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z10) violated

SQL> insert into z values(2,2014,2017);

1 row created.

Denormalize safely

For some reason there is a need to denormalize values from p table to c table.
How to ensure that denormalized values are the same that original values in p?

drop table c cascade constraints purge;

drop table p cascade constraints purge;

create table p(p_id number(10) primary key
             , p_name varchar2(200) not null);

create table c(c_id number(10) primary key
             , p_id constraint c_p_fk references p
             , c_value varchar2(200) not null);

The denormalization.

alter table c add (p_name varchar2(200) not null);

alter table p add unique (p_name,p_id);

alter table c add constraint c_p_2fk foreign key(p_name,p_id) references p(p_name,p_id);

Make the c_p_2fk deferrable if there is a need to update the denormalized values. To satisfy unindex:

create index c_p_2fk_idx on c(p_id,p_name);

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.

Only one (alternative 2)

10g friendly approach to store primary currencies. No virtual column used. Structure seems a lot like Tom Kyte suggested. Difference is that all country currencies are here populated in the same table and the materialized view replaced with a foreign key constraint.

create table country(country varchar2(2) constraint country_pk primary key
                   , primary_currency varchar(3) not null
                    )
;

create table currency(country references country
                    , currency varchar(3)
                    , constraint currency_pk primary key (currency,country)
                    )
;

alter table country
  add constraint primary_curr_in_country_curr
  foreign key(primary_currency,country)
  references currency(currency,country)
  deferrable initially deferred
;

Populating

insert into country (country,primary_currency) values ('US','USS');

insert into currency (country,currency) values ('US','USD');

insert into currency (country,currency) values ('US','USN');

insert into currency (country,currency) values ('US','USS');

commit;

insert into country (country,primary_currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','EUR');

insert into currency (country,currency) values ('FI','FIM');

commit;

Changing the default. Easier than before. Less code

update country set primary_currency='USD' where country='US';

commit;

select * from country;
country primary_currency
US      USD
FI      EUR

select * from currency;
country currency
FI      EUR
FI      FIM
US      USD
US      USN
US      USS

Testing the model:

update country set primary_currency='USD' where country='FI';

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found

insert into country (country, primary_currency) values ('SW','SEK');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.PRIMARY_CURR_IN_COUNTRY_CURR) violated -
parent key not found

There is only one default currency per country, should be obvious.

Be sure to run unindex on your production model.

Load using Java

Cary Millsap is writing good things about measuring when tuning performance. His paper Making friends "Optimizing the insert program" is talking how to insert 10000 rows using Java. There are presented only possibilities to insert using Statement in a loop or using PreparedStatement in a loop. Should there be a alternative way also presented? Avoid looping statements in Java and populate all rows just in single call to the database.

Timing results inserting 10000 rows:

Array
Executed in 0 min 0 s 313 ms.
Prepared
Executed in 0 min 2 s 985 ms.

ARRAY:

create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;

    private static void insertArray(Connection c, List elems) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into si " +
                        " select *" +
                        "    from table(?)");
        ops.setARRAY(1, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

Prepared:

    private static void insertPrepared(Connection c, List elems) 
            throws SQLException {
        PreparedStatement ps = c.prepareStatement("insert into si values(?,?)");
        Iterator i = elems.iterator();
        while ( i.hasNext()) 
        {
            Object[] o = i.next();
            ps.setInt(1, (Integer)o[0]);
            ps.setInt(2, (Integer)o[1]);
            ps.executeUpdate();
        }
        ps.close();
        
        c.commit();
    }

Here are some timings for other number of rows. It shows that if you are inserting 100-1000 rows this approach might be worth considering. Measure yourself. Be sure to have enough memory available for your Java.

ms      localhost      remote db 
rows    prepared array prepared array
10      94       140   93       141
100     125      141   125      156
1000    313      203   844      265
10000   1500     344   16297    453
100000  12063    1422  206210   2078
1000000 182063   java.lang.OutOfMemoryError: Java heap space  

insertintoselect.java

Only one

Last September Tom Kyte wrote a yet another good article in Oracle magazine The Trouble with Triggers. The point about triggers is very good, but the example and the "Correct Answer" I do not like. He is suggesting to model currencies to two separate tables. In my opinion the data model is entirely wrong if the same thing is modeled in many places. "Less code equals fewer bugs. Look for ways to write less code." Here is my approach to the problem without a materialized view and materialized view logs.

The problem itself is that
-a country must have a default currency
-there is only one default currency per country

create table country(country varchar2(2) constraint country_pk primary key);

create table currency(country references country
                    , currency varchar(3)
                    , is_primary varchar2(1) not null 
                                             check (is_primary in ('Y','N'))
                    , primary_country 
                            as (case when is_primary = 'Y' then country end) 
                               virtual 
                        constraint only_one_primary_u unique 
                    , constraint currency_pk primary key (currency,country)
                    )
;

alter table country
  add constraint must_have_at_least_one_primary
  foreign key(country)
  references currency(primary_country)
  deferrable initially deferred;

And that is it. One may see that there is used the "bad hack" behind only_one_primary_u. The unique constraint generates a function-based normal index.
The good thing about this is that constraints are visible in constraint list, not implemented only in a index or a materialized view.

select * 
  from user_constraints 
 where constraint_name 
    in ('ONLY_ONE_PRIMARY_U','MUST_HAVE_AT_LEAST_ONE_PRIMARY');

Populating

insert into country (country) values ('US');

insert into currency (country,currency,is_primary) values ('US','USD','N');

insert into currency (country,currency,is_primary) values ('US','USN','N');

insert into currency (country,currency,is_primary) values ('US','USS','Y');

commit;

insert into country (country) values ('FI');

insert into currency (country,currency,is_primary) values ('FI','EUR','Y');

insert into currency (country,currency,is_primary) values ('FI','FIM','N');

commit;

Changing the default

update currency set is_primary = 'N' where country = 'US' and currency = 'USS';

update currency set is_primary = 'Y' where country = 'US' and currency = 'USD';

commit;

select * from country;

COUNTRY
US
FI

select * from currency;

COUNTRY	CURRENCY	IS_PRIMARY	PRIMARY_COUNTRY
US	USD		Y		US
US	USN		N	
US	USS		N	
FI	EUR		Y		FI
FI	FIM		N	

A country must have a default currency

insert into country (country) values ('NO');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

insert into country (country) values ('SW');

insert into currency (country,currency,is_primary) values ('SW','EUR','N');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

There is only one default currency per country

insert into currency (country,currency,is_primary) values ('FI','USD','Y');

ORA-00001: unique constraint (RAFU.ONLY_ONE_PRIMARY_U) violated

Multi table insert atomic?

Reading SQL and Relational Theory: How to Write Accurate SQL Code. Talking about constraints and data modifications. There should be able to update several tables in one atomic operation. Oracle multi table insert could be something to that direction, but no atomicy. Bug still open from version not any more supported.

SQL> create table a (a_id number(1) primary key
  2                , b_id number(1) not null);

Table created.

SQL> create table b (b_id number(1) primary key
  2                , a_id not null constraint b_a_fk references a );

Table created.

SQL> alter table a add
  2    constraint a_b_fk foreign key (b_id) references b
  3      deferrable initially deferred;

Table altered.

SQL> insert all into b values (x,x)
  2  into a values (x,x)
  3  select 0 x
  4    from dual;
insert all into b values (x,x)
*
ERROR at line 1:
ORA-02291: integrity constraint (RAFU.B_A_FK) violated - parent key not found


SQL> insert all into a values (x,x)
  2  into b values (x,x)
  3  select 1 x
  4    from dual;

2 rows created.

SQL> insert into a values (2,2);

1 row created.

SQL> insert into b values (2,2);

1 row created.

SQL> select * from a;

      A_ID       B_ID
---------- ----------
         1          1
         2          2

SQL> rollback;

Rollback complete.