DM8 表管理

标签：PARTITION 管理分区 partition test values DM8 table

1 堆表

达梦默认的表索引组织表, oracle 默认的是堆表rowid

达梦既支持索引组织表，又支持堆表。

堆表好处：并发插入性能高

缺点：查询效率低。

带分支，不带分支的。

创建索引组织表：

create table a1(id int)；

创建堆表：

create table A2(id int, name varchar(20)) storage(initial 1, next 1, branch(4,2));

No branch:不带分支的堆表

Branch(4,2) ：表示4 个并发分支，2 个非并发分支。

(1<=Branch<=64, 1<=nobranch<=64)。

DM8 表管理_数据

索引组织表和堆表的索引类型

DM8 表管理_分区表_02

堆表的索引类型：FLAT

索引组织表索引类型：CLUSTER

List_table 参数：创建的表是否为堆表：0 ：否，1 ：是。

DM8 表管理_表分区_03

sp_set_para_value(1,’LIST_TABLE’,1);

堆表有一些限制：

1 、DM 暂不支持堆表的列存储

2 、对表进行 alter 操作，数据记录 ROWID 可能发生改变，会引起索引重建。

2 分区表

分区表的优点：

1 、改善了查询性能

2 、增加了可用性

3 、维护方便

4 、均衡I/O

分区表的类型：水平分区

水平分区类型：范围分区，列表分区，哈希分区，间隔分区

目的：把一张超大表，逻辑拆分出多张小表，分散i/o,方便管理，提高数据查询和DML 操作的效率。

2.1 范围分区

1 、要求：分区列是数字或是日期类型

create tablespace TEST datafile '/dm8/data/DAMENG/test_01.dbf' size 512;

create user TEST identified by dameng123 default tablespace test; 创建范围分区

create table "TEST"."T1"

(

"ID" INT,

"NAME" VARCHAR(20))

PARTITION BY RANGE ("ID")

(

PARTITION "P1" VALUES LESS THAN (100), ###p1<100

PARTITION "P2" VALUES LESS THAN (200), ####100<=p2<200

PARTITION "P3" VALUES LESS THAN (300) ####200<=p3<300

)

storage(initial 1, next 1, minextents 1, fillfactor 0)

;

DM8 表管理_表分区_04

DM8 表管理_分区表_05

begin

for i in 1..299 loop

insert into test.t1 values(i,'EEEE' | |i);

commit;

end loop;

end;

访问分区表；

DM8 表管理_分区表_06

insert into test.t1 values(300, 'cccc300');

DM8 表管理_表分区_07

alter table test.t1 add partition pn values less than(maxvalue);

insert into test.t1 values(300,'CCC300');

commit;

案例2：

create tablespace TBS1 DATAFILE '/dm8/data/DAMENG/TBS1_01.DBF' size 32;

create tablespace TBS2 DATAFILE '/dm8/data/TBS2_01.DBF' size 32; create tablespace TBS3 DATAFILE '/dm8/TBS3_01.DBF' size 32;

create tablespace TBS4 DATAFILE '/dm8/data/DAMENG/TBS4_01.DBF' size 32;

创建范围分区表

create table "TEST"."T2"

(

"ID" INT,

"NAME" VARCHAR(20)

)

PARTITION BY RANGE ("ID")

(

PARTITION "P1" VALUES LESS THAN (100) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS1"),

PARTITION "P2" VALUES LESS THAN (200) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS2"),

PARTITION "P3" VALUES LESS THAN (300) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS3"),

PARTITION "PN" VALUES LESS THAN (MAXVALUE) STORAGE( initial 1, next minextents 1, fillfactor 0, on "TBS4")

)

storage(initial 1, next 1, minextents 1, fillfactor 0)

;

案例3 ：堆表的分区表

create table "TEST"."T3"

(

"ID" INT,

"NAME" VARCHAR(20)

)

PARTITION BY RANGE ("ID")

(

PARTITION "P1" VALUES LESS THAN (100) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS1"),

PARTITION "P2" VALUES LESS THAN (200) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS2"),

PARTITION "P3" VALUES LESS THAN (300) STORAGE( initial 1, next 1, minextents fillfactor 0, on "TBS3"),

PARTITION "PN" VALUES LESS THAN (MAXVALUE) STORAGE( initial 1, next minextents 1, fillfactor 0, on "TBS4")

)

storage(initial 1, next 1, minextents 1, fillfactor 0, nobranch)

;

DM8 表管理_表分区_08

