达梦数据库分区表与分区索引 达梦数据库分页查询

1 分区表
1.1 默认的表
即索引组织表，无主键利用rowid，并发不好
SQL> select para_name,para_value from v$dm_ini where para_name=‘LIST_TABLE’;

行号 para_name para_value

---

1 LIST_TABLE 0

已用时间: 47.601(毫秒). 执行号:526.

LIST_TABLE：
=0，创建的表为默认的索引组织表
=1，创建的表为堆表
不建议改这个系统参数，想要建堆表就用关键字
1.2 堆表
并发性能好很多（相较于索引组织表），但没有Oracle并发性能强。

#查看表的创建语句

SQL> sp_tabledef (‘SYSDBA’,’##PLAN_TABLE’);

#查看表的创建语句

SQL> select dbms_metadata.get_ddl(‘TABLE’,‘T1’,‘SYSDBA’);

#堆表创建

SQL> create table t2 (id int primary key,name varchar(10)) storage (on main ,nobranch);
操作已执行
已用时间: 75.123(毫秒). 执行号:555.
SQL> sp_tabledef (‘SYSDBA’,‘t2’);

行号 COLUMN_VALUE

---

1 CREATE TABLE “SYSDBA”.“t2” ( “id” INT NOT NULL, “name” VARCHAR(10), NOT CLUSTER PRIMARY KEY(“id”)) STORAGE(ON “MAIN”, NOBRANCH) ;

已用时间: 3.311(毫秒). 执行号:556.

#装载因子设置为80%，预留出数据块中20%的空间用于对付行链接，用来提升性能

SQL> create table t3 (id int primary key,name varchar(10)) storage (on main ,nobranch,fillfactor 80);
操作已执行
已用时间: 11.391(毫秒). 执行号:557.
SQL> sp_tabledef (‘SYSDBA’,‘t3’);
行号 COLUMN_VALUE

---

1 CREATE TABLE “SYSDBA”.“t3” ( “id” INT NOT NULL, “name” VARCHAR(10), NOT CLUSTER PRIMARY KEY(“id”)) STORAGE(FILLFACTOR 80, ON “MAIN”, NOBRANCH) ;
1.3 分区表
物理上是一张大表，逻辑上是N张小表，提高I/O性能
达梦支持的类型：范围分区、哈希分区、列表分区、组合分区
达梦不支持的类型：system分区、参考分区
1.3.1 范围分区
1.3.1.1 创建分区表
案例：创建一个范围分区，一个月一个分区，每个分区存放当月的数据
create tablespace tbs1 datafile ‘/dm8/db/DAMENG/tbs1.dbf’ size 32;
create tablespace tbs2 datafile ‘/dm8/db/DAMENG/tbs2.dbf’ size 32;
create tablespace tbs3 datafile ‘/dm8/db/DAMENG/tbs3.db3’ size 32;

#错误创建1

create table R_T1 (sid int primary key,name varchar(10),bir date)
partition by range (bir)
(partition p1 values less than (‘2019-12-31’) tablespace tbs1,
partition p2 values less than (‘2020-01-31’) tablespace tbs2,
partition p3 values less than (‘2019-02-29’) tablespace tbs3);
第5 行附近出现错误[-2683]:
局部唯一索引必须包含全部分区列

#错误创建2

create table R_T1 (sid int,name
varchar(10),bir date)
partition by range (bir)
(partition p1 values less than (‘2019-12-31’),
partition p2 values less than (‘2020-01-31’),
partition p3 values less than (‘2019-02-29’));
第6 行附近出现错误[-2615]:
非法的分布/分区范围值

#正确创建（不带主键、日期正确）

create table R_T1 (sid int ,name varchar(10),bir date)
partition by range (bir)
(partition p1 values less than (‘2019-12-31’) tablespace tbs1,
partition p2 values less than (‘2020-01-31’) tablespace tbs2,
partition p3 values less than (‘2020-02-29’) tablespace tbs3)

#错误三：用堆表创建所有分区必须在同一个表空间里

create table R_T2 (sid int primary key ,name varchar(10),bir date)
partition by range (bir)
(
partition p1 values less than (‘2019-12-31’) tablespace tbs1,
partition p2 values less than (‘2020-01-31’) tablespace tbs2 ,
partition p3 values less than (‘2020-02-29’) tablespace tbs3
)
storage (nobranch );
第8 行附近出现错误[-2757]:
水平分区堆表各子表必须位于同一个表空间

#正确创建

create table R_T2 (sid int primary key ,name varchar(10),bir date)
partition by range (bir)
(
partition p1 values less than (‘2019-12-31’) ,
partition p2 values less than (‘2020-01-31’) ,
partition p3 values less than (‘2020-02-29’)
)
storage (on tbs1,nobranch );
1.3.1.2 操作表分区

#录入操作

begin
for i in 1…10000 loop
insert into r_t1 values (i,‘aaa’||i,sysdate);
commit;
end loop;
end;
没有找到合适的分区

#查询分区是怎么分的（HIGH_VALUE）

select * from dba_tab_partitions where table_name=‘R_T1’；

#查询分区

select * from r_t1 partition (p2);

#增加分区

alter table r_t1 add partition p4 values less than (‘2020-10-31’);

#删除分区

alter table r_t1 drop partition p4;

#拆分分区

#从 2 月 10 日分界点（分区太大了）数据分布不均匀

alter table r_t1 split partition p3 at(‘2020-02-10’)
into (partition p3_1,partition p3_2);

#合并分区

alter table r_t1 merge partitions p3_1,p3_2 into partition p3;

#交换分区（做分区交换，原表的数据就没有了）

alter table r_t1 exchange partition p2 with table test;
1.3.1.3 非分区表转换成分区表
1、 数据导出
dexp sysdba/SYSDBA file=/home/dmdba/test.dmp tables=test
2、 删除非分区表（或重命名）
alter table test rename to test1;
3、 创建分区表
create table test (sid int ,name varchar(10),bir date)
partition by range (bir)
(partition p1 values less than (‘2019-12- 31’) tablespace tbs1,
partition p2 values less than (‘2020-01- 31’) tablespace tbs2,
partition p3 values less than (‘2020-02- 29’) tablespace tbs3 );
4、 数据导入
dimp sysdba/SYSDBA file=/home/dmdba/test.dmp tables=test IGNORE=y（忽略创建语句）
5、 验证
select count(*) from test;

超出范围的数据如何处理？全部加到pmax这个分区里面去

alter table test add partition pmax values less than (maxvalue);
1.3.1.4 间隔分区

达梦支持

NUMTOYMINTERRVAL 年、月

达梦不支持

NUMTODSINTERVAL 天、小时、周、分

创建一个分区表table01

CREATE TABLE table01 ( id int, name VARCHAR(20), bir DATE )
PARTITION BY RANGE(bir) INTERVAL (NUMTOYMINTERVAL(1,‘month’))
( PARTITION p1 VALUES LESS THAN (‘2020-01-01’),
PARTITION p2 VALUES LESS THAN (‘2020-02-01’) );

查询分区结构，有两个分区

select * from dba_tab_partitions where table_name = ‘table01’;

再插入一条本应录不进去的数据

insert into table01 values (1,‘aaa’,‘2020-05-01’);

再次查询分区结构，发现系统自动创建了一个分区

select * from dba_tab_partitions where table_name = ‘table01’;

原因：NUMTOYMINTERVAL(1,‘month’) 函数会一个月自动加一个分区
间隔分区实际上是范围分区的升级版本，分区表取名是数据库分配的
1.3.2 哈希分区
特点：存数据块，取数据慢

创建哈希分区表

create table h_t1 (id int,name varchar(10)) partition by hash (id) partitions 4;

查看哈希分区

SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = ‘H_T1’

给分区表录入1000条数据

begin for i in 1…1000 loop
insert into h_t1 values (i,‘aa’||i);
commit;
end loop;
end;

查询分区表中第3个分区的数据

SELECT COUNT(1) FROM H_T1 PARTITION(DMHASHPART3)

注意：哈希分区不支持修改、删除、增加、合并、交换等操作
1.3.3 列表分区

创建分区表

create table l_t1 (id int,name varchar (20)) partition by list (name) (partition p1 values (‘a’), partition p2 values (‘b’));

增加一个分区

alter table l_t1 add partition p3 values (‘c’)；

删除一个分区

alter table l_t1 drop partition p3;
1.3.4 分区组合

创建分区组合表（l-r）

CREATE TABLE 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 LESS THAN (‘2012-07-01’),
SUBPARTITION P13 VALUES LESS THAN (‘2012-10-01’),
SUBPARTITION P14 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)
);

