CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Jim Burnell
Jim Burnell

Reputation: 1020

Efficiently determining most recent record in child table

I'm working on a project that needs to implement versioned entities, where the constant values are stored in a parent table and the varying values are stored in a child "version" table with its own key and a foreign key to the parent table.

The most recent version for each entity must be computed not as the maximum of the version_id but rather the version with the most recent created_timestamp for the given entity_id.

I've created a SQL Fiddle with the following schema:

CREATE TABLE entity (
    entity_id INTEGER,
    constant_value VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_entity PRIMARY KEY (entity_id)
)
/
CREATE SEQUENCE seq_entity
/
CREATE TABLE entity_version (
    entity_id INTEGER,
    version_id INTEGER,
    varying_value VARCHAR2(50) NULL,
    created_timestamp TIMESTAMP (9) WITH TIME ZONE DEFAULT systimestamp NOT NULL,
    CONSTRAINT pk_entity_version PRIMARY KEY (version_id),
    CONSTRAINT fk_entity_id FOREIGN KEY (entity_id) REFERENCES entity(entity_id)
)
/
CREATE SEQUENCE seq_entity_version
/
CREATE INDEX idx_fk_entity_id ON entity_version(entity_id)
/
CREATE VIEW v_entity_curr_version
(
    entity_id,
    version_id
) AS
SELECT ev.entity_id,
       MAX(ev.version_id) KEEP(dense_rank LAST ORDER BY ev.created_timestamp)
           AS version_id
  FROM entity_version ev
 GROUP BY ev.entity_id
/
DECLARE
    l_id entity.entity_id%TYPE;
BEGIN

    INSERT INTO entity(entity_id, constant_value)
    VALUES(seq_entity.nextval, 'Gomez Addams')
    RETURNING entity_id INTO l_id;
    COMMIT;
    
    INSERT INTO entity_version(version_id, entity_id, varying_value)
    VALUES(seq_entity_version.nextval, l_id, '1313 Mockingbird Ln');
    COMMIT;
    
    INSERT INTO entity_version(version_id, entity_id, varying_value)
    VALUES(seq_entity_version.nextval, l_id, '001 Cemetery Ln');
    COMMIT;

    INSERT INTO entity(entity_id, constant_value)
    VALUES(seq_entity.nextval, 'Herman Munster')
    RETURNING entity_id INTO l_id;
    COMMIT;
    
    INSERT INTO entity_version(version_id, entity_id, varying_value)
    VALUES(seq_entity_version.nextval, l_id, '001 Cemetery Ln');
    COMMIT;
    
    INSERT INTO entity_version(version_id, entity_id, varying_value)
    VALUES(seq_entity_version.nextval, l_id, '1313 Mockingbird Ln');
    COMMIT;

END;
/

We are typically writing a LOT of queries where we need to get the current version based on created timestamp, like:

SELECT e.constant_value,
       ev.varying_value
  FROM entity e
  JOIN v_entity_curr_version ecv
    ON ecv.entity_id = e.entity_id
  JOIN entity_version ev
    ON ev.entity_id = ecv.entity_id
   AND ev.version_id = ecv.version_id;

returning

CONSTANT_VALUE VARYING_VALUE
Gomez Addams 001 Cemetery Ln
Herman Munster 1313 Mockingbird Ln

and in most cases we also provide a function that computes a single most recent version id thusly:

    FUNCTION fn_get_max_version(p_entity_id INTEGER) RETURN INTEGER IS
        v_version_id entity_version.version_id%TYPE;
    BEGIN
        SELECT ev.version_id
          INTO v_version_id
          FROM entity_version ev
         WHERE ev.entity_id = p_entity_id
         ORDER BY ev.created_timestamp DESC
         FETCH FIRST ROW ONLY;

        -- return to caller
        RETURN v_version_id;
    END;

Questions:

  1. Are there likely any more performant ways of computing the most recent record(s) than MAX(ev.version_id) KEEP(dense_rank LAST ORDER BY ev.created_timestamp) and FETCH FIRST ROW ONLY? Any recommendations? We do this so often and sometimes with different techniques, so we'd like to settle on the best technique for both recordsets and individual version ids.

  2. Is there some index I could add to entity_version that would help? I tried (entity_id, version_id) as well as (entity_id, version_id, created_timestamp), and it didn't look like the execution plan used the index in either case.

Upvotes: 0

Views: 55

Answers (1)

d r
d r

Reputation: 7836

Try to create index:

CREATE UNIQUE INDEX ENTITY_VERSION_INDEX1 ON ENTITY_VERSION 
(ENTITY_ID ASC, VERSION_ID DESC)

... and then check/compare Explain Plans before and after.
It should be like this for your sql within the function:

Rows Plan
2 SELECT STATEMENT
2 SORT ORDER BY
2 TABLE ACCESS BY INDEX ROWID ENTITY_VERSION
2 INDEX RANGE SCAN ENTITY_VERSION_INDEX1

Regards...

Upvotes: 1

Related Questions