SAP HANA HDB 分区管理 (二)

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 端口。