Geek asks and answers

Quick Oracle Overlap Check

I have a table with two beginrange and endrange columns. Overlapping ranges must not be allowed. These columns have indexes, and we tried many sql conditions, like

inputBegin between beginRange and endRange or
inputEnd between beginRange and endRange

not ( inputEnd < beginRange or inputStart > endRange )

etc. Which ones work fine, except that they are very slow, as the table contains entries over 5 miles.

Is there a way to write an effective overlap check?

Edit : I was thinking about another solution, the oracle will only read the index when count () is executed in the NOT NULL column with the 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)

UNION ALL , , . , , , . ?

+4
4

, :

  • , , ,
  • , ?

(1), , ...

SELECT *
FROM YOUR_TABLE
WHERE :inputEnd > beginRange AND :inputStart < endRange;

... , , : {beginRange ASC, endRange DESC}.

(2), windowing :

SELECT *
FROM (
    SELECT
        YOUR_TABLE.*,
        LEAD(beginRange) OVER (ORDER BY beginRange) nextBeginRange
    FROM YOUR_TABLE
)
WHERE endRange > nextBeginRange;

, ( "" beginRange).

, ( covering) - {beginRange} .

+1

- :

beginRange endRange, (beginRange, endRange).

(inputStart, 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  --> Overlaps
  --- FALSE --> No overlap
+1

, . , , ...

1) InputBegin
2) InputEnd
3)

SELECT * FROM yourTable WHERE InputEnd   < ExclusionPeriodStart 
UNION ALL
SELECT * FROM yourTable WHERE InputBegin > ExclusionPeriodEnd

InputEnd. , .

, , .

( ), ( , ). , UNION ALL UNION.

, , . ( 5- , , .)


EDIT. , , . , ...

. , , , , ...

1 -->--> 4
      3 -->--> 6
            5 -->--> 8
                  7 -->--> 9

1 3 2 4?

, , , .

, , A B, B A.

SELECT
  *
FROM
  yourTable   AS first_range
INNER JOIN
  yourTable   AS second_range
    ON  second_range.start_date >= first_range.start_date
    AND second_range.start_date <= first_range.end_date

first_range. start_date , start_date .

EDIT2: , , ?

, , , .

SELECT * FROM yourTable WHERE InputEnd   >= ExclusionPeriodStart 
INTERSECT
SELECT * FROM yourTable WHERE InputBegin <= ExclusionPeriodEnd

, , . query1 . , ...

SELECT
  *
FROM
  yourTable
WHERE
    InputStart <= ExclusionPeriodEnd
AND InputEnd   >= ExclusionPeriodStart

WHERE , . , , (currently (start of table) -> (ExclusionPeriodEnd)).

, : ...

SELECT
  *
FROM
  yourTable
WHERE
    InputStart <= ExclusionPeriodEnd
AND InputStart >= ExclusionPeriodStart - (maximumLength)
AND InputEnd   >= ExclusionPeriodStart

.

max lnegth? , .

; , . SELECT MAX(calculatedField) FROM yourTable . . INSERTS, , DELETE ( , , ? , , ).

0

, , {beginRange} ( ) , , :

SELECT *
FROM YOUR_TABLE
WHERE beginRange = (
    SELECT MAX(beginRange)
    FROM YOUR_TABLE
    WHERE beginRange < :inputEnd
)
AND :inputStart < endRange
  • If the new range overlaps with some of the existing ranges, this query returns "highest".
  • If there is no overlap, it returns an empty result set.

The index "under" the key is {beginRange}enough for efficiency (we only need to support the "MAX scan").

0
source

More articles:


All Articles