create table "TEST"."T3"

(

"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 "TEST"),

PARTITION "P2" VALUES LESS THAN (200) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "TEST"),

PARTITION "P3" VALUES LESS THAN (300) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "TEST"),

PARTITION "PN" VALUES LESS THAN (MAXVALUE) STORAGE( initial 1, next 1, minextents 1, fillfactor 0, on "TEST")

)

storage(initial 1, next 1, minextents 1, fillfactor 0, nobranch)

;

2.2 列表分区

分区列适合字符串类型

北京天津哈尔滨，青岛

上海，南京，杭州

武汉，长沙，合肥

广州，深圳，福建

create table test.t_sales(sales_id int, saleman varchar(20), saledate date,city

char(10))

Partition by list(city)

(partition P1 values('北京', ' 天津',' 哈尔滨','青岛'),

partition P2 values('上海','南京','杭州'),

partition P3 values('武汉','长沙','合肥'),

partition P4 values('广州','深圳','福建'));

DM8 表管理_数据_09

alter table test.t_sales add partition PN values(default);

insert into test.t_sales values(4,'CCCC','2022-04-18','西安');

commit;

SELECT * FROM test.T_SALES;

2.3 哈希分区

将分区列的值进行hash 运算，然后将数据平均分配各个子分区，存储数据非常快，取数据慢，如果hash 分区不指定分区表名，那就通过指定的哈希分区数来创建。

分区表名统一使用DMHASHPART+分区号(从 0 开始) 作为分区名。

CREATE TABLE TEST.T4

(ID INT,

NAME VARCHAR(20))

PARTITION BY HASH(ID)

( PARTITION "01",

PARTITION "02",

PARTITION "03",

PARTITION "04"

) ;

create table test.t_hash(id int, name varchar(20))

partition by hash (id) partitions 10;

2.4 组合分区

RANGE-LIST RANGE-HASH RANAGE-RANGE LIST-LIST HASH-HASH

LIST-RANGE..........

DM 支持最多8 层分区

List-range

create table test.sales_sum(sale_id int, sale_name varchar(20), sale_date date, city char(10))

partition by list(city) ----主分区

subpartition by range(sale_date) ---子分区

subpartition template( ---子分区模板

subpartition p11 values less than('2012-04-01'),

subpartition p12 values less than('2013-04-01'),

subpartition p13 values less than(maxvalue))

( partition p1 values('北京', '天津',' 哈尔滨','青岛') ---创建组合分区

(subpartition p1_1 values less than('2012-08-01'),

subpartition p1_2 values less than('2013-08-01'),

subpartition p1_3 values less than(maxvalue)),

partition p2 values('上海','南京','杭州'),

partition p3 values('武汉','长沙','合肥'),

partition p4 values('广州','深圳','福建'),

partition p5 values(default));

insert into test.sales_sum values(1,'AAAAA','2012-05-01','北京');

insert into test.sales_sum values(2,'CCCC','2012-05-01','武汉');

COMMIT;

DM8 表管理_表分区_10

2.5 间隔分区

NUMTOYMINTERVAL(N,INTERVAL_UNIT);

N:数据类型

Interval_unit:year, month

实际上是范围分区的一个扩展

SELECT sysdate+numtoyminterval(2,'YEAR');

SELECT sysdate+numtoyminterval(2,'MONTH');

如果用了间隔函数做分区，在数据插入的时候，如果没有适合的分区，

数据库分自动给你创建一个新的分区。

create table test.t6_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')));

INSERT INTO TEST.T6_INTERVAL_YEAR VALUES(1,'RRRR','1989-10-24');

INSERT

INTO

TEST.T6 INTERVAL YEAR

VALUES(2,'GGGGG','1993-10-24');

INSERT

INTO

TEST.T6 INTERVAL YEAR

VALUES(2,'GGGGG','1994-10-24');

INSERT

INTO

TEST.T6 INTERVAL YEAR

VALUES(2,'GGGGG','2020-08-24');

COMMIT;

2.6 分区表维护

相关的数据字典：dba_tab_partitions;

SELECT * FROM dba_tab_partitions WHERE table_owner=’TEST’;

1 、增加分区

ALTER TABLE TEST.T1 add partition pn values less than(maxvalue); 2 、删除分区

Alter table test.t1 drop partition pn;

注意；删除分区前请确认好，并做好备份。

3 、合并分区

alter table test.t1 merge partitions p2,p3 into partition p2_3;

