SAP HANA HDB 分区管理 (二)
哈希分区(HASH partitioning):
哈希分区用于把表平均分配到各个分区中,做到负载均衡和克服表2亿行数据的限制。
语法:
CREATE COLUMN TABLE TEST_HASH_PARTITION_2 (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS 4
创建四个分区在列a 和b上。
select * from sys.m_cs_partitions where table_name='TEST_HASH_PARTITION_2'
SCHEMA_NAME TABLE_NAME PART_ID PARTITION; SUBPARTITION; RANGE
KEMIYA2 TEST_HASH_PARTITION_2 1 1 1
KEMIYA2 TEST_HASH_PARTITION_2 2 2 2
KEMIYA2 TEST_HASH_PARTITION_2 3 3 3
KEMIYA2 TEST_HASH_PARTITION_2 4 4 4
由列a 和 b的实际值来确定目标分区。
a,b 是主键的一部分
创建分区字段不是主键的一部分时:
CREATE COLUMN TABLE TEST_HASH_PARTITION_3 (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, c) PARTITIONS 4
错误信息:
Could not execute 'CREATE COLUMN TABLE TEST_HASH_PARTITION_3 (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY ...'
SAP DBTech JDBC: [435] (at 105): invalid expression: partition column should be included in primary key columns: c: line 1 col 106 (at pos 105)
推荐使用创建哈希分区的脚本:
CREATE COLUMN TABLE TEST_HASH_PARTITION (A INT, B INT, C INT, PRIMARY KEY (A,B)) PARTITION BY HASH (A, B)
PARTITIONS GET_NUM_SERVERS()
分区数是由数据库引擎在运行时根据其配置来决定。
循环分区(Round-robin partitioning):语法
CREATE COLUMN TABLE mytab (a INT, b INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS 4
哈希-范围分区:
CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY
HASH (a, b) PARTITIONS 4,
RANGE (c)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20)
循环-范围分区:
CREATE COLUMN TABLE mytab (a INT, b INT, c INT)
PARTITION BY
ROUNDROBIN PARTITIONS 4,
RANGE (c)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20)
哈希-哈希分区:
CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b))
PARTITION BY
HASH (a, b) PARTITIONS 4,
HASH (c) PARTITIONS 7
转移分区:SAP HANA的分区和分区组可以从一台服务器转移到另外一台服务器,
语法: ALTER TABLE mytab MOVE PARTITION 1 TO '<host:port>'
通过M_CS_PARTITIONS
host:主机名 port: 目标index server的端口不是SQL 端口。