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?