查看分区（只能看到主分区）

SELECT TABLE_NAME,PARTITION_NAME,DBA_TAB_PARTITIONS.SUBPARTITION_COUNT
FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = ‘SALES’;

1.4 临时表
临时保存数据的表，用来替代中间表，中间表会产生大量没用的redolog。
临时表会存放在临时表空间。

查看临时表空间相关参数

select para_name,para_value,PARA_TYPE from v$dm_ini where para_name like ‘%TEMP%’;

 0表示不限制

设置临时表空间的参数

sp_set_para_value(2,‘TEMP_SIZE’,200);

达梦参数类别

IN FILE 静态参数，修改时候只能去改配置文件
SYS 系统级动态参数
SESSION 会话级的动态参数
READONLY 只读参数，修改要关闭数据库实例。

1.4.1 基于事务的临时表
事务提交，临时表数据会被truncate

创建基于事务的临时表

CREATE GLOBAL TEMPORARY TABLE TEST(ID INT) ON COMMIT DELETE ROWS;

插入一条数据

INSERT INTO TEST VALUES(1);

查询数据，会有结果

SELECT * FROM TEST;

提交事务

COMMIT;

再次查询，数据消失

SELECT* FROM TEST;
1.4.2 基于会话的临时表
会话关闭，临时表数据会被truncate

创建基于会话的临时表

CREATE GLOBAL TEMPORARY TABLE TEST2(ID INT) ON COMMIT PRESERVE ROWS;
 达梦支持在数据守护的备机上面创建临时表

1.5 外部表
数据库中只有表结构，真实的数据都在操作系统中（一般来说是一个文件），这种表就叫做外部表
外部表构成条件：
1、 外部数据
[dmdba@localhost ~]$ cat a.txt
1,aa
2,bb
3,cc
2、 控制文件
[dmdba@localhost ~]$ cat a.ctl
load data
infile ‘/home/dmdba/a.txt’
into table ext
fields ',’ 以“,”为分隔符
3、 创建
CREATE EXTERNAL TABLE EXT (id int,name varchar(10)) from ‘/home/dmdba/a.ctl’;
 外部表不支持增删改操作，只读
 Oracle的外部表迁移到达梦，要主语语法改造