组合索引区间表达式展开

c1,c2,c3 组合索引 区间表达式展开

2. 展开半闭合区间

(s1,s2,s3) -> (+oo, +oo, +oo)

    1, 2, 3         - (c1 = s1 and c2 = s2 and c3 >= s3)          P3

    1, 2, 4         - /

    1, 3, 1         - (c1 = s1 and c2 > s2) /                               P2

    1, 3, 4          - (c1 > s1)                                                     P1

    2, 1, 3

    2, 3, 5

    4, 3, 10         - /

分析：[(s1,s2,s3), +oo) SQL语句 expr: (c1,c2,c3) >= (s1, s2, s3)

展开成普通表达式，可以集合分为3个子集

子集P1, U(c1 > s1)

子集P2, U(c1 = s1 and c2 > s2)

子集P3, U(c1 = s1 and c2 = s2 and c3 >= s3)

展开成条件表达式： (c1 > s1) OR (c1 = s1 and c2 > s2) OR (c1 = s1 and c2 = s2 and c3 >= s3)

2. 展开区间

(s1,s2,s3) -> (h1,h2,h3) 范围如何展开？

    1, 2, 3         - c1 = s2 and c2 = s2 and c3 >= s3              P3-

    1, 2, 4         - /

    1, 3, 1         - c1 = s1 and c2 > s2                                   P2-

    1, 3, 4        - /

    2, 1, 3        - c1 > s1 and c1 < h1                                   P1

    2, 3, 5

    3, 3, 3        - /

    4, 1, 1        - c1 = h1 and c2 < h2                                   P2+

    4, 1, 2

    4, 2, 4        - /

    4, 3, 8        - c1 = h1 and c2 = h2 and c3 <= 10             P3+

    4, 3, 9

    4, 3, 10       - /

分析： (s1,s2,s3) -> (h1,h2,h3) SQL语句 expr： (c1, c2, c3) >= (s1, s2, s3) and (c1, c2, c3) <= (h1, h2, h3)

展开成普通表达式，可以划分为5个集合

子集P1, U(c1 > s1 and c1 < h1)

子集P2-, U(c1 = s1 and c2 > s2)

子集P2+, U(c1 = h1 and c2 < h2)

子集P3-, U(c1 = s2 and c2 = s2 and c3 >= s3)

子集P3+, U(c1 = h1 and c2 = h2 and c3 <= h3)

展开后： P1 + P2- + P2+ + P3- + P3+

这样可以吗？

当s1=h1时，考虑一下P2-和P2+的集合，会发现，包含了一部分非法数据记录；

需要取P2-和P2+的交集才可以。

P3-和P3+存在同样的问题。

Tip：

P2- 和 P2+ 当s1=h1时，取P2-和P2+的交集

P3- 和 P3_类似，也要考虑s1=s1 并且 s2=h2时，取P3- 和 P3+的交集

解1：s1 != h1

expand：(c1 > s1 and c1 < h1) OR (c1 = s1 and c2 > s2) OR (c1 = h1 and c2 < h2) OR (c1 = s2 and c2 = s2 and c3 >= s3) OR (c1 = h1 and c2 = h2 and c3 <= h3)

解2：s1 == h1 && s2 != h2

expand：(c1 > s1 and c1 < h1) OR (c1 = s1 and c2 > s2 AND c1 = h1 and c2 < h2) OR (c1 = s2 and c2 = s2 and c3 >= s3) OR (c1 = h1 and c2 = h2 and c3 <= h3)

P1集合位空

等价于：c1 = s1 and c2 > s2 AND c1 = h1 and c2 < h2) OR (c1 = s2 and c2 = s2 and c3 >= s3) OR (c1 = h1 and c2 = h2 and c3 <= h3)

等价于：c1 = s1 and c2 > s2 AND c2 < h2) OR (c1 = s2 and c2 = s2 and c3 >= s3) OR (c1 = h1 and c2 = h2 and c3 <= h3)

解3：s1 == h1 && s2 == h2 && s3 != s3

expand: (c1 > s1 and c1 < h1) OR (c1 = s1 and c2 > s2 AND c1 = h1 and c2 < h2) OR (c1 = s2 and c2 = s2 and c3 >= s3 AND c1 = h1 and c2 = h2 and c3 <= h3)

P1集合位空， P2- 和 P2+ 交集也为空

等价于：(c1 = s2 and c2 = s2 and c3 >= s3 AND c1 = h1 and c2 = h2 and c3 <= h3)

等价于：(c1 = s2 and c2 = s2 and c3 >= s3 and c3 <= h3)

解4：s1 == h1 && s2 == h2 && s3 == h3 同解3