定义分区表的范围
在创建分区表时,可以为每个数据分区指定范围。 分区表使用了数据组织方案,即,表数据根据该表中表分区键列的值分布到多个数据分区中。
关于本任务
要全面地定义每个数据分区的范围,必须指定足够的边界。 以下是定义分区表的范围时要考虑的一系列准则:
- STARTING 子句指定数据分区范围的下界。 对于最低数据分区范围来说,此子句是必需的(尽管可以将边界定义为 MINVALUE)。 最低数据分区范围是具有最低指定边界的数据分区。
- ENDING(或 VALUES)子句指定数据分区范围的上界。 对于最高数据分区范围来说,此子句是必需的(尽管可以将边界定义为 MAXVALUE)。 最高数据分区范围是具有最高指定边界的数据分区。
- 如果未对某个数据分区指定 ENDING 子句,那么下一个更大数据分区就必须指定 STARTING 子句。 否则,如果未指定 STARTING 子句,那么上一个数据分区就必须指定 ENDING 子句。
- MINVALUE 指定一个值,该值小于所用列类型的任何可能值。 不能将 MINVALUE 与 INCLUSIVE 或 EXCLUSIVE 一起指定。
- MAXVALUE 指定一个值,该值大于所用列类型的任何可能值。 不能将 MAXVALUE 与 INCLUSIVE 或 EXCLUSIVE 一起指定。
- INCLUSIVE 表示将所有等于指定值的值都包括在包含此边界的数据分区中。
- EXCLUSIVE 表示所有等于指定值的值都不包括在包含此边界的数据分区中。
- CREATE TABLE 语句的 NULLS FIRST 和 NULLS LAST 子句指定在考虑数据分区放置时是将空值排序为高还是低。 缺省情况下,将空值安排在高位置。 在此情况下,将把表分区键列中的空值视为正无穷并放到以 MAXVALUE 结尾的范围中。 如果未定义这样的数据分区,就会将空值视为超出范围的值。 如果要排除表分区键列中的空值,请使用 NOT NULL 约束。 LAST 指定让空值在排序的值列表中最后出现。 FIRST 指定让空值在排序的值列表中最先出现。
- 当使用长语法格式时,必须对每个数据分区至少指定一个边界。
提示:在开始对表定义数据分区之前,您应该了解表分区是否能使表受益以及影响分区列选择的那些因素,这一点十分重要。
可以自动生成对每个数据分区指定的范围,也可以手动生成这些范围。
自动生成
自动生成方法十分简单,它使您能够快速方便地创建许多数据分区。 此方法适合于创建基于日期或数值并且大小相等的范围。
示例 1 和 2 演示如何使用 CREATE TABLE 语句来自动定义和生成对每个数据分区指定的范围。
示例 1:
CREATE TABLE lineitem (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate DATE,
l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate)))
PARTITION BY RANGE(l_shipdate)
(STARTING ('1/1/1992') ENDING ('12/31/1992') EVERY 1 MONTH);
This statement results in 12 data partitions each with 1 key value (l_shipdate) >= ('1/1/1992'), (l_shipdate) < ('3/1/1992'), (l_shipdate) < ('4/1/1992'), (l_shipdate) < ('5/1/1992'), ..., (l_shipdate) < ('12/1/1992'), (l_shipdate) <= ('12/31/1992').
由于整体起始界限('1/1/1992')包括端值(缺省情况),所以第一个数据分区的起始值包括端值。 同样,由于整体结束界限('12/31/1992')包括端值(缺省情况),所以最后一个数据分区的结束界限包括端值。 其余 STARTING 值都包括端值,并且其余 ENDING 值也都包括端值。 每个数据分区都存放 n 个键值,其中 n 由 EVERY 子句指定。 使用公式 (start + every) 来确定每个数据分区的范围末端。 如果 START 到 END 的范围无法整除 EVERY 值,最后一个数据分区包含的键值就会较少。
示例 2:
CREATE TABLE t(a INT, b INT)
PARTITION BY RANGE(b) (STARTING FROM (1)
EXCLUSIVE ENDING AT (1000) EVERY (100))
This statement results in 10 data partitions each with 100 key values (1 < b <= 101, 101 < b <= 201, ..., 901 < b <= 1000).
The starting value of the first data partition (b > 1 and b <= 101) is exclusive because the overall starting bound (1) is exclusive. Similarly the ending bound of the last data partition ( b > 901 b <= 1000) is inclusive because the overall ending bound (1000) is inclusive. 其余 STARTING 值都不包括端值,并且其余 ENDING 值全都包括端值。 每个数据分区都存放 n 个键值,其中 n 由 EVERY 子句指定。 最后,如果整个子句的起始和结束界限都不包括端值,那么由于整体起始界限(1)不包括端值,所以第一个数据分区的起始值不包括端值。 同样,由于整体结束界限(1000)不包括端值,所以最后一个数据分区的结束界限不包括端值。 其余 STARTING 值都不包括端值,并且 ENDING 值全都包括端值。 每个数据分区(最后一个数据分区除外)都存放 n 个键值,其中 n 由 EVERY 子句指定。
示例 3:
发出定义了下列范围的 CREATE TABLE 语句:
db2 "
CREATE TABLE lineitem2 (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate TIMESTAMP,
l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate)))
PARTITION BY RANGE(l_shipdate)
(STARTING ('1992-01-01-00.00.00.000000') ENDING ('1992-12-31-23.59.59.999999') EVERY 1 MONTH)
"
This statement results in 12 data partitions each with 1 key value (l_shipdate) >= ('1992-01-01-00.00.00.000000'), (l_shipdate) < ('1992-03-01-00.00.00.000000'), (l_shipdate) < ('1992-04-01-00.00.00.000000'), (l_shipdate) < ('1992-05-01-00.00.00.000000'), ..., (l_shipdate) < ('1992-12-01-00.00.00.000000), (l_shipdate) <= ('1992-12-31-23.59.59.999999').
由于整体起始界限 ('1992-01-01-00.00.00.000000') 包括端值(缺省情况),所以第一个数据分区的起始值包括端值。 同样,由于整体结束界限 ('1992-12-31-23.59.59.999999') 包括端值(缺省情况),所以最后一个数据分区的结束界限包括端值。 其余 STARTING 值都包括端值,并且其余 ENDING 值也都包括端值。 每个数据分区都存放 n 个键值,其中 n 由 EVERY 子句指定。 使用公式 (start + every) 来确定每个数据分区的范围末端。 如果 START 到 END 的范围无法整除 EVERY 值,最后一个数据分区包含的键值就会较少。
另外,对于界限指定为 DATE 的情况,在隐式转换过程中,最后一个数据分区的结束界限将不同:
db2 "
CREATE TABLE lineitem3 (
l_orderkey DECIMAL(10,0) NOT NULL,
l_quantity DECIMAL(12,2),
l_shipdate TIMESTAMP,
l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate)))
PARTITION BY RANGE(l_shipdate)
(STARTING ('1/1/1992') ENDING ('12/31/1992') EVERY 1 MONTH)
"
This statement results in 12 data partitions each with 1 key value (l_shipdate) >= ('1992-01-01-00.00.00.000000'), (l_shipdate) < ('1992-03-01-00.00.00.000000'), (l_shipdate) < ('1992-04-01-00.00.00.000000'), (l_shipdate) < ('1992-05-01-00.00.00.000000'), ..., (l_shipdate) < ('1992-12-01-00.00.00.000000), (l_shipdate) <= ('1992-12-31-00.00.00.000000').
手动生成
手动生成方法为 PARTITION BY 子句中列示的每个范围创建一个新数据分区。 这种语法格式提高了定义范围时的灵活性,从而增加了数据和 LOB 布置选项。 示例 4 和 5 演示如何使用 CREATE TABLE 语句来以手动方式定义和生成对数据分区指定的范围。
示例 4:
CREATE TABLE sales(invoice_date date, inv_month int NOT NULL
GENERATED ALWAYS AS (month(invoice_date)), inv_year INT NOT
NULL GENERATED ALWAYS AS ( year(invoice_date)),
item_id int NOT NULL,
cust_id int NOT NULL) PARTITION BY RANGE (inv_year,
inv_month)
(PART Q1_02 STARTING (2002,1) ENDING (2002, 3) INCLUSIVE,
PART Q2_02 ENDING (2002, 6) INCLUSIVE,
PART Q3_02 ENDING (2002, 9) INCLUSIVE,
PART Q4_02 ENDING (2002,12) INCLUSIVE,
PART CURRENT ENDING (MAXVALUE, MAXVALUE));
在范围之间允许存在间隔。 对于未紧贴上一数据分区 ENDING 值的范围,CREATE TABLE 语法允许您对该范围指定 STARTING 值,从而支持间隔。
示例 5:
CREATE TABLE foo(a INT)
PARTITION BY RANGE(a)
(STARTING FROM (1) ENDING AT (100),
STARTING FROM (201) ENDING AT (300))
使用允许添加或移除数据分区的 ALTER TABLE 语句还会导致范围中出现间隔。
SQL0327N The row cannot be inserted into table <tablename>
because it is outside the bounds of the defined data partition ranges.
SQLSTATE=22525
限制
- 表级别限制:
- 使用自动生成的语法格式(包含 EVERY 子句)创建的表在表分区键中只能使用数字或日期时间类型。
- 语句级限制:
- 在自动生成的语法格式中,不支持 MINVALUE 和 MAXVALUE。
- 范围按升序排列。
- 在自动生成的语法格式中,只能指定一列。
- EVERY 子句中的增量必须大于零。
- ENDING 值必须大于或等于 STARTING 值。