CNDBA社区
之前我们将了理论考试的注意事项：
达梦 DM8 DCP 备考笔记(1) — 理论考试 说明
https://www.cndba.cn/dave/article/108708
本篇开始我们进入达梦DCP 的实操部分。我这里只记录操作，相关理论部分不再说明，可以直接查看官方手册第十五章：《管理分区表和分区索引》。
DCP 考试中的相关操作可以通过命令行进行，也可以通过工具进行界面操作。 我们这里记录命令行的操作过程。
1 初始化实例
我这里准备了3台虚拟机，IP 地址信息如下：
[dave@www.cndba.cn ~]# cat /etc/hosts
127.0.0.1 localhost
192.168.56.102 dcp1
192.168.56.103 dcp2
192.168.56.104 dcp3
[dave@www.cndba.cn ~]#
102和 103 主备库，104 做监视器节点。 所以这里在操作之前现在102和 103上创建初始化的实例。
[dave@www.cndba.cn software]$ dminit PATH=/dm/dmdbms/data DB_NAME=DCP INSTANCE_NAME=DCP SYSDBA_PWD=dameng123 SYSAUDITOR_PWD=dameng123 page_size=32 extent_size=32 length_in_char=1 LOG_SIZE=256 CASE_SENSITIVE=Y CHARSET=1 PORT_NUM=5236
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-05-25
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dm/dmdbms/data/DCP/DCP01.log
log file path: /dm/dmdbms/data/DCP/DCP02.log
write to dir [/dm/dmdbms/data/DCP].
create dm database success. 2022-10-27 23:28:27
[dave@www.cndba.cn software]$
用root用户注册服务：
[root@dcp1 root]#./dm_service_installer.sh -t dmserver -dm_ini /dm/dmdbms/data/DCP/dm.ini -p dcp
Created symlink from /etc/systemd/system/multi-user.target.wants/DmServicedcp.service to /usr/lib/systemd/system/DmServicedcp.service.
Finished to create the service (DmServicedcp)
[root@dcp1 root]#
2 创建水平分区表
2.1 创建用户和表空间
[dave@www.cndba.cn DCP]$ disql SYSDBA/dameng123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 1.078(ms)
disql V8
SQL> create tablespace CNDBA1 DATAFILE '/dm/dmdbms/data/DCP/CNDBA1_01.dbf' size 128;
executed successfully
used time: 25.888(ms). Execute id is 55300.
SQL> create tablespace CNDBA2 DATAFILE '/dm/dmdbms/data/DCP/CNDBA2_01.dbf' size 128;
executed successfully
used time: 26.555(ms). Execute id is 55301.
SQL> create tablespace CNDBA3 DATAFILE '/dm/dmdbms/data/DCP/CNDBA3_01.dbf' size 128;
executed successfully
used time: 39.750(ms). Execute id is 55302.
SQL> create tablespace CNDBA4 DATAFILE '/dm/dmdbms/data/DCP/CNDBA4_01.dbf' size 128;
executed successfully
used time: 38.049(ms). Execute id is 55303.
SQL> create user CNDBA identified by dameng123 default tablespace CNDBA1;
executed successfully
used time: 4.349(ms). Execute id is 55304.
SQL> grant public,resource,dba to cndba;
executed successfully
used time: 3.058(ms). Execute id is 55305.
SQL>
2.2 创建范围分区表
create
table CNDBA."T1"
(
"ID" INT,
"NAME" VARCHAR(20)
)
PARTITION BY RANGE
(
"ID"
)
(
PARTITION "P1" VALUES LESS THAN (100) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "CNDBA1"),
PARTITION "P2" VALUES LESS THAN (200) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "CNDBA2"),
PARTITION "P3" VALUES LESS THAN (300) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "CNDBA3"),
PARTITION "PN" VALUES LESS THAN (MAXVALUE) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "CNDBA4")
)
storage
(
initial 1 ,
next 1 ,
minextents 1,
fillfactor 0
) ;
查看分区表信息：
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t;
LINEID TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- ---------- ----------------- --------------------
1 T1 RANGE 4
used time: 15.389(ms). Execute id is 55401.
SQL> select table_name,partition_name,high_value from all_tab_partitions;
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 P1 100
2 T1 PN MAXVALUE
3 T1 P3 300
4 T1 P2 200
used time: 26.522(ms). Execute id is 55402.
SQL>
插入测试数据：
begin
for i in 1..299
loop
insert into cndba.t1 values
(i, 'EEEE'||i
);
commit;
end loop;
end;
/
2.3 创建 LIST 分区表
创建分区表：
create table sales(
sales_id int,
saleman char(20),
saledate datetime,
city char(10)
)
partition by list(city)(
partition p1 values ('合肥', '巢湖'),
partition p2 values ('安庆', '怀宁', '太湖'),
partition p3 values ('广州', '深圳'),
partition p_default values (default)
);
查看分区表：
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t;
LINEID TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- ---------- ----------------- --------------------
1 T1 RANGE 4
2 SALES LIST 4
used time: 10.377(ms). Execute id is 55603.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='SALES';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- --------------------------------------
1 SALES P_DEFAULT DEFAULT
2 SALES P3 '广州 ','深圳 '
3 SALES P2 '安庆 ','怀宁 ','太湖 '
4 SALES P1 '合肥 ','巢湖 '
used time: 16.370(ms). Execute id is 55604.
SQL>
2.4 创建哈希分区表
create table sales01(
sales_id int,
saleman char(20),
saledate datetime,
city char(10)
)
partition by hash(city)(
partition p1,
partition p2,
partition p3,
partition p4
);
如果不需指定分区表名，可以通过指定哈希分区个数来建立哈希分区表。
这种方式建立的哈希分区表分区名统一使用DMHASHPART+分区号（从 0 开始）作为分区名。
Create table sales02(
id int,
name varchar(20)
)
partition by hash (id)
partitions 10;
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t;
LINEID TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- ---------- ----------------- --------------------
1 T1 RANGE 4
2 SALES LIST 4
3 SALES01 HASH 4
4 SALES02 HASH 10
used time: 4.353(ms). Execute id is 55607.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='SALES02';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 SALES02 DMHASHPART9 NULL
2 SALES02 DMHASHPART8 NULL
3 SALES02 DMHASHPART7 NULL
4 SALES02 DMHASHPART6 NULL
5 SALES02 DMHASHPART5 NULL
6 SALES02 DMHASHPART4 NULL
7 SALES02 DMHASHPART3 NULL
8 SALES02 DMHASHPART2 NULL
9 SALES02 DMHASHPART1 NULL
10 SALES02 DMHASHPART0 NULL
10 rows got
2.5 创建组合分区表
SQL> drop table sales;
SQL> create table sales(
2 sales_id int,
3 saleman char(20),
4 saledate datetime,
5 city char(10)
6 )
7 partition by list(city)
8 subpartition by range(saledate) subpartition template(
9 subpartition p11 values less than ('2012-04-01'),
10 subpartition p12 values less than ('2012-07-01'),
11 subpartition p13 values less than ('2012-10-01'),
12 subpartition p14 values equ or less than (maxvalue))
13 (
14 partition p1 values ('北京', '天津')
15 ( subpartition p11_1 values less than ('2012-10-01'),
16 subpartition p11_2 values equ or less than (maxvalue)
17 ),
18 partition p2 values ('上海', '南京', '杭州'),
19 partition p3 values (default)
20 );
executed successfully
used time: 14.861(ms). Execute id is 469.
SQL>
在创建多级分区表时，指定了子分区模板，同时子分区 P1 自定义了子分区描述 P11_1和 P11_2。
P1 有两个子分区 P11_1 和 P11_2。而子分区 P2 和 P3 有四个子分区 P11、P12、P13 和 P14。
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t;
SQL>select table_name,partition_name,high_value,subpartition_count from all_tab_partitions where table_name='SALES';
DM 支持最多八层多级分区。
三级分区的例子：
SQL> create table student(name varchar(20), age int, sex varchar(10) check (sex in ('mail','femail')), grade int check (grade in (7,8,9)))
partition by list(grade)
subpartition by list(sex) subpartition template
(
subpartition q1 values('mail'),
subpartition q2 values('femail')
),
subpartition by range(age) subpartition template
(
subpartition r1 values less than (12),
subpartition r2 values less than (15),
subpartition r3 values less than (maxvalue)
)
(
partition p1 values (7),
partition p2 values (8),
partition p3 values (9)
);
2.6 创建间隔分区
如果用了间隔函数做分区，在数据插入的时候，如果没有适合的分区，数据库分自动给你创建一个新的分区。
create
table t_interval_year
(
employee_id int ,
employee_name varchar(20),
bir date
)
partition by RANGE
(
bir
)
INTERVAL
(
numtoyminterval(1, 'YEAR')
)
(
PARTITION P1990 VALUES LESS THAN(TO_date('1991-01-01', 'YYYY-MM-DD')),
PARTITION P1991 VALUES LESS THAN(TO_date('1992-01-01', 'YYYY-MM-DD')),
PARTITION P1992 VALUES LESS THAN(TO_date('1993-01-01', 'YYYY-MM-DD'))
);
SQL> select table_name,partitioning_type ,partition_count from all_part_tables t;
LINEID TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------- ----------------- --------------------
1 T1 RANGE 4
2 SALES LIST 4
3 SALES01 HASH 4
4 SALES02 HASH 10
5 T_INTERVAL_YEAR RANGE 3
used time: 4.984(ms). Execute id is 55610.
SQL>
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T_INTERVAL_YEAR';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------- ----------------
1 T_INTERVAL_YEAR P1992 DATE'1993-01-01'
2 T_INTERVAL_YEAR P1991 DATE'1992-01-01'
3 T_INTERVAL_YEAR P1990 DATE'1991-01-01'
used time: 17.331(ms). Execute id is 55611.
SQL>
SQL> INSERT INTO T_INTERVAL_YEAR VALUES(1,'dave','1989-10-24');
affect rows 1
used time: 1.146(ms). Execute id is 55612.
SQL> INSERT INTO T_INTERVAL_YEAR VALUES(2,'cndba','1993-10-24');
affect rows 1
used time: 5.518(ms). Execute id is 55613.
SQL> INSERT INTO T_INTERVAL_YEAR VALUES(3,'hefei','1994-10-24');
affect rows 1
used time: 5.138(ms). Execute id is 55614.
SQL> commit;
executed successfully
used time: 1.258(ms). Execute id is 55615.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T_INTERVAL_YEAR';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------- ----------------
1 T_INTERVAL_YEAR SYS_P1084_1090 DATE'1995-01-01'
2 T_INTERVAL_YEAR SYS_P1084_1088 DATE'1994-01-01'
3 T_INTERVAL_YEAR P1992 DATE'1993-01-01'
4 T_INTERVAL_YEAR P1991 DATE'1992-01-01'
5 T_INTERVAL_YEAR P1990 DATE'1991-01-01'
used time: 16.527(ms). Execute id is 55616.
SQL>
3 维护水平分区表
DM 数据库的分区表有如下维护操作：
- 增加分区
- 删除分区
- 合并分区：将相邻的两个范围分区合并为一个分区。只能在范围分区上进行合并分区。
- 拆分分区：将某一个范围分区拆分为相邻的两个分区。只能在范围分区上进行拆分分区；
- 交换分区：将分区数据跟普通表数据交换功能，普通表必须跟分区表同构（拥有相同的列和索引）。不支持含有加密列的分区表交换分区。
3.1 删除分区
只能对范围分区和 LIST 分区进行删除分区，哈希分区不支持删除分区。
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 P1 100
2 T1 PN MAXVALUE
3 T1 P3 300
4 T1 P2 200
used time: 18.311(ms). Execute id is 55617.
SQL>
SQL> alter table T1 drop partition PN;
executed successfully
used time: 13.177(ms). Execute id is 55618.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 P1 100
2 T1 P3 300
3 T1 P2 200
used time: 17.835(ms). Execute id is 55619.
SQL>
3.2 增加分区
SQL> alter table t1 add partition pn values less than(maxvalue);
executed successfully
used time: 5.423(ms). Execute id is 55620.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 PN MAXVALUE
2 T1 P1 100
3 T1 P3 300
4 T1 P2 200
used time: 13.049(ms). Execute id is 55621.
SQL>
3.3 合并分区
仅范围分区表支持合并分区，并且合并的分区必须是范围相邻的两分区。
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 PN MAXVALUE
2 T1 P1 100
3 T1 P3 300
4 T1 P2 200
used time: 13.049(ms). Execute id is 55621.
SQL> alter table T1 merge partitions p1, p2 into partition p1_2;
executed successfully
used time: 21.121(ms). Execute id is 55622.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 PN MAXVALUE
2 T1 P3 300
3 T1 P1_2 200
used time: 14.320(ms). Execute id is 55623.
SQL>
3.4 拆分分区
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 PN MAXVALUE
2 T1 P3 300
3 T1 P1_2 200
used time: 14.320(ms). Execute id is 55623.
SQL> alter table T1 split partition p1_2 at(100) into (partition p1,partition p2);
executed successfully
used time: 25.909(ms). Execute id is 55624.
SQL> select table_name,partition_name,high_value from all_tab_partitions where table_name='T1';
LINEID TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------
1 T1 P2 200
2 T1 P1 100
3 T1 PN MAXVALUE
4 T1 P3 300
used time: 14.427(ms). Execute id is 55625.
3.5 交换分区
仅范围分区和 LIST 分区支持交换分区，哈希分区表不支持。
SQL> create table cndba(
name char(15),
salary int)
partition by range(salary)(
partition p1 values less than (5000),
partition p2 values less than (10000),
partition p3 values less than (15000),
partition p5 values equ or less than (maxvalue)
);
SQL> create table cndba_tmp(
name char(15),
salary int
)
;
SQL> insert into cndba_tmp values('dave',8888);
SQL> commit;
SQL> select * from cndba;
SQL> select * from cndba_tmp;
--交换分区
SQL> alter table cndba exchange partition p2 with table cndba_tmp;
#查询验证：
SQL> select * from cndba_tmp;
SQL> select * from cndba partition(p2);
注意：
以上操作都可以通过DMManager工具在图形界面进行。
版权声明：本文为博主原创文章，未经博主允许不得转载。
