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