(self) are combined in time intervals

Question

I have a table in oracle database. Scheme

create table PERIODS ( ID NUMBER, STARTTIME TIMESTAMP, ENDTIME TIMESTAMP, TYPE VARCHAR2(100) ) 

I have two different TYPE's : TYPEA and TYPEB . They have independent start and end times, and they can overlap. What I would like to find are the initial TYPEB periods, fully contained or completed within the given TYPEA period.

Here is what I came up with so far (with some sample data)

 WITH mydata AS (SELECT 100 ID, To_timestamp('2015-08-01 11:00', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 11:20', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEA' TYPE FROM dual UNION ALL SELECT 110 ID, To_timestamp('2015-08-01 11:30', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 11:50', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEA' TYPE FROM dual UNION ALL SELECT 120 ID, To_timestamp('2015-08-01 12:00', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 12:20', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEA' TYPE FROM dual UNION ALL SELECT 105 ID, To_timestamp('2015-08-01 10:55', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 11:05', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEB' TYPE FROM dual UNION ALL SELECT 108 ID, To_timestamp('2015-08-01 11:05', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 11:15', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEB' TYPE FROM dual UNION ALL SELECT 111 ID, To_timestamp('2015-08-01 11:15', 'YYYY-MM-DD HH24:MI') STARTTIME, To_timestamp('2015-08-01 12:25', 'YYYY-MM-DD HH24:MI') ENDTIME, 'TYPEB' TYPE FROM dual), typeas AS (SELECT starttime, endtime FROM mydata WHERE TYPE = 'TYPEA'), typebs AS (SELECT id, starttime, endtime FROM mydata WHERE TYPE = 'TYPEB') SELECT id FROM typebs b join typeas a ON ( b.starttime BETWEEN a.starttime AND a.endtime ) OR ( b.starttime BETWEEN a.starttime AND a.endtime AND b.endtime BETWEEN a.starttime AND a.endtime ) OR ( b.endtime BETWEEN a.starttime AND a.endtime ) ORDER BY id; 

This seems to work in principle, the query result above

  ID ---------- 105 108 111 

therefore, he selects three TYPEB periods that begin or end during the first TYPEA period.

The problem is that the table has about 200 thousand records and already has such a size that the above query is rather slow, which is very surprising for me, since the number of TYPEA and TYPEB rather small (1-2k)

Is there a more efficient way to do this type of self-join? Am I missing something else in my request?

Answer 1

Maybe it’s worth a try (you also need to write the most restrictive conditions at the end in the oracle, don’t ask me why or believe me, better do your own performance tests):

 SELECT p.id FROM periods p WHERE EXISTS(SELECT * FROM periods q WHERE (p.startTime BETWEEN q.startTime AND q.endTime OR p.endTime BETWEEN q.startTime AND q.endTime OR p.startTime < q.startTime AND p.endTime > q.endTime -- overlapping correction, remove if not needed ) AND q.type = 'TYPEA' ) AND p.type = 'TYPEB' ORDER BY p.id ;