Options for handling a column that is an instance count

Question

Consider the following table

CREATE TABLE example (
  example_id     NUMBER,       -- PK
  parent_ref     NUMBER,       -- FK to parent
  variant        VARCHAR2(8),  -- An arbitrary string  
  instance_count NUMBER,
  mod_date       DATE          -- Date the record was processed by the sender
);

The instance_count column is an incremented value based on the parent_ref,variant combination. It should be monotonically increasing by mod_date. There are other columns that have been omitted for clarity. A valid set of data would look like:

example_id	parent_ref	variant	instance_count	mod_date
1	47	abc	0	2020-01-01
2	47	xyz	0	2020-01-01
3	52	abc	0	2020-02-01
4	47	abc	1	2020-02-03
5	53	def	0	2020-03-01
6	47	abc	2	2020-03-03

Right now a (select nvl(max(instance_count),-1)+1 from example where parent_ref=p_parent and variant=p_variant) is being performed to generate instance_count when new records are processed. There are some issues with this implementation, the most obvious one is that the instance count will be wrong if data arrives out of order. Another issue is it does not handle concurrent writers correctly.

I would very much like to change the design of the table, but that is not within the scope. The instance_count column is being used in reports and tools--plus the users prefer it over the date. My first thought was to replace it with a virtual column, but window functions cannot be used in a virtual column (at least as of Oracle 12c).

The table is the largest one in the schema with millions of rows. On average there are 3 variants per parent_ref and 99% of the rows have an instance_count of 0. Data ingest occurs at a low rate (usually less than 1000 rows/day).

Replace with a view

One approach is a view that uses ROW_NUMBER() to generate the column (N.B. in the actual implementation the view would be named example and the table something else to avoid breaking queries):

CREATE VIEW example_view AS
SELECT 
  example_id, parent_ref, variant, mod_date,
  ROW_NUMBER() 
    OVER (PARTITION BY parent_ref, variant ORDER BY parent_ref, variant, mod_date) instance_count
FROM result;

When I compare the performance (e.g. EXPLAIN PLAN, actual query times) between the table and view, the optimizer does a good job of rewriting the query to ignore the partition operation when instance_count is not used. When instance_count is used, the cost is about 3 times more. I need to do more testing to determine the actual real world impact.

Keep same design

Keeping the current design requires a more robust method for generating instance_count. Fortunately, all INSERT and UPDATE transactions are handled via a stored package (the only exception would be some extraordinary data maintenance). One approach would be to implement a table lock until the transactions are committed, but that approach strikes me as a brittle solution. Alternatively, the generation of instance_count could be deferred and handled in a separate transaction after the first one is committed. There would be a small opportunity for a race condition between the two transactions where a query would get bogus values for instance_count.

Summary

While I do like the view approach, the potential performance impact gives me pause. The cost could be mitigated by using a materialized view. The storage requirement for the table is modest, so a materialized view is not a storage problem. What are some of the administrative issues with using a MV? Any thoughts for a different solution?

dbfiddle here

Answer 1

Incremental Gap Free Number

What you've described is the requirement for an Incremental Gap Free number.

There are two ways to implement this requirement. You've already mentioned the VIEW method. Your "lock table" method is flawed.

The methods used are:

1. Use a View/MV to calculate the instance_count.
2. Lock a row in a parent table whose PK is parent_ref,variant within a Transactional API [XAPI]. (The Package that does your INSERT and UPDATE).

VIEW

The VIEW problem will "automatically" renumber the instance_count if not-the-top-one is deleted.

example: if you have instance_counts 1,2,3,4 and you need to delete #2, you'll end up with 1,2,3

sometimes, this is a problem. sometimes, its desired.

XAPI

create table parent_variant (
  parent_ref               NUMBER,       -- FK to parent
  variant                  VARCHAR2(8),  -- An arbitrary string 
  current_instance_count   number not null default zero,
  constraint parent_variant_pk  (parent_ref, variant)
);

By locking the row on this table, you minimize the about of serialization.

However, you may want to ensure that the Bulk Load process goes through the XAPI also.

example INSERT code:

procedure do_insert( p_parent_ref in parent_variant.parent_ref%type,
                     p_variant    in parent_variant.variant%type )
as
   l_instance_count parent_variant.instance_count%type;
begin
  <<gain_lock>>
  begin
    select  current_instance_count
      into l_instance_count
    from parent_variant
    where parent_ref = p_parent_ref
      and variant = p_variant
    FOR UPDATE wait 5; -- wait 5s to grab lock, then fail 
  exception
    when no_data_found then
      insert into parent_variant(parent_ref,variant)
        values (p_parent_ref,p_variant)
        returning current_instance_count into l_instance_count;
  end;
  
  -- l_instance_count++ (with sanity check)
  l_instance_count := nvl( l_instance_count + 1, 1);

  -- normal insert
  insert into example (parent_ref, variant, instance_count, mod_date)
    values (p_parent_ref, p_variant, l_instance_count, sysdate);

  -- update parent_variant value
  update parent_variant
    set current_instance_count = l_instance_count
  where parent_ref = p_parent_ref
    and variant = p_variant;
end;