热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
二级分区，list+range,增加/删除二级range分区的语法怎么写
两只蚂蚁
2024/09/12
650
2
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:8
【操作系统】：麒麟
【CPU】:
【问题描述】*：二级分区，list+range,增加二级range分区的语法怎么写，【alter table 表 add partition xxx "P20240103" VALUES LESS THAN(DATETIME'2024-01-03 00:00:00') 】报错。
PARTITION BY LIST("AREA_CODE")
SUBPARTITION BY RANGE("READ_DATE") SUBPARTITION TEMPLATE
(
SUBPARTITION "P20240101" VALUES LESS THAN(DATETIME'2024-01-01 00:00:00'),
SUBPARTITION "P20240102" VALUES LESS THAN(DATETIME'2024-01-02 00:00:00')
)
)
(
PARTITION "P050000 VALUES('050000') STORAGE(ON "TBS_ELE_DATA_050000, CLUSTERBTR) ,
PARTITION "P050100 VALUES('050100') STORAGE(ON "TBS_ELE_DATA_050100, CLUSTERBTR)
) STORAGE(HASHPARTMAP(1), ON "TBS_ELE_DATA_050000", CLUSTERBTR) ;
回答 0
暂无回答
codePanda
参考语法：
-- 加二级分区
ALTER TABLE eco MODIFY PARTITION P050000 ADD SUBPARTITION P_MAX VALUES LESS THAN (MAXVALUE);
ALTER TABLE eco MODIFY PARTITION P050100 ADD SUBPARTITION P_MAX VALUES LESS THAN (MAXVALUE);
-- 删除二级分区
ALTER TABLE eco drop SUBPARTITION P_MAX ;
ALTER TABLE eco MODIFY PARTITION P050100 drop SUBPARTITION P_MAX; --不支持对具体子分区drop操作
dmdba@DAMENG:5236 SQL> @tabsubpart sysdba.eco
unknown COLUMN choosen head
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS NUM_ROWS TABPART_HIGH_VALUE HIGH_VALUE_LENGTH
----------- ---------- -------------- ----------------- ----------- -------------------- ----------------------------- -----------------
SYSDBA ECO P050000 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050000 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
SYSDBA ECO P050100 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050100 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
used time: 53.638(ms). Execute id is 622578.
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL> ALTER TABLE eco MODIFY PARTITION P050000 ADD SUBPARTITION P_MAX VALUES LESS THAN (MAXVALUE);
used time: 47.028(ms). Execute id is 622579.
dmdba@DAMENG:5236 SQL> ALTER TABLE eco MODIFY PARTITION P050100 ADD SUBPARTITION P_MAX VALUES LESS THAN (MAXVALUE);
used time: 42.511(ms). Execute id is 622580.
dmdba@DAMENG:5236 SQL> @tabsubpart sysdba.eco
unknown COLUMN choosen head
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS NUM_ROWS TABPART_HIGH_VALUE HIGH_VALUE_LENGTH
----------- ---------- -------------- ----------------- ----------- -------------------- ----------------------------- -----------------
SYSDBA ECO P050000 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050000 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
SYSDBA ECO P050000 P_MAX 3 NULL MAXVALUE 8
SYSDBA ECO P050100 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050100 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
SYSDBA ECO P050100 P_MAX 3 NULL MAXVALUE 8
used time: 9.066(ms). Execute id is 622581.
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL> ALTER TABLE eco drop SUBPARTITION P_MAX ;
used time: 64.295(ms). Execute id is 622582.
dmdba@DAMENG:5236 SQL>
dmdba@DAMENG:5236 SQL> @tabsubpart sysdba.eco
unknown COLUMN choosen head
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS NUM_ROWS TABPART_HIGH_VALUE HIGH_VALUE_LENGTH
----------- ---------- -------------- ----------------- ----------- -------------------- ----------------------------- -----------------
SYSDBA ECO P050000 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050000 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
SYSDBA ECO P050100 P20240101 1 NULL DATETIME'2024-01-01 00:00:00' 29
SYSDBA ECO P050100 P20240102 2 NULL DATETIME'2024-01-02 00:00:00' 29
SYSDBA ECO P050100 P_MAX 3 NULL MAXVALUE 8
used time: 2.701(ms). Execute id is 622583.
dmdba@DAMENG:5236 SQL>
发布于
2025/10/28 16:37
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
CREATE TABLE SALES(
SALES_ID INT,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10)
)
PARTITION BY LIST(CITY)
SUBPARTITION BY RANGE(SALEDATE)
(
PARTITION P1 VALUES ('北京', '天津')
(
SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01')
),
PARTITION P2 VALUES ('上海', '南京', '杭州')
(
SUBPARTITION P11_3 VALUES LESS THAN ('2012-10-01')
),
PARTITION P3 VALUES (DEFAULT)
(
SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01')
)
);
ALTER TABLE SALES MODIFY PARTITION P1 ADD SUBPARTITION P11_3 VALUES LESS THAN ('2012-10-02');