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 );
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:
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
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
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?