Tom,

We have an OLTP environment where we store calculation results. The 'data model' is (simplified) as follows:

SQL> desc DEVDAT.CALCFIGURESUB -- VALUES table
Name Null? Type
----------------------------------------- -------- -------------
CALCFIGIK NOT NULL NUMBER(10)
CALCFIGURE NUMBER(21,8)
CALCFIGSUBDATIK NOT NULL NUMBER(10)

SQL> desc DEVDAT.CALCFIGSUBDATA -- DATA table
Name Null? Type
----------------------------------------- -------- ---------------
CALCFIGSUBDATIK NOT NULL NUMBER(10)
LINEIK NOT NULL NUMBER(10)
CALCITEMTYPE NOT NULL NUMBER(5)
SEGIK NOT NULL NUMBER(10)
TRANSSEG NOT NULL NUMBER(10)
COSTIK NOT NULL NUMBER(5)
PTRIK NOT NULL NUMBER(5)
SIGN NOT NULL NUMBER(5)
CALCRULE NOT NULL VARCHAR2(1000)
REFLINE NOT NULL NUMBER(10)
SORT NOT NULL NUMBER(5)
ROUNDVAL NOT NULL NUMBER(13,9)
ROUNDTRUNC NOT NULL NUMBER(5)
FILENAME NOT NULL VARCHAR2(255)
REFKEY NOT NULL VARCHAR2(12)
<< 17 other NUMBER columns>>
PERFSPLITNAME NOT NULL VARCHAR2(50)
<< 13 other NUMBER columns>>

The column CALCFIGSUBDATIK in CALCFIGSUBDATA (from now on the DATA table) is the primary key of the table and the same column in CALCFIGURESUB (the VALUES table) is a foreign key that refers the DATA table. The two tables used to be one (via a simple join) but the high number of 'redundant' values in the DATA part of the tables did we try to 'normalize' all these columns in to a separate table and then try to reuse the values in the DATA table for each new insert into the VALUES table.

In an ideal world would we like to enforce uniqueness of all the columns in the DATA table (all except for the primary key) - but we have a problem as the VALUES (and DATA) table is filled (calculated) by a high number of parallel calculation servers. The problem we have seen very often is the following:

Time Process A Process B
0 Finds a new record R
that has to be inserted
into the DATA table.
1 Finds that the same record R
should be inserted into the
DATA table.
2 Inserts record R into
the DATA table.
3 Inserts record R into
the DATA table.
4 Commits
5 Commits

Record R is not visible for process B before time=4 - so the two processes will insert a 'redundant' record into the DATA table (with a new primary key). We have tried a number of things:

1) Add a unique constraint to the columns in the DATA table. Process B would then fail with ORA-00001 and process B could go back to before time=1. This will off cause not work for more than 32(!) columns in a unique constraint - and as you probably can see have we something like 40+ columns!

2) Make an autonom transaction that locks the full DATA table (and the VALUES since foreign keys can not be validated!) - The autonom transaction would then start around time=2 above and then verify if R is now in the table. If not will this be inserted and committed. Process B was forced to wait until time=4 before it could execute the autonom transaction. This serialized our access to DATA and VALUES tables - and was abandoned for that specific reason.

3) Allow 'duplicates' in the DATA table... (As we have elected now) - But this might gives us up to the number of calculation servers of 'redundant' rows of the R record.

We have looked into compressed tables (where we join the DATA and VALUES tables) - and we see a considerable space savings (if we do a CTAS) but all succeeding inserts (via OCI and array inserts) makes the table explode in size (some of our installations have more than 85000000 rows in the VALUE table).

Although we think we have exhausted our possibilities would we like to hear if you see an option that we have overlooked that can be used in an OLTP environment and does not have the characteristics of 3) above?

Sincerely,

Lars Stampe Villadsen