DM表管理
1 堆表：
达梦默认的表是索引组织表，oracle默认的是堆表；
达梦既支持索引组织表，也支持堆表；
堆表：使用物理方式生成rowid，增加插入效率；但是查询效率低
创建堆表：
CREATE TABLE TEST.T1(ID INT,NAME VARCHAR(20)) STORAGE (ON TBS,BRANCH(4,2));
--BRANCH 堆表的标志
CREATE TABLE TEST.T2(ID INT,NAME VARCHAR(20)) STORAGE (ON "TBS", CLUSTERBTR) --CLUSTERBTR 索引组织表
//通过查询dba_indexes表中的index_type，来确定表类型：
select index_type from dba_indexes where table_name IN ('T1','T2')
CLUSTER --索引组织表
FLAT --堆表
系统中的参数：
LIST_TABLE 参数：创建的表是否为堆表：0 否，1 是（默认为0，达梦默认创建索引组织表）
2 分区表
2.1 范围分区(RANGE)：
分区列必须是数字或者是日期类型；
--创建分区表：
CREATE TABLE TEST.T_R1(ID INT , NAME VARCHAR(20))
PARTITION BY RANGE (ID) --RANGE为范围分区的标识
(PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(300));
SELECT T.partitioned FROM DBA_TABLES T WHERE TABLE_NAME='T_R1' --partitioned列中值为yes 是分区表
分区表(索引组织表)可以存在不同的表空间中：
新建表空间:
create tablespace "TBS2" datafile '/dm8/data/DAMENG/TBS02.DBF' size 100 autoextend on next 2 maxsize 200 CACHE = NORMAL;
create tablespace "TBS3" datafile '/dm8/data/DAMENG/TBS03.DBF' size 100 autoextend on next 2 maxsize 200 CACHE = NORMAL;
create tablespace "TBS4" datafile '/dm8/data/DAMENG/TBS04.DBF' size 100 autoextend on next 2 maxsize 200 CACHE = NORMAL;
--创建一个在不同表空间的分区表：
CREATE TABLE TEST.T_R2(SID INT,ID INT , NAME VARCHAR(20))
PARTITION BY RANGE (SID)
(PARTITION P1 VALUES LESS THAN(100) TABLESPACE TBS,
PARTITION P2 VALUES LESS THAN(200) TABLESPACE TBS2,
PARTITION P3 VALUES LESS THAN(300) TABLESPACE TBS3);
--增加分区表中的分区
ALTER TABLE TEST.T_R2 ADD PARTITION PN VALUES LESS THAN (MAXVALUE);
分区表(堆表)所有的子表都只能位于同一表空间：
--创建位于不同表空间的分区表（堆表）
CREATE TABLE TEST.T_R3(SID INT,ID INT , NAME VARCHAR(20))
PARTITION BY RANGE (SID)
(PARTITION P1 VALUES LESS THAN(100) TABLESPACE TBS,
PARTITION P2 VALUES LESS THAN(200) TABLESPACE TBS2,
PARTITION P3 VALUES LESS THAN(300) TABLESPACE TBS3)
STORAGE(NOBRANCH);
报错： 执行失败(语句1)
第6 行附近出现错误[-2757]:
水平分区堆表各子表必须位于同一个表空间
2.2 列表分区(LIST)：
列表分区适合字符串类型：
--创建列表分区表
CREATE TABLE TEST.T_SALES(SALEID INT,SALENAME VARCHAR(20),SALEDATE DATETIME,CITY CHAR(10))
PARTITION BY LIST(CITY) --LIST未列表分区的标识
(PARTITION S1 VALUES('北京','天津'）,
PARTITION S2 VALUES('武汉','长沙'),
PARTITION S3 VALUES('广州','深圳'))
--插入数据：
INSERT INTO TEST.T_SALES VALUES(1,'TOM','1996-02-16','北京')
--插入未定义分区的数据：
INSERT INTO TEST.T_SALES VALUES(1,'JERRY','1996-01-16','南京')
--报错：
执行失败(语句1)
没有找到合适的分区
--增加分区，该分区可存储所有未定义的数据：
ALTER TABLE TEST.T_SALES ADD PARTITION PN VALUES (DEFAULT)
2.3 哈希分区(HASH)：
存数据非常快，取数据慢，数据平均分配各个分区。
如果hash分区不指定分区表名，那就通过指定的哈希的分区数来创建，分区表名统一使用DMHASHPART+分区号（从0开始）作为分区表名。
--创建哈希分区表：
CREATE TABLE TEST.T_HASH(ID INT,NAME VARCHAR(10))
PARTITION BY HASH(ID) PARTITIONS 10;
--插入数据：
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TEST.T_HASH VALUES(I,'AAA'||I);
END LOOP;
COMMIT;
END;
2.4 组合分区：
RANGE+LIST 范围+列表 RANGE+HASH 范围+哈希 LIST+LIST 列表+列表
HASH+HASH 哈希+哈希 LIST+RANGE 列表+范围
DM最多支持8层分区：
--例：LIST--RANGE
CREATE TABLE TEST.SALE_SUM(SALE_ID INT,SALE_NAME VARCHAR(20),SALE_DATE DATE,CITY CHAR(20))
PARTITION BY LIST (CITY)
SUBPARTITION BY RANGE(SALE_DATE)
SUBPARTITION TEMPLATE( --模板子分区
SUBPARTITION P01 VALUES LESS THAN('2012-04-01'),
SUBPARTITION P02 VALUES LESS THAN('2014-04-01'),
SUBPARTITION P03 VALUES LESS THAN(MAXVALUE))
(PARTITION P1 VALUES('北京','天津','河北') --自定义子分区
(SUBPARTITION P1_1 VALUES LESS THAN('2013-03-01'),
SUBPARTITION P1_2 VALUES LESS THAN('2015-05-01'),
SUBPARTITION P1_3 VALUES LESS THAN(MAXVALUE)),
PARTITION P2 VALUES('南京','上海'),
PARTITION P3 VALUES('广州','深圳'));
注：1 数据符合自定义子分区的，先写入自定义子分区；没有自定义子分区的，进入模板子分区；
2 分区表中有主键的话，分区列必须包含主键列；
2.5 间隔分区：
函数：NUMTOYMINTERVAL(N,INTERVAL_UNIT)
--eg:
SELECT SYSDATE+NUMTOYMINTERVAL(1,'YEAR') FROM DUAL
N:数据类型 INTERVAL_UNIT:YEAR,MONTH
实际上是范围分区的一个扩展；
--新建时间间隔分区:
CREATE TABLE TEST.T_INTERVAL(ID INT,NAME VARCHAR(20),BIRTHDAY DATE)
PARTITION BY RANGE(BIRTHDAY)
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION P_1996 VALUES LESS THAN(TO_DATE('1996-12-31','YYYY-MM-DD')),
PARTITION P_1997 VALUES LESS THAN(TO_DATE('1997-12-31','YYYY-MM-DD')));
--插入数据;
INSERT INTO TEST.T_INTERVAL VALUES(1,'DDD','1996-05-01');
INSERT INTO TEST.T_INTERVAL VALUES(1,'DDD','1997-05-01');
INSERT INTO TEST.T_INTERVAL VALUES(1,'DDD','1998-05-01');
注：用了时间间隔函数做分区，在数据插入的时候，如果没有合适的分区，数据库会自动创建一个新分区；
2.6 分区表的维护
数据字典： dba_tab_partitions
--1 增加分区
ALTER TABLE <TABLE_NAME> ADD PARTITION <PARTITION_NAME> VALUES LESS THAN(VALUES);
--2 删除分区
ALTER TABLE <TABLE_NALTER TABLE TEST.T_R1 MERGE PARTITIONS P2,P3 INTO PARTITION P2_3；ME> DROP PARTITION <PARTITION_NAME>;
--3 合并分区
ALTER TABLE <TABLE_NAME> MERGE PARTITIONS <需要和并和子表> INTO PARTITION <新子表名>；
eg:ALTER TABLE TEST.T_R1 MERGE PARTITIONS P2,P3 INTO PARTITION P2_3;
--4 拆分分区
ALTER TABLE <TABLE_NAME> SPLIT PARTITION <需要拆分的分区> AT('分区条件') into (partiton <新分区1>，PARTITION <新分区2>)；
eg:ALTER TABLE TEST.T_R1 SPLIT PARTITION p2_3 AT(200) into (PARTITION p2，PARTITION p3);
--5 交换分区
ALTER TBALE <TABLE_NAME(分区表)> EXCHANGE PARTITION <PAR_NAME(分区子表)> with table <TABLE_NAME(被交换的普通表)> --将普通表的数据与分区表的子表中的数据交换；
注：交换时，会将普通表中的全部数据全部交换至分区表中，但不会校验分区表子表的范围，需要人工审核后再做交换；
--6 如何将非分区表转换为分区表
1、将普通表中的数据导出来（dexp）；
2、建一个结构一样的分区表；
3、将数据导入到分区表中；
2.7 总结：
3 临时表
临时表空间支持以下功能：
1、在临时表中，可以像普通的永久表一样更新，插入和删除数据。
2、临时表的dml 操作产生较少的redo 日志
3、临时表支持建索引，以提高查询性能
4、在一个会话或是事务结束后，数据将自动从临时表中删除。
5、不同用户访问相同的临时表，每个用户只能看到自己的数据。
6、临时表的表结构在数据删除后仍然存在的
7、临时表的权限管理和普通表一致。
临时表有两种类型：事务级表空间 会话级表空间
1、on commit delete rows：临时表是事务级的
CREATE GLOBAL TEMPORARY TABLE TEST.TMP_T1(ID INT) ON COMMIT DELETE ROWS;
2、on comit preserve rows：会话级的
CREATE GLOBAL TEMPORARY TABLE TEST.TMP_T2(ID INT) ON COMMIT PRESERVE ROWS;
注意：
临时表清空，临时表空间文件在磁盘所占大小并不会因此缩减，用户可以通过系统函数sp_trunc_ts_file 来进行磁盘空间的清理。
Sp_trunc_ts_file(
Ts_id int -----指定截断文件的临时表空间id
File_id int ----指定截断文件的文件ID
TO_SIZE INT ----指定将文件截断至多大，以M 单位。
)
案例：
将临时表空间id 为3,临时文件id 为0 的文件截断到32M,库的页大小8K.
Call sp_trunc_ts_file(3,0,32);
4 清空表
DM8 支持以下的方式来删除表中的所有的行
1、delete 语句
2、Drop (create replace)
3、truncate 语句
区别：
1、空间释放：drop>truncate>delete
2、应用范围：truncate 只能对table,delete 可以是table,view.Truncatet 和delete 只删除数据，drop 则删除 整个表，包括结构和数据。
3、delete DML 语句，truncate 和drop ddl 语句。
4、Delete 可以带where 进行过滤，其他的不可以。
5、Delete 记undo 慢。Truncate 不记undo 快。
5 huge表
HFS(Huge file system):是达梦数据库实现的，针对海量数据进行分析出一种高效、简单的列存存储机制，huge 就是建立在hfs 上的。Huge有自已特有的表空间（huge 空间）。V$huge_tablespace.最多可以创建32767 个huge 表空间。
如果不指定huge 表空间，默认放在hmain 表空间中。
Huge 表空间的大小由创建huge 表空间时指定的文件大小来定。单个文件的大小为：16M-1024*1024M。文 件的大小必须2的n次方。如果不是则向上看齐，默认为64M.
CREATE HUGE TABLESPACE<表空间名> path <表空间文件路径>
--创建huge表空间
create huge tablespace "THUGE" path '/dm8/data/DAMENG/THUGE01.DBF';
--创建huge表
create huge table "TEST"."TABLE_1"
(
"ID" INT,
"NAME" VARCHAR2(50)
) storage(section(65536), filesize(64),on "THUGE")
--查表结构
sp_tabledef('TEST','THUGE1');
SELECT DBMS_METADATA.GET_DDL('TABLE','THUGE1','TEST')
6 外部表
定义：外部表是存储在数据库外面的表，外部表的数据存储在操作系统中，是操作系统文件。建立外部表的时候，不会产生段，页，簇等存储结构。只有与表相关的定义放在数据字典中，不能对外部表的内容进行修改，不能对外部表建立索引。不需要将外部表的数据装载到数据库中来，通过Sql 解码器来访问外部表。
如何建立外部表：
Create external table<表名><表结构定义>from <控制文件路径>｜<数据文件路径><参数>；
案例1：
[dmdba@localhost dm8]$ cat a.txt
1,aaaaa
2,bbbbb
3,cc
4,ddd
5,fff
6,uuu
[dmdba@localhost dm8]$ cat a.ctl
LOAD DATA
INFILE '/dm8/a.txt'
INTO TABLE EXT
FIELDS ','
SQL> create external table test.ext(ID int, name varchar(20)) from '/dm8/a.ctl';
SQL> SELECT * FROM TEST.EXT;
案例2：
[dmdba@localhost dm8]$ cat b.txt
10|9|7
4|3|2|5
1|3|4|5
6|7
CREATE EXTERNAL TABLE TEST.EXT2(C1 INT, C2 INT, C3 INT) FROM DATAFILE '/dm8/b.txt' parms(fields delimited by '|');
TO TABLE EXT
FIELDS ‘,’
SQL> create external table test.ext(ID int, name varchar(20)) from ‘/dm8/a.ctl’;
SQL> SELECT * FROM TEST.EXT;
案例2：
[dmdba@localhost dm8]$ cat b.txt
10|9|7
4|3|2|5
1|3|4|5
6|7
CREATE EXTERNAL TABLE TEST.EXT2(C1 INT, C2 INT, C3 INT) FROM DATAFILE ‘/dm8/b.txt’ parms(fields delimited by ‘|’);
扫一扫
886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
