如何调优以下 Oracle10g 查询？

Question

我编写了一个查询来从 my_codes 表中获取一些重叠的数字范围。查询是...

select distinct t1.destination,
                      t1.digitsmin,
                      t1.digitsmax,
                      t2.destination,
                      t2.digitsmin,
                      t2.digitsmax,
                      'S'
        from my_codes t1
        join my_codes t2
          on t1.rownumber <> t2.rownumber
         and t1.typ = t2.typ
       WHERE t1.mycarr= 73
         and t1.typ = 'S'
         AND (t2.DigitsMin <= t1.DigitsMin AND t2.DigitsMax > t1.DigitsMin and
             t2.DigitsMax < t1.DigitsMax OR
             (t2.digitsmin > t1.digitsmin and t2.digitsmax <= t1.digitsmin) OR
             (t2.digitsmin >= t1.digitsmin and t2.digitsmax < t1.digitsmax) OR
             (t2.digitsmin > t1.digitsmin and t2.digitsmax <= t1.digitsmax) OR
             (t2.digitsmin > t1.digitsmin and t2.digitsmin <= t1.digitsmax and
             t2.digitsmax > t1.digitsmax));

my_codes 表数据是

mycarr  typ  rownumber destination digitsmin digitsmax
73       S    1         AAA        8875       8880
73       S    2         AAA1       8870       8880
73       S    3         AAA2       8875       8878
73       S    4         AAA3       8876       8880

如果表有更多数据，则需要花费大量时间。有人可以帮我解决这个问题吗？

Answer 1

尝试将其简化为这样：

select distinct t1.destination,
                      t1.digitsmin,
                      t1.digitsmax,
                      t2.destination,
                      t2.digitsmin,
                      t2.digitsmax,
                      'S'
        from my_codes t1
        join my_codes t2
          on t1.rownumber <> t2.rownumber
         and t1.typ = t2.typ
       WHERE t1.mycarr= 73
         and t1.typ = 'S'
         and t1.digitsmax >= t2.digitsmin
         and t1.digitsmin <= t2.digitsmax;

根据您的四个示例行，这将返回 12 行，而不是原始查询返回的 7 行。但是，据我了解，这是正确的，因为每一行都与其他行重叠。