4 、拆分分区

alter table test.t1 split partition P2_3 at(200 into (partition p2,partition p3);

5 、交换分区

ALTER TABLE TEST.T1 EXCHANGE PARTITION P2 WITH TABLE TEST.T10; 注意：交换时，会普通表的所有数据全部交换过来，包括不属于这个分区的数据也一起交换过来,所以我们在交换分区的时候，要把普通表的数据先整理好。

6 、如何把非分区表转换成分区表

1)把非分区表的数据导出来。dexp dts.

2) 建立一个和非分区表结构一样的分区表

3) 将数据导入到分区表中dimp remap_schema) create table test.t20(id int)

begin

for i in 1..10000 loop

insert into test.t20 values(i);

end loop;

commit;

end;

SELECT * FROM test.t20

导出t20 表的数据。

[dmdba@localhost bin]$ ./dexp sysdba/dameng123 file=/dm8/t20.dmp tables=test.t20;

删除t20 表

DROP TABLE TEST.T20;

创建分区表t20

create table test.t20(id int)

partition by range (id)

(partition p1 values less than (1000),

partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000), partition p5 values less than(5000), partition p6 values less than(6000), partition p7 values less than(7000), partition pn values less than(maxvalue));

[dmdba@localhost bin]$ ./dimp sysdba/dameng123 file=/dm8/t20.dmp tables=test.t20 ignore=y

SELECT count(*) FROM test.t20;

2.2.7 总结

DM8 表管理_数据_11

DM8 表管理_分区表_12

DM8 表管理_分区表_13

DM8 表管理_表分区_14

范围分区：

每一个分区都必须有一个VALUES LESS THAN 子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

在最高的分区中，MAXVALUE 被定义。MAXVALUE 代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于

任何分区中指定的VALUE LESS THAN 的值，同时包括空值。

Hash 分区

分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O 设备上进行散列分区，使得这些分区大小一致。

建议分区的数量采用2 的n 次方，这样可以使得各个分区间数据分布更加均匀。

列表分区：

通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。

间隔分区：

间隔分区(interval partitioning) 它就是以一个区间分区表为“起点”，并在定义中增加了一个规则分区。

间隔分区关键字：numtoyminterval (年，月)

3 外部表

1 、定义

外部表，是指不存在于数据库中的表。通过向达梦提供描述外部表的元数据，可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。

外部表的数据存储在操作系统中，是操作系统文件，建立外部表的时候，不会产生段，页簇等存储结构，只有与表相关的定义放在数据字典中。在数据库中不能对外部表的内容进行修改，不能对外部表建立

主键，索引，外键。不需要将外部表的数据载入到数据库中来，通过

sql 解码器来访问外部表。

2 、如何建立外部表：

DM8 表管理_分区表_15

Create external table <表名><表结构定义> FROM <控制文件路径> | < 数据文件路径><参数>；

案例1：

1 、数据文件

[dmdba@localhost ext]$ cat a.txt

1,AAAAA

2,BBBBB

3,DD

4,CCCC

5,FF

6,WERWRWRW

7,iiiii

8,nnnnn

9,SER

10,EWDEWRWRWRWRW

2 、控制文件

[dmdba@localhost ext]$ cat a.ctl

LOAD DATA

INFILE '/dm8/ext/a.txt'

INTO TABLE TEST.EXT

FIELDS ','

3 、创建外部表

SQL> create external table test.ext(id int, name varchar(20)) FROM '/dm8/ext/a.ctl';

DM8 表管理_表分区_16

DM8 表管理_分区表_17

案例2：

1 、数据文件

[dmdba@localhost ext]$ cat b.txt

10 |9 |7

4 |3 |2 |5

1 |3 |4 |5

6 |7

2 、创建外部表

SQL> create external table test.ext2(c1 int, c2 int, c3 int) FROM datafile '/dm8/ext/b.txt' parms(fields delimited by ' |');

DM8 表管理_数据_18

使用说明

1. <表名>指定了所要建立的外部基表名。如果<模式名>缺省，则缺省为当前模式。表名需要是合法的标识符，且满足SQL 语法要求；

2. 外部表的表名最大长度为128 个字符；

3. 所建外部基表至少要包含一个<列名>指定的列，在一个外部基表中，各<列名>不得相同。一张外部基表中至多可以包含2048 列；

4. 外部基表不能存在大字段列；

5. 外部基表不能存在任何约束条件；

6. 外部基表不能为临时表，不能建立分区；

