分区条件为表达式 c1 + 1,而查询条件为非等值条件 c1 < 150 and c1 > 100,则无法进行分区裁剪。示例如下:
EXPLAIN SELECT * FROM t1 WHERE c1 > 110 and c1 < 150 \G
*************************** 1. row ***************************
Query Plan: ============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
|0 |EXCHANGE IN DISTR | |19 |1410|
|1 | EXCHANGE OUT DISTR| |19 |1303|
|2 | TABLE SCAN |t1 |19 |1303|
Outputs & filters:
0 - output([t1.c1], [t1.c2]), filter(nil)
1 - output([t1.c1], [t1.c2]), filter(nil)
2 - output([t1.c1], [t1.c2]), filter([t1.c1 < 150], [t1.c1 > 110]),
access([t1.c1], [t1.c2]), partitions(p[0-1])
问题:
分区条件为表达式 c1 + 1,而查询条件为非等值条件 c1 < 150 and c1 > 100,则无法进行分区裁剪?为什么?
WHERE c1 > 110 and c1 < 150 不就是 WHERE c1 + 1 > 111 and c1 + 1< 151 吗?
分区条件为表达式 c1 + 1,而查询条件是等值条件,则可以进行分区裁剪。示例如下:
EXPLAIN SELECT * FROM t1 WHERE c1 = 150 \G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
|0 |TABLE SCAN|t1 |1 |1303|
Outputs & filters:
0 - output([t1.c1], [t1.c2]), filter([t1.c1 = 150]),
access([t1.c1], [t1.c2]), partitions(p1)
问题:
为什么?