Re: Q: How do I avoid overlapping values?

Bjørn,

If you have an index on the two date columns I would think that your performance would be reasonable. There is one other way that you could possibly speed things up if your date ranges are usually consecutive (no gaps). I assume this is most likely the case as you mention that you're keeping track of prices (no good not knowing the price...). You could run a process to build date ranges into another table (probably an index-only table would be good). These would be the aggregated ranges of dates already existing for the product (I assume you are keeping track of the price of more than one 'thing' in this table). Then each update would check against the aggregate table to see if there were overlaps.

Another possiblity is that if you usually only add new periods, you may just keep the latest datefrom / to and price in your product table. The only time you'll need to check the period table for overlaps is if the datefrom of the new record is less than the datefrom of the latest one. Of course you'd still have to truncate the latest period in the case that there was an overlap, but that's only one record :).

Regards, David.

Bjørn Augestad wrote:

> Steven Hauser wrote:
> >
> > Try check constraints. They can be deferred so a large insert or update can
> > be checked at the end of your transaction.
> > --
> > ---------------------------------------------------------
> > Steven Hauser
> > email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011
> > ---------------------------------------------------------
>
> AFAIK I can't use check constraints, since check's only validates one row. I have to verify that on existing rows in the table has a date span that contains one or more dates from the new row. Something like this
> select count(*) into cOverlappingRows
> from foo
> where dateFrom <= :new_dateTo
> and dateTo >= :new_dateFrom;
>
> if cOverlappingRows > 0 then
> overlap_error ...
> end if;
>
> I have a feeling that this may be too slow for our needs, but I'm stumped...
> Bjørn
Received on Thu Jun 03 1999 - 19:15:04 CDT