sql - (自己)按时间间隔加入

我在oracle数据库中有一张表。该架构是

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

我有两种不同的TYPE's:TYPEA和TYPEB。具有独立的开始和结束时间，它们可以重叠。我想找到的是TYPEB的开始，完全包含或结束于给定TYPEA的时期。

到目前为止，这是我想出的(带有一些示例数据)

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;

这似乎是原则上可行，上面查询的结果是

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

因此它选择在第一个TYPEB周期内开始或结束的三个周期TYPEA。

问题在于该表大约有200k条目，并且已经达到此大小，上述查询非常慢---这对我来说非常令人惊讶，因为TYPEA和TYPEB条目的数量都非常低(1-2k)

有没有更有效的方法来执行这种类型的自我联接？我是否想念查询中的其他内容？

最佳答案

也许值得一试(最后您还需要在oracle中编写最严格的条件，不要问我为什么或不相信我，最好进行自己的性能测试):

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
;