The “OVERLAPS” Predicate
Posted by David Aldridge on 2008-06-12
I was browsing a pretty interesting “e-book” this evening: “Developing Time-Oriented Database Applications in SQL” By Richard T. Snodgrass. I quietly bemoaned the missing functionality of Oracle in some regards, for instance the rather neat OVERLAPS predicate that returns true when two intervals … well, overlap. We tend to jump through hoops a little to achieve this sometimes, I felt.
But you never know, things have moved on since the Oracle 8, so I searched the 10g and 11g documentation to see if I’dbeen negligent in not reading the New Features Guide. Alas, nothing in the SQL Reference, but I did turn up this interesting entry:
ORA-30085: syntax error was found in overlaps predicate Cause: A syntax error was found during parsing an overlaps predicate. Action: Correct the syntax.
“How”, I asked myself, “can there be an error message relating to a non-existent feature”?
“What if”, I continued, “said feature is not missing, but merely undocumented?”
So I tested it.
And it works.
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 23:14:03 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter user-name: XXX/XXX@XXX Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security and Data Mining Scoring Engine options SQL> select * from dual 2 where (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2006-01-01') 4 / no rows selected SQL> SQL> select * from dual 2 where (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2007-01-02') 4 / D - X SQL> SQL> select * from dual 2 where (date '2007-01-01', interval '5' year) 3 overlaps (date '2005-01-01', date '2007-01-02') 4 / D - X SQL> SQL> select * from dual 2 where (date '2007-01-01', interval '5' year) 3 overlaps (date '2005-01-01', interval '10' year) 4 / D - X SQL> SQL> select * from dual 2 where NOT (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2006-01-01') 4 / D - X SQL> SQL> SQL> with test_data as 2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 7 select * 8 from test_data t1, 9 test_data t2 10 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 11 / FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07 01-APR-07 01-AUG-07 01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07 FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08 15 rows selected. SQL> SQL> with test_data as 2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 7 select * 8 from test_data t1, 9 test_data t2 10 where NOT (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 11 / FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07 10 rows selected. SQL> explain plan for 2 with test_data as 3 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 4 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 6 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 7 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 8 select * 9 from test_data t1, 10 test_data t2 11 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 12 / Explained. SQL> set linesize 200 SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- Plan hash value: 1640239735 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 22 (37)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | | 3 | UNION-ALL | | | | | | | 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 36 | 12 (0)| 00:00:01 | | 10 | VIEW | | 5 | 90 | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 | |* 12 | VIEW | | 1 | 18 | 2 (0)| 00:00:01 | | 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- --------------------------------------------------- 12 - filter((INTERNAL_FUNCTION("T1"."FROM_DT"),INTERNAL_FUNCTION("T1"."TO_DT"))OVERLAPS(INTERNAL _FUNCTION("T2"."FROM_DT"),INTERNAL_FUNCTION("T2"."TO_DT"))) 26 rows selected.
So not only does it let you check two pairs of dates to see if they overlap, it lets you check if they do not overlap (pretty simple stuff), and it also accepts a date and interval expression in place of date pairs.
It doesn’t work for pairs of numbers though, it seems:
SQL> select * from dual 2 where (1,5) 3 overlaps (3,8) 4 / where (1,5) * ERROR at line 2: ORA-00932: inconsistent datatypes: expected DATE got NUMBER SQL>
Still, very cool! Undocumented, but still cool.
Does it make it more cool if it is undocumented?
Gary said
Makes it more unusable :) There’s an equivalent in Workspace Manager though.
select wm_intersection (wm_period (sysdate-10,sysdate+10),
wm_period (sysdate-1 ,sysdate+1 )) from dual
where wm_overlaps (wm_period (sysdate-10,sysdate+10),
wm_period (sysdate-1 ,sysdate+1 )) =1;
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273
There’s also a WM_CONTAINS (period 1 within period 2) and a WM_INTERSECTION function (which returns the period of the overlap).
dombrooks said
That is both cool and useful.
Tim Hall said
Nice!
Much cooler if it’s undocumented. :)
Cheers
Tim…
ebuddelm said
The only other reference I found (did not look far) is in the Reserved words list. It seems somebody had a good project going over at Oracle but did not get the time to finish it.
Very cool indeed.
Brian Tkatch said
The code i’ve been using lately for check range A1-A2 and B1-B2 is:
WHERE A2 BETWEEN B1 AND B2
OR B2 BETWEEN A1 AND A2
In our case, A1-A2 is a one month range, and instead of using the last moment of the month, we are using the first moment of the next month, in order to get around any granularity issues. This requires a final check:
WHERE A2 BETWEEN B1 AND B2
OR (B2 BETWEEN A1 AND A2 AND B2 < A2)
But this FUNCTION would sure be welcome, if only to make it clearer.
John Flack said
For many years I have had an OVERLAP_CNT(start_date1 date, end_date1 date, start_date2 date, end_date2 date) function in my CMP package, which is available on my website. This returns a 1 if the date range represented by the first two dates overlaps the date range represented by the second two dates, and a 0 if it does not. This allows you to use it in a sum() to count how many rows have an overlapping date range, or a where clause.
Blake said
Would you consider using OVERLAPS in production code? It might be useful when doing analysis on your data, but certainly if it’s undocumented there could be bugs associated with the implementation.
I’m really curious about opinions on my question because 50% of the data transformation in the current ETL project I’m on involves comparing sets of records for time interval overlaps.
Thanks!
joel garry said
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> with test_data as
2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
7 select *
8 from test_data t1,
9 test_data t2
10 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
11 /
FROM_DT TO_DT FROM_DT TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07
01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07
01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07
01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07
01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07
01-APR-07 01-AUG-07 01-APR-07 01-AUG-07
01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07
01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07
01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07
01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08
FROM_DT TO_DT FROM_DT TO_DT
--------- --------- --------- ---------
01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08
01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08
01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08
01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08
15 rows selected.
joel garry said
Even more cool if it’s been sitting there undocumented for years and years!
David Aldridge said
Indeed, I was sent a demo of it running on 8.1.5.0.0 also, so it goes way back.
Would I use it in production? Not a chance.
joel garry said
See section 14.1.5 in the FIPS standard. The O8 SQL reference notes partial compliance, but not that particular transitional. Which, oddly enough, includes a reference to timestamp, which many people are just now starting to struggle with.
So I bet someone at Oracle has been thinking about this at least since the draft 1992 ANSI SQL standard started circulating.
SQL standards. There’s a job I could not do.
carlosal said
I had to learn it on my move to Teradata, and it turns out that it has been there in Oracle for years…
Cheers.
Carlos.
Log Buffer #102: a Carnival of the Vanities for DBAs said
[...] the Oracle Sponge, David Aldridge exposes Oracle’s OVERLAPS predicate. “I quietly bemoaned the missing functionality of Oracle in some regards, for instance the [...]
Michael Garfield Sørensen said
Just be aware that (matching) end-points do not overlap:
select ‘x’ x from dual where trunc(sysdate)+1=trunc(sysdate)+1;
X
-
x
select ‘x’ from dual where (trunc(sysdate),trunc(sysdate)+1)
overlaps (trunc(sysdate)+1,trunc(sysdate)+2);
no rows selected
See Not so overlapping OVERLAPS!
Cool Undocumented OVERLAPS Predicate | Oracle said
[...] Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in [...]
Duke Ganote said
I agree with Tim: it’s much cooler if it’s undocumented!
As for using something like that in production… well, it depends on how clumsy or available the “official” options are. For example, I used COMMITSCN in production:
http://tinyurl.com/2rmj3z
However, I warned management that it was the only way to achieve their “must have” goal on an Oracle 7.3.4 database!
“You can always tell the pioneers by the arrows in their backs”: I’ve had to pull a few outta my backside over the years.
Amit said
This is very cool thing.
i am going to use it in 6 years old classic asp application.
Good work.
Nick said
Example of same query as in message 8, but without useing overlaps, also you can define rules of overlapping by setting different compare rules (less or less&equal, above or above&equal):
with test_data as
(select date ‘2007-01-01′ from_dt, date ‘2007-12-01′ to_dt from dual union all
select date ‘2007-04-01′ from_dt, date ‘2007-08-01′ to_dt from dual union all
select date ‘2007-07-01′ from_dt, date ‘2007-08-01′ to_dt from dual union all
select date ‘2007-10-01′ from_dt, date ‘2008-02-01′ to_dt from dual union all
select date ‘2008-01-01′ from_dt, date ‘2008-12-01′ to_dt from dual)
select *
from test_data t1,
test_data t2
where t1.from_dt LESS_THAN t2.to_dt and t1.to_dt ABOVE_THAN t2.from_dt
Plese delete my above messages -)
Ioana said
Is it possible that the overlaps predicate is not supported by the driver, and gives an oracle internal error (ORA-00600)?
David Aldridge said
I think that is quite possible
Andrew from SGNZ said
What Oracle RDBMS really needs is a special built-in index (kind of like a unique index, but not quite) which would be able to reject records with overlapping time ranges.