Forum Stats

  • 3,824,927 Users
  • 2,260,440 Discussions
  • 7,896,351 Comments

Discussions

Unique key on date range columns (temporal validity)

John_K
John_K Member Posts: 2,498 Gold Trophy
edited Mar 27, 2017 3:22AM in Database Ideas - Ideas

A lot of systems use date-tracking functionality, where a record is valid from one date to another.

ID  Date_From    Date_To1   01-Jan-2017  12-Jan-20171   13-Jan-2017  31-Jan-2017

in most instances we would not want overlapping date ranges for a given ID. We can put in a unique key on say (ID, Date_From) which would eliminate duplicates, or we could write some kind of trigger to detect duplicates (with all kinds of workarounds for mutating table etc). However I think it would be really useful if we could have a special constraint (or option to the "Add Period" clause when enabling Temporal Validity such as "No Overlap(key columns)") whereby for a given set of columns, they cannot intersect with another record having those columns. I.e. the following would not be allowed.

ID  Date_From  Date_To1   01-Jan-2017 12-Jan-20171   03-Jan-2017 31-Jan-2017

I appreciate on 11g and earlier it might have been impractical to implement however with TV now being a feature of the database rather than just being implemented as two date columns on a record, I'm sure there must be some underlying structures which would make this reasonably efficient to build into the database that wouldn't have the overhead of having to scan loads of records looking for intersections.

ctriebSven W.Mike KutzberxulohmannPeter HraškoJeffrey KempWilliam Robertsondba_mm3782731ApexBinecormacosdstuber
15 votes

Active · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    Behind the scenes, Oracle creates a "system generated", "hidden" column with the name <period>.

    This is where I would place "some underlying structures".  (eg an SDO "Line")

    Right now, that value seems to be a constant.

    Even if without "unique", having the ability to use the <period> as part of a CREATE INDEX statement would be beneficial.

    MK

  • [Deleted User]
    [Deleted User] Posts: 0 Silver Trophy

    Using Workspace Manager allows you to do this kind of thing: Workspace Manager Valid Time Support

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown

    In my view, the best way to get this functionality is through SQL Assertions, see

    If we got that, we could "assert" no overlaps easily.

    I strongly recommend upvoting that idea; it is the best and most likely way to get what you want.

    Best regards, Stew Ashton

  • John_K
    John_K Member Posts: 2,498 Gold Trophy

    In my view, the best way to get this functionality is through SQL Assertions, see

    If we got that, we could "assert" no overlaps easily.

    I strongly recommend upvoting that idea; it is the best and most likely way to get what you want.

    Best regards, Stew Ashton

    Already voted up a while back.

    Whilst I can see assertions being extremely useful, I see this as being somewhat of a specialized case in that it could probably be implemented to run far quicker than a generalized solution. Perhaps it could use assertions under the covers, however I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown

    Already voted up a while back.

    Whilst I can see assertions being extremely useful, I see this as being somewhat of a specialized case in that it could probably be implemented to run far quicker than a generalized solution. Perhaps it could use assertions under the covers, however I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.

    ORA-01033 wrote:... I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.

    Wow, why do you say that? Checking for overlaps is two inequality joins, and inequality joins can occasionally be a performance nightmare - not to mention figuring out which rows to lock before doing the check.

    Constraining date ranges is probably the number one use case for assertions. It has been mentioned in the comments over there.

    I suspect any specific support for periods would just be syntactic sugar over assertions.

    Please understand that I want a good solution for avoiding overlaps as much as you do, but Oracle is going to pick and choose among these ideas, so I keep in mind priorities when I vote. Assertions are far and away my priority over all the other ideas I see here, even though many appeal to me.

    Bests regards, Stew

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    ORA-01033 wrote:... I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.

    Wow, why do you say that? Checking for overlaps is two inequality joins, and inequality joins can occasionally be a performance nightmare - not to mention figuring out which rows to lock before doing the check.

    Constraining date ranges is probably the number one use case for assertions. It has been mentioned in the comments over there.

    I suspect any specific support for periods would just be syntactic sugar over assertions.

    Please understand that I want a good solution for avoiding overlaps as much as you do, but Oracle is going to pick and choose among these ideas, so I keep in mind priorities when I vote. Assertions are far and away my priority over all the other ideas I see here, even though many appeal to me.

    Bests regards, Stew

    Stew Ashton wrote:ORA-01033 wrote:... I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.Wow, why do you say that? Checking for overlaps is two inequality joins, and inequality joins can occasionally be a performance nightmare - not to mention figuring out which rows to lock before doing the check.

    I was thinking more along the lines of it being implemented internally using a different structure to what currently exists - certainly not "simple" inequality joins. Perhaps using Oracle Spatial structures to represent the date range as a 2D value somehow etc. I dunno - I just posted the idea, I don't intend to try and design the solution . But yes I agree on the assertions idea being a superset of this and very useful.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    Stew Ashton wrote:ORA-01033 wrote:... I'd expect the check on whether a record already exists to be similar speed to the lookup of a primary key.

    Wow, why do you say that? Checking for overlaps is two inequality joins, and inequality joins can occasionally be a performance nightmare - not to mention figuring out which rows to lock before doing the check.

    I was thinking more along the lines of it being implemented internally using a different structure to what currently exists - certainly not "simple" inequality joins. Perhaps using Oracle Spatial structures to represent the date range as a 2D value somehow etc. I dunno - I just posted the idea, I don't intend to try and design the solution . But yes I agree on the assertions idea being a superset of this and very useful.

    John_K

    I believe it would be better off as an INDEX instead of an ASSERTION.

    Behind the scenes, an R*Tree Index could be used.  (As I understand, this is what Oracle Spatial uses under the hood)

    John_K wrote:I just posted the idea, I don't intend to try and design the solution  

    I only went as far as implementing a Domain Index as a Proof of Concept.

    I can post the URL to it (LiveSQL) if any only if anyone is interested.