(Selbst) join nach timeintervallen

Ich habe eine Tabelle in einer Oracle-database. Das Schema ist

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

Ich habe zwei verschiedene TYPE's : TYPEA und TYPEB . Sie haben unabhängige Start- und Endzeiten und können sich überschneiden. Was ich gerne hätte, sind die TYPEB timeräume, die innerhalb eines bestimmten timeraums von TYPEA begonnen, vollständig abgeschlossen oder beendet wurden.

Hier ist, was ich bisher herausgefunden habe (mit einigen Beispieldaten)

 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; 

Dies scheint im Prinzip zu funktionieren, das Ergebnis aus der obigen Abfrage ist

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

TYPEB wählt es die drei Perioden TYPEB , die innerhalb der ersten TYPEA Periode begonnen oder beendet wurden.

Das Problem ist, dass die Tabelle über 200k Einträge hat und bereits bei dieser Größe die obige Abfrage ziemlich langsam ist – was für mich sehr überraschend ist, da die Anzahl der Einträge TYPEA und TYPEB ziemlich niedrig ist (1-2k)

Gibt es eine effizientere Möglichkeit, diese Art von Self-Join durchzuführen? Habe ich etwas anderes in meiner Anfrage vermisst?

Solutions Collecting From Web of "(Selbst) join nach timeintervallen"

Vielleicht einen Versuch wert (auch müssen Sie am Ende die meisten einschränkenden Bedingungen in oracle schreiben, fragen Sie mich nicht warum oder glauben Sie mir, besser machen Sie Ihre eigenen performancestests):

 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 ;