3 分区表
3.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,创建的表为堆表
不建议改这个系统参数,想要建堆表就用关键字
3.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) ;
3.3 分区表
物理上是一张大表,逻辑上是N张小表,提高I/O性能
达梦支持的类型:范围分区、哈希分区、列表分区、组合分区
达梦不支持的类型:system分区、参考分区
3.3.1 范围分区
3.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 );
3.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;
3.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);
3.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’);
再次查询分区结构,发现系统自动创建了一个分