达梦数据库堆表,分区表,哈希分区表的创建方法
登录disql(bin下或者tool下启动disql)
./disql SYSDBA/dameng123@127.0.0.1:5236
conn SYSDBA/dameng123@127.0.0.1:5236
新建用户test
create user test identified by dameng123
新建表空间TEST(图形化界面)
将用户的表空间修改为TEST
Ø 创建堆表
CREATE TABLE TEST.T1(ID INT,NAME
VARCHAR(20)) STORAGE(BRANCH(4,2)); (堆表)
CREATE TABLE
TEST.T2(IDINT,NAMEVARCHAR(20)); (索引组织表)
Ø 查看堆表和索引组织表的结构
SP_TABLEDEF(‘TEST’,‘T1’);
SP_TABLEDEF(‘TEST’,‘T2’);
Ø 创建不带分支的堆表
CREATE TABLE TEST.T3(ID INT,NAME
VARCHAR(20)) STORAGE (NOBRANCH);
Ø 查看修改list_table参数
select para_name,para_value from v$dm_ini
where para_name=‘LIST_TABLE’;
sp_set_para_value(1,‘LIST_TABLE’,0);
Ø 创建分区表
CREATE TABLE T_R1(ID INT, NAME VARCHAR(20))
PARTITION BY RANGE(ID)
(PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200));
Ø 查询表是否是分区表
SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES
WHERE TABLE_NAME=‘T_R1’;
Ø 查询表中有几个分区
SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME=’T_R1’;
Ø 往 t_r1 表中录入数据
BEGIN
FOR I IN 1…150
LOOP
INSERT INTO T_R1 VALUES(I,‘AA’||I);
COMMIT;
END LOOP;
END;
Ø 查看分区数据
SELECT COUNT(*) FROM T_R1 PARTITION(P2);
Ø 查询分区值限制
INSERT INTO T_R1 VALUES(300,’AA300’);
SELECT TABLE_NAME,
HIGH_VALUE, PARTITION_NAME FROM
DBA_TAB_PARTITIONS WHERE TABLE_NAME=‘T_R1’;
Ø 增加分区:
ALTER TABLE T_R1 ADD PARTITION PN VALUES LESS
THAN(MAXVALUE);
INSERT INTO T_R1 VALUES (300,‘AA300’);
Ø 将不同的分区存储在不同的表空间中
CREATE TABLE T_R3 (SID INT, ID INT, NAME VARCHAR(20))
PARTITION BY RANGE(SID)
(PARTITION P1 VALUES LESS THAN (100) TABLESPACE TBS1,
PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS2);
默认建索引组织表,可以放到两个表空间中
建堆表,水平分区必须放在一个表空间中,会报错,正确建表:
CREATE TABLE T_R2 (SID INT, ID INT, NAME VARCHAR(20))
PARTITION BY RANGE(SID)
(PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200)) STORAGE(ON
TBS1, NO BRANCH);
Ø 创建列表分区表
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 P4 VALUES(‘广州’,‘深圳’))
Ø 增加列表分区
ALTER TABLE SALES ADD PARTITION PN VALUES(DEFAULT);
Ø 创建哈希分区表
CREATE TABLE T_R4(ID INT) PARTITION BY HASH(ID) PARTITIONS 10;
Ø 录入 1-10000 数据到 T_R4 表
BEGIN
FOR I IN 1…10000 LOOP
INSERT INTO T_R4 VALUES(I);
COMMIT;
ENDLOOP;
END;
Ø 创建组合分区
CREATE TABLE TEST.SALES(
SALES_ID INT,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10))
PARTITION BY LIST(CITY)
SUBPARTITION BY RANGE(SALEDATE)
SUBPARTITION TEMPLATE(
SUBPARTITION P11 VALUES LESS THAN(‘2012-04-01’),
SUBPARTITION P12 VALUES EQU OR LESS
THAN(MAXVALUE))
(PARTITION P1 VALUES(‘北京’,‘天津’)
(SUBPARTITION P11_1 VALUES LESS THAN(‘2012-10-01’),
SUBPARTITION P11_2 VALUES EQU OR LESS THAN(MAXVALUE) ),
PARTITION P2 VALUES(‘上海’,‘南京’,‘杭州’),
PARTITION P3 VALUES(DEFAULT) );