7. 外部基表上不能建立任何索引；

8. 外部基表是只读的，不存在表锁，不允许任何针对外部表的增删改数据操作，不允许 TRUNCATE 外部表操作；

9. 控制文件路径，以及数据文件路径建议采用绝对路径；

2.3 临时表

临时表就是用来暂时保存临时数据据库对象，它和普通表有些类似，然而又有很大区别。它只能存储在临时表空间，而非用户的表空间。临时表是会话或事务级别的，只对

当前会话或事务可见。每个会话只能查看和修改自己的数据。 DM 临时表支持以下功能：

1 、在临时表中，可以像普通的永久表一样更新，插入和删除数据。

2 、临时表的dml 操作产生较少的redo 日志。

3 、临时表支持建索引，以提高查询性能。

4 、在一个会话或是事务结束后，数据将自动从临时表中删除。

5 、不同用户访问相同的临时表，每个用户只能看到自己数据。

6 、临时表的表结构在数据删除后仍然存在的。

7 、临时表的权限管理和普通表一致。临时表有两种类型：

会话级的临时表

事务级的临时表

Commit 时默认情况下是事务级的。

Oracle 有临时表空间组，包括多个临时表空间，可以手动创建临时表空间。

DM:创建数据库的时候，就创建了临时表空间，不能手动创建临时表空间。

临时表和临时表空间相关参数：

USE_FTTS: 0 产生的临时数据的存放格式，0 ：用临时表空间的数据页来存放，1 ：用临时文件存放。

TEMP_SIZE:10 默认创建的临时表空间大小，以M 为单位。有

效值范围10-1048576M)

temp_space_limit 一定要大于等于temp_size.

TEMP_PATH : 临时表空间路径。

dm.ini 中设置

Enable_tmp_tab_rollback :1 临时表操作是否生成回滚记录，0：不生成，1 生成。

DM8 表管理_分区表_19

创建临时表

1 、事务级别：on commit detele rows

SQL> create global temporary table test.tmp_t1(id int) on commit delete rows;

SQL> insert into test.tmp_t1 values(1);

SQL> insert into test.tmp_t1 values(2);

SQL> insert into test.tmp_t1 values(3);

SQL> SELECT * FROM test.tmp_t1;

SQL> commit;

SQL> SELECT * FROM test.tmp_t1;

2 、会话级别的临时表on commit preserve rows

SQL> create global temporary table test.tmp_t2(id int) on commit preserve rows;

DM8 表管理_分区表_20

重开一个会话

DM8 表管理_表分区_21

查询临时表空间的信息

SELECT para_name,para_value FROM v$dm_ini WHERE para_name like '%TEMP%';

DM8 表管理_数据_22

SQL> SP_SET_PARA_VALUE(2,'TEMP_SIZE',200);

DM8 表管理_表分区_23

SELECTtable_name,tablespace_name FROMdba_tables WHEREtablespace_name='TEMP';

增加临时表空间唯一操作，临时表空间其实是可以自增长的

SP_SET_PARA_VALUE(2,'TEMP_SIZE',2048);//需要重启数据库服务才能生效

DM8 表管理_表分区_24

[dmdba@dm bin]$ ./DmServiceDMSERVER restart

DM8 表管理_数据_25

SELECTtable_name,tablespace_name FROMdba_tables WHEREtablespace_name='TEMP';

DM8 表管理_数据_26

DM8 表管理_数据_27

注意：

临时表清空，临时表空间文件在磁盘所占大小并不会因此缩减，用户可以通过系统函数sp_trunc_ts_file 来进行磁盘空间的清理。 Sp_trunc_ts_file(

Ts_id int ---指定截断文件的临时表空间id

File_id int ---指定截断文件id

To_size int --指定将文件截断至多少，以M 为单位

)

SQL> begin

2 for i in 1..10000000

3 loop

4 insert into test.tmp_t1 values(i);

5 end loop;

6 end;

7 /

DM8 表管理_分区表_28

DM8 表管理_数据_29

SQL> sp_trunc_ts_file(3,0,10);

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 FROM

dba_data_files WHERE tablespace_name='TEMP'；

DM8 表管理_表分区_30

注意：若无法缩小临时表空间，可重启数据库服务，重启后，临时表空间会重新为初始大小，即temp_size 参数指定的大小。

标签：PARTITION,管理,分区,partition,test,values,DM8,table
From： https://blog.51cto.com/u_15130867/5846601

相关文章

赞助商

阅读排行