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
  example_id, parent_ref, variant, mod_date,
    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.


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

  • I'd suspect an MV wouldn't actually work that well - it would need to be a fully refreshed MView which means keeping it up to date would be a disaster. You would have to write an instead of trigger in order to update your base table form it. I would just go with the XAPI solution - it scales exactly as much as it needs to. The table would be easy to query. You'd just have to accept that the count is assigned at insert so it doesn't have to be in the same order as some column (using columns that could be updated as a key is definitely more trouble than it's worth!) Nov 4, 2021 at 21:43

1 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).


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.


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 )
   l_instance_count parent_variant.instance_count%type;
    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 
    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;
  -- 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;
  • Thanks for the answer--it further reinforces the view based approach because the presented XAPI solution will break if data messages are processed out of order (not your fault--I wasn't clear that MOD_DATE meant processed by the sender and not the receiver). Creating the parent_variant table is a definite improvement to what I was thinking and provides a better way to update instance_count than what I was thinking. That said, it doesn't appear to be notably better than a materialized view.
    – Jim D
    Nov 4, 2021 at 19:05
  • I would do this but instead of the select into.. just do an update .. returning this saves the additional call at the end and has the same (correct) row locking requirements. I would also give the example table a foreign key to this one so there's no need for the exception logic. Nov 4, 2021 at 21:45
  • This XAPI Procedure let's the application (developer) not think about these things. The call is designed to place the needed entry in the parent_variant table if it doesn't exists, and raise an error if obtaining a row lock takes longer than 5s (WAIT 5). If I didn't have the Timeout requirement, I'd use MERGE to satisfy the Ensure Row Exists and Lock Row requirements. I'll edit code later. Nov 5, 2021 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.