千里之行始于足下,梦想不付之行动,终究是纸上谈兵
经过一段时间的培训学习了好多支持点。让我对达梦的理解更加深刻。
说到数据库表,那么达梦有那些表呢?
达梦数据库有:表、分区表、临时表、堆表(rowid)、列存表(HUGE)、外部表等
默认的表是索引组织部表。
规划表的原则:
1、权限(create table)创建表权限
2、命名规则 表名称
3、存储位置(tablespace)放在那个表空间
4、作用(数据类型)
5、约束
6、注释
8、应用的架构
堆表
堆表的好处:并发插入的性能非常高。
索引组织表:在插入的时候会自动建立索引,性能稍微低。
堆表带分支(存储方式:一个树分支)二叉树。
create user test IDENTIFIED BY dameng123; --创建一个数据库用户;
alter user test DEFAULT TABLESPACE TEST; --更换用户表空间
CREATE TABLE TEST.T1 (ID INT,NAME VARCHAR(20)) STORAGE ( BRANCH(4,2)); --创建一个堆表 带分支 4-2
SP_TABLEDEF('TEST','T1'); --通过系统过程查看表结构
表结构如下:
CREATE TABLE "TEST"."T1"
(
"ID" INT,
"NAME" VARCHAR(20)) STORAGE(ON "TEST", BRANCH(4, 2)) ;
CREATE TABLE TEST.T2 (ID INT,NAME VARCHAR(20)); --创建一个数据库默认的表,索引组织表
SP_TABLEDEF('TEST','T2'); --通过系统过程查看表结构
表结构如下
CREATE TABLE "TEST"."T2"
(
"ID" INT,
"NAME" VARCHAR(20)) STORAGE(ON "TEST", CLUSTERBTR) ;
CREATE TABLE TEST.T3 (ID INT,NAME VARCHAR(20))STORAGE (NOBRANCH); --创建一个不带分支的索引组织表;
SP_TABLEDEF('TEST','T3'); --通过系统过程查看表结构
/* 表结构如下
CREATE TABLE "TEST"."T3"
(
"ID" INT,
"NAME" VARCHAR(20)) STORAGE(ON "TEST", NOBRANCH) ;
*/
----通过一个参数可以查看默认创建的表是否为索引组织表、堆表。
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LIST_TABLE';
----LIST_TABLE 参数:默认情况下是0 0为索引组织表 1为堆表
SP_SET_PARA_VALUE(1,'LIST_TABLE',1); --通过系统过程修改创建表时默认为堆表
SP_SET_PARA_VALUE(1,'LIST_TABLE',0);--通过系统过程修改创建表时默认为索引组织表
–堆表限制:
–1、不支持聚集索引 如果用户需要借助聚集索引主键堆数据进行排序,是不推荐使用堆表。
–2、不支持列存储 由于列存储采用了不同方式对表进行物理存储
范围分区
DROP TABLE TEST.T_R1;
CREATE TABLE TEST.T_R1 (ID INT ,NAME VARCHAR(20))
PARTITION BY RANGE (ID)
(PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200)); ----创建范围分区表,P1分区最大100 P2分区ID列最大200
SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME='T_R1'; ---查询T_R1这张表是否为分区表。
SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS; ---查询T_R1有分区表有几个分区
BEGIN
FOR I IN 1..150 LOOP
INSERT INTO TEST.T_R1 VALUES (I,'AA'||I);
COMMIT;
END LOOP;
END; --循150次环写入到TEST.T_R1 表
SELECT * FROM TEST.T_R1; ---查询TEST.T_R1表
SELECT * FROM TEST.T_R1 PARTITION(P1); ----查询P1分区表数据
SELECT * FROM TEST.T_R1 PARTITION(P2); ----查询P2分区表数据
INSERT INTO TEST.T_R1 VALUES (300,'AAA300'); ---插入数据提示没有合适的分区
SELECT * FROM SYS.DBA_TAB_PARTITIONS WHERE TABLE_NAME='T_R1'; ---查询T_R1表最高值是多少
ALTER TABLE TEST.T_R1 ADD PARTITION PN VALUES LESS THAN (MAXVALUE); --修改分区表T_R1 增加PN分区表最大值MAXVALUE
INSERT INTO TEST.T_R1 VALUES (300,'AAA300'); ---插入数据成功!!
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='T_R1';
创建堆表的分区表
CREATE TABLE TEST.T_R2(SID INT,NAME VARCHAR(20))
PARTITION BY RANGE (SID)
(PARTITION P1 VALUES LESS THAN (100) TABLESPACE TBS1,
PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS2 ) STORAGE (NOBRANCH); -----提示:水平分区堆表各子表必须位于同一个表空间
解决办法:
CREATE TABLE TEST.T_R2(SID INT,NAME VARCHAR(20))
PARTITION BY RANGE (SID)
(PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200)) STORAGE (ON TBS1 , NOBRANCH);
----堆表不支持把不同分区放在不同表空间。
创建索引组织表的分区表,放在不同的表空间
CREATE TABLE TEST.T_R3 (SID INT,NAME VARCHAR(20))
PARTITION BY RANGE(SID)
(PARTITION P1 VALUES LESS THAN (100) TABLESPACE TBS1,
PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS2 );
ALTER TABLE TEST.T_R3 ADD PARTITION PN VALUES LESS THAN (MAXVALUE) TABLESPACE TBS3;
列表分区
列表分区:适合适合字符串类型的。
```sql
CREATE TABLE TEST.T_R4 AS SELECT E.EMPLOYEE_ID,E.EMPLOYEE_NAME,J.JOB_TITLE FROM DMHR.EMPLOYEE E,DMHR.JOB J WHERE E.JOB_ID = J.JOB_ID;
SELECT DISTINCT T_R4.JOB_TITLE FROM TEST.T_R4;
CREATE TABLE TEST.T_R5 ( ID INT ,NAME VARCHAR(20),JOB_TITLE VARCHAR(20))
PARTITION BY LIST(JOB_TITLE)
(PARTITION P1 VALUES ('总经理','总经理助理','秘书')TABLESPACE TBS1,
PARTITION P2 VALUES ('行政部经理','开发部经理','项目经理','市场部经理','技术支持部经理','测试部经理','人力资源部经理')TABLESPACE TBS2,
PARTITION P3 VALUES (DEFAULT)TABLESPACE TBS3);
列表分区不支持拆分分区,后期不能进行拆分。
CREATE TABLE TEST.T_R6 ( ID INT ,NAME VARCHAR(20),JOB_TITLE VARCHAR(20))
PARTITION BY LIST(JOB_TITLE)
(PARTITION P1 VALUES ('总经理','总经理助理','秘书')TABLESPACE TBS1,
PARTITION P2 VALUES ('行政部经理','开发部经理','项目经理','市场部经理','技术支持部经理','测试部经理','人力资源部经理')TABLESPACE TBS2);
列表分区如果不创建default分区,不会把其他数据写入到数据表中。后期只能添加default 分区。
ALTER TABLE TEST.T_R6 ADD PARTITION P4 VALUES (DEFAULT) TABLESPACE TBS3;
--查询T_R6的分区表属性。
SELECT * FROM SYS.DBA_TAB_PARTITIONS WHERE TABLE_NAME= 'T_R6';
--查询子分区数据
SELECT * FROM TEST.T_R6 PARTITION (P1);
哈希分区
哈希分区的特点:存数据非常快,取数据很慢,所有数据是平均分配到各个子分区。
CREATE TABLE TEST.T_R7 (ID INT,NAME VARCHAR(20),JOB_TITLE VARCHAR(20))
PARTITION BY HASH (ID) PARTITIONS 10;
创建表为哈希分区表为10个分区。注意:这里使用的PARTITIONS 这个关键字比较方便。如果不适用这个关键字就需要用到一下脚本。
create table "TEST"."T_R8"
(
"ID" INT,
"NAME" VARCHAR(50),
"JOB_TITLE" VARCHAR(50)
)
PARTITION BY HASH ("ID")
(
PARTITION "PART_1",
PARTITION "PART_2",
PARTITION "PART_3",
PARTITION "PART_4",
PARTITION "PART_5",
PARTITION "PART_6",
PARTITION "PART_7",
PARTITION "PART_8",
PARTITION "PART_9",
PARTITION "PART_10"
);
---查询分区表的属性
SELECT * FROM SYS.DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'T_R7'
SELECT COUNT(*) FROM "TEST"."T_R7_DMHASHPART9"
—查询数据是否平均分配 注意:这里需要注意的是需要在模式面加.来直接跟表的分区的名称进行查看。
间隔分区
间隔分区的特点:通过改功能,可以在输入相应分区的数据库自动创建相应的分区。
在达梦中我也可以使用间隔分区,不过需要注意的是达梦有一下限制:
1、仅支持一级范围分区创建间隔分区,只能有一个分区列,且分区列类型为日期或者数值。
2、对间隔分区进行SPLIT,只能在间隔范围内进行操作;
3、被SPLIT/MERGE的分区其他侧分区不在进行自动创建。
4、表定义不能包含MAXVALUE分区;
6、不允许新增分区,不能删除起始间隔分区;
7、间隔分区表定义语句显示到起始间隔分区位置,自动生成的间隔分区均不包含边界值。
8、间隔表达式只能为常量或日期间隔函数。日期间隔函数为: NUMTOYMINTERVAL(1,‘year’), NUMTOYMINTERVAL(1,‘month’) NUMTODSINTERVAL(1,‘day’)
CREATE TABLE TEST.T_R8 (
EMPLOYEE_ID INT,
EMPLOYEE_NAME VARCHAR(20),
BIRTHDAY DATE
)
PARTITION BY RANGE (BIRTHDAY)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
PARTITION P2020 VALUES LESS THAN (TO_DATE('2021-01-01 00:00:0'.'YYYY-MM-DD HH24:MI:SS'))
create table "TEST"."T_R8"
(
"EMPLOYEE_ID" INT not null ,
"EMPLOYEE_NAME" CHAR(10),
"BIRTHDAY" DATE
)
PARTITION BY RANGE ("BIRTHDAY") interval(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION "PART_1" VALUES LESS THAN (TO_DATE('2021-01-01 00:00:0','YYYY-MM-DD HH24:MI:SS')))
SELECT TO_DATE('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
SELECT TRUNC(TO_DATE(SYSDATE(),'YYYY-MM-DD'));
SELECT COUNT(*) FROM TEST.T_R8 PARTITION (PART_1);
非分区表变成分区表
1、把非分区表变成分区表需要把数据导出后,删除原表或者从新建分区表,把原有数据导出后在导入新的表中
*/
DROP TABLE TEST.T_R9; --删除表
CREATE TABLE TEST.T_R9(ID INT,NAME VARCHAR(20));----创建一张普通表。
begin
for i in 1..100000 LOOP
insert into test.T_R9 VALUES(i,'AAA'||i);
END LOOP;
COMMIT;
END; ------------写入数据到这张T_R9表。
--导出T_R9这张表。通过dexp 或者dimp 进行导出导入
./dexp SYSDBA/SYSDBA@192.168.1.11:5236 FILE=/dm7/dexp.dmp tables=test.t_r9
./dimp SYSDBA/SYSDBA@192.168.1.11:5236 FILE=/dm7/T_R9.dmp TABLES=TEST.T_R9 IGNORE=Y
CREATE TABLE TEST.T_R9 (
ID INT,
NAME VARCHAR(20))
PARTITION BY RANGE(ID)
(PARTITION P1 VALUES LESS THAN (10000),
PARTITION P2 VALUES LESS THAN (20000),
PARTITION P3 VALUES LESS THAN (30000),
PARTITION PN VALUES LESS THAN (MAXVALUE));
select * from test.T_R9
insert into TEST.T_R9 values (500000 ,' ')
sf_rest_temp_ts()
临时表
1、在达梦里面不可以创建临时表空间,用户不能手动创建临时表空间。
2、可以创建临时表
3、用户可以通过系统函数 sf_rest_temp_ts 释放表空间
4、通过 select * from v$dm_ini where para_name link ‘%TEMP%’;
5、通过sp_set_para_value(2,‘temp_size’,200) 设置临时表空间大小。
6、临时表空间文件在磁盘占用大小不会缩减,用户可以通过sf_rest_temp_ts来进行磁盘空间清理。
7、TEMP表空间完全由达梦数据库自动维护。
----临时表分为两种级别:(事务级,会话级):
--1、on commit delete rows:临时表是事务级的,每次事务提交或回滚之后,表中的所有数据被删除。
--2、on commit paresrve rows:指定临时表是会话级,会话结束的时候清空表。
CREATE GLOBAL TEMPORARY TABLE TEST.T_R10 (ID INT,NAME VARCHAR(20))
ON COMMIT PRESERVE ROWS; --创建基于会话级的临时表。
CREATE GLOBAL TEMPORARY TABLE TEST.T_R11 (ID INT , NAME VARCHAR(20))
ON COMMIT DELETE ROWS; --创建基于事务级临时表。
SELECT * FROM SYS.DBA_TABLES WHERE DBA_TABLES.TABLE_NAME LIKE 'T_R1_%';
清空表
有些情况下,当表的数据库不在使用时,需要删除表的所有的行,DM7支持一下方式来删除表中的行。
1、使用delete 语句 使用delete 语句可以回滚 ,删除视图可以使用delete
2、使用drop 或者create语句 使用drop 删除表会把表空间的占用进行释放,会删除整个表。
3、使用truncate语句 使用truncate 不可回滚 不能恢复,除非通过备份进行还原。
表空间释放 drop>truncate>delete
应用范围:truncate 只能对表进行操作。 delete也可以是表 也可以是 视图 。 truncate 和delete 只能删除数据。
drop则删除整个表(包括表结构和数据。)
列存表 (HUGE)
1、HUGE FILE SYSTEM:是大梦数据自有的,针对海量数据进行分析一种高效的表,列存储表是建立在HFS(分层文件管理系统)上的。
2、HUGE 表建立在自己特有的表空间上HUGE表空间,默认在HMAIN表空间上 。
3、相关信息存在V$huge_tablespace中。最多可以创建32767个huge表空间。
4、创建HUGE表时,根据with和without 来确定表是非事务还是事务型的HUGE表。
5、HUGE表与普通行表一样,可以进行增删改操作。
6、但HUGE表的删除与更新操作的效率会比行表低一些,并发操作性能会比行差一些。
HUGE表的一些限制:
1、支持定义 NULL , NOTNULL, UNIQUE ,PRIMARY KEY
2、 HUGE表不建立聚族索引,允许建立二级索引,不支持位图索引, 其中unique不检查唯一性。
3、不支持表空间限制
4、不支持大字段列
5、不支持全文索引
6、不支持建立触发器
7、不允许建触发器
8、不允许垂直分区
create HUGE TABLESPACE HTS PATH '/dm7/data/DAMENG/HTS/'; --创建一个HUGE表空间。
CREATE HUGE TABLE TEST.H1(ID INT,NAME VARCHAR(20)) STORAGE (WITHOUT DELTA); ---非事务的大表
CREATE HUGE TABLE TEST.H2 (ID INT,NAME VARCHAR(20))STORAGE ( ON HTS ,WITH DELTA) ---事务型大表
SP_TABLEDEF('TEST','H1') --查看表空间
begin
for i in 1..100000 loop
insert into test.h1 values (i ,'AAA'||i);
commit;
end loop;
end;