Oracle快速重叠检查 [英] Oracle faster overlap check

问题描述

我有一个包含两列beginrange和endrange的表。不允许重叠范围。这些列上有索引，我们尝试过许多sql条件，例如

  inputBegin在beginRange和endRange或
 inputEnd在beginRange和endRange之间
  

; beginRange或inputStart> endRange）

etc
这些工作很好，除非它们非常慢，

编辑

strong>：
我想到了一个更多的解决方案，oracle将计数索引，只有当count（）在一个具有索引的NOT NULL列完成。如果beginRange和endRange不为NULL，并且都有索引，我们可以有三个和：

  count（endRange）where inputBegin> endRange 
 + 
 count（beginRange）where inputEnd< beginRange 
 = 
 count（beginRange / endRange）
  

会得到三行，在代码中，我需要检查前两者的和是否等于第三。当然，我假设只有索引将被计数，没有行将被访问。

解决方案

这是一个答案 - 如果可以做出某些断言：

您有一个包含 beginRange 和 endRange 列的表，其中没有两个现有行重叠（beginRange，endRange）。

您要插入一个新行 ，inputEnd）但检查它是否与表上的任何现有行重叠。

然后，您可以使用这个条件 - 用 startRange 的简单索引：

  WHERE input_Start< 
（SELECT endRange 
 FROM 
（SELECT endRange 
，ROW_NUMBER（）OVER（ORDER BY startRange DESC）AS rn 
 FROM tableX 
 WHERE startRange< input_End 
）tmp 
 WHERE rn = 1 
）
 
 
 --- TRUE  - >重叠
 --- FALSE  - >无重叠
  

I've a table with two columns beginrange and endrange. No overlapping ranges should be allowed.There are indexes on these columns and we've tried many sql conditions like

inputBegin between beginRange and endRange or
inputEnd between beginRange and endRange 

not ( inputEnd < beginRange or inputStart > endRange )

etc Which work fine, except they're very slow as the table contains over 5mil records.

Is there anyway to write a much efficient overlap check?

Edit: I've thought of one more solution, oracle will count the index only when count() is done on a NOT NULL column with an index. If beginRange and endRange are NOT NULL and both have an index we can have three sums:

count(endRange) where inputBegin > endRange
+
count(beginRange) where inputEnd < beginRange
=
count(beginRange/endRange)

so with UNION ALL I would get three rows, and in the code I need to check if sum of first two equals the third. Of course I'm assuming only index will be counted and no rows will be accessed. Any other way?

解决方案

This is an answer - if certain assertions can be made:

You have a table with beginRange and endRange columns where there ano no two existing rows with overlapping (beginRange, endRange).

You want to insert a new row with (inputStart, inputEnd) but check if it overlaps with any of the existing rows on the table.

Then you can use this condition which should be fast - with a simple index on startRange:

WHERE input_Start <
      ( SELECT endRange
        FROM
          ( SELECT endRange
                 , ROW_NUMBER() OVER(ORDER BY startRange DESC) AS rn 
            FROM tableX
            WHERE startRange < input_End
          ) tmp
        WHERE rn = 1
      )


  --- TRUE  --> Overlaps
  --- FALSE --> No overlap

这篇关于Oracle快速重叠检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！