【达梦】进阶知识点

一、分区表
1范围分区

create tablespace TEST datafile ‘/dm8/data/DAMENG/TEST01.DBF’ size 512;
create user TEST IDENTIFIED BY “abcd+1234” DEFAULT TABLESPACE TEST;

CREATE TABLE TEST.T1
(
“ID” INT,
“NAME” VARCHAR(20)
)
PARTITION BY RANGE (“ID”)
(
PARTITION “P1” VALUES LESS THAN(100),
PARTITION “P2” VALUES LESS THAN(200),
PARTITION “P3” VALUES LESS THAN(300)
)
STORAGE(INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0)
;

begin
FOR i IN 1…1000 LOOP
insert INTO TEST.T1 VALUES(i,‘****’||i);
COMMIT;
END LOOP;
end;

insert INTO TEST.T1 VALUES(300,‘aaa’);

alter TABLE TEST.T1 ADD PARTITION pn VALUES LESS THAN (MAXVALUE);

create TABLESPACE tbs1 DATAFILE ‘/dm8/data/DAMENG/tbs01.DBF’ size 512;
create TABLESPACE tbs2 DATAFILE ‘/dm8/data/DAMENG/tbs02.DBF’ size 512;
create TABLESPACE tbs3 DATAFILE ‘/dm8/data/DAMENG/tbs03.DBF’ size 512;
create TABLESPACE tbs4 DATAFILE ‘/dm8/data/DAMENG/tbs04.DBF’ size 512;

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 1,FILLFACTOR 0,on “TBS1”),
PARTITION “P2” VALUES LESS THAN(200) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS2”),
PARTITION “P3” VALUES LESS THAN(300) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS3”),
PARTITION “pn” VALUES LESS THAN(MAXVALUE) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS4”)
)
storage (INITIAL 1,NEXT 1,MINEXTENTS 1,fillfactor 0)
;

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 “TBS1”),
PARTITION “P2” VALUES LESS THAN(200) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS2”),
PARTITION “P3” VALUES LESS THAN(300) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS3”),
PARTITION “pn” VALUES LESS THAN(MAXVALUE) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on “TBS4”)
)
storage (INITIAL 1,NEXT 1,MINEXTENTS 1,fillfactor 0 ,NOBRANCH)
;

2列表分区

‘北京’,‘天津’, ‘哈尔滨’,‘青岛’
‘上海’,‘南京’,‘杭州’
‘武汉’,‘长沙’,‘合肥’
‘广州’,‘深圳’,‘福建’
‘西安’

create table TEST.t_sales(sales_id INT,saleman VARCHAR,saledata DATE,city CHAR(10))
partition by LIST(city)
(partition P1 values (‘北京’,‘天津’, ‘哈尔滨’,‘青岛’),
PARTITION P2 VALUES (‘上海’,‘南京’,‘杭州’),
PARTITION P3 VALUES (‘武汉’,‘长沙’,‘合肥’),
PARTITION P4 VALUES (‘广州’,‘深圳’,‘福建’));

insert INTO TEST.T_SALES values (1,‘aaaa’,‘2022-04-18’,‘北京’);
commit;

insert INTO TEST.T_SALES values (2,‘ccccc’,‘2022-03-18’,‘西安’);

alter table TEST.T_SALES ADD PARTITION PN VALUES(DEFAULT);

3哈希分区
create TABLE TEST.t4
(ID int,
NAME varchar(20)
)
partition by HASH(ID)
(
PARTITION “P1”,
PARTITION “P2”,
PARTITION “P3”,
PARTITION “P4”
);

create TABLE TEST.HASH1
(ID int,
NAME varchar(20)
)
partition by HASH(ID) PARTITIONS 10;

4组合分区
create TABLE TEST.sales_sum(id int,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 (‘广州’,‘深圳’,‘福建’)
);

insert into TEST.SALES_SUM VALUES(1,‘aaaaa’,‘2012-05-01’,‘北京’);
insert into TEST.SALES_SUM VALUES(2,‘bbbbbb’,‘2012-06-01’,‘武汉’);
commit;

5间隔分区

create TABLE TEST.R10_INTERVAL_YEAR
(ID int,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.R10_INTERVAL_YEAR VALUES(3,‘EEE’,‘1989-03-02’);
INSERT INTO TEST.R10_INTERVAL_YEAR VALUES(4,‘ffffff’,‘1991-03-02’);
INSERT INTO TEST.R10_INTERVAL_YEAR VALUES(7,‘UIOULIU’,‘1994-07-02’);
commit;

二、外部表
create EXTERNAL TABLE TEST.ext (id int,name VARCHAR(20)) from ‘/dm8/a.ctl’;
select * from TEST.EXT;

create EXTERNAL TABLE TEST.ext2 (c1 int,c2 int,c3 int) from DATAFILE ‘/dm8/b.txt’ PARMS(fields delimited by ‘|’);

三、临时表
1、事务级别： on commit detele rows
create GLOBAL TEMPORARY TABLE TEST.tmp_01(id int) ON COMMIT DELETE ROWS;
insert into test.tmp_01 values(1);
insert into test.tmp_01 values(2);
insert into test.tmp_01 values(3);
commit;
select * from TEST.TMP_01;

2、会话级别的临时表 on commit preserve rows
create GLOBAL TEMPORARY TABLE TEST.tmp_02(id int) on COMMIT PRESERVE ROWS;

select para_name,para_value from v$d{m}_{i}niWHERE"V$DM_INI".PARA_NAME LIKE ‘%TEMP%’;

SP_SET_PARA_VALUE(2,‘TEMP_SIZE’,20);

sp_trunc_ts_file(3,0,10);
select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 from
dba_data_files where tablespace_name=‘TEMP’;

四、序列
序列：在内存预先申请的一段地址空间，类似于取号排
CREATE SEQUENCE “TEST”.“s1” INCREMENT BY 2 START WITH 1 MAXVALUE 10 MINVALUE 1;

五、同义词
create SYNONYM TEST.SY1 FOR TEST.T1;
create PUBLIC SYNONYM SY2 FOR TEST.EXT;

六、视图
CREATE VIEW TEST.V1 AS SELECT employee_id,employee_name FROM DMHR.EMPLOYEE;
CREATE VIEW TEST.V2 AS
SELECT d.department_name, a.av1
FROM (select department_id,AVG( salary) as av1
FROM dmhr.EMPLOYEE e GROUP BY DEPARTMENT_ID having
avg(salary)>10000) a
join DMHR.DEPARTMENT d ON a.DEPARTMENT_ID=d.DEPARTMENT_ID;

物化视图
create MATERIALIZED VIEW TEST.MV1 AS SELECT * FROM TEST.T1;
create MATERIALIZED VIEW TEST.MV2 AS SELECT * FROM TEST.T1 WHERE id >200;

CREATE MATERIALIZED VIEW LOG ON TEST.T1;日志
CREATE MATERIALIZED VIEW TEST.MV3 REFRESH COMPLETE ON COMMIT AS SELECT * FROM TEST.T1;自动更新
CREATE MATERIALIZED VIEW TEST.MV3 REFRESH FAST ON COMMIT AS SELECT * FROM TEST.T1;自动更新

七、索引
唯一索引
create UNIQUE INDEX ind_t2 ON TEST.t2(ID) TABLESPACE ind;

函数索引
create INDEX ind_tmp ON TEST.TMP(UPPER(email)) TABLESPACE ind;

复合索引
create INDEX IND_TMP1 on TEST.TMP (EMPLOYEE_ID,employee_name);

位图索引
create BITMAP INDEX ind_t2 ON TEST.T2(SEX);

索引维护
alter INDEX TEST.IND_TMP REBUILD;
alter INDEX TEST.IND_TMP1 REBUILD ONLINE;
drop INDEX TEST.IND_TMP1;

分区索引
create INDEX ind_sale2 ON TEST.SALE2(SALE2.ID);
create INDEX ind_sale3 ON TEST.SALE2(SALE2.NAME) GLOBAL STORAGE(INITIAL 1,NEXT 1,MINEXTENTS 1,on ind);

全文索引
create CONTEXT INDEX cti_ad on PERSON.address(ADDRESS1) LEXER default_lexer;

八、审计
审计使用审计用户
sp_set_enable_audit(2);0,1,2
sp_set_enable_audit(0);0,1,2

sp_audit_stmt(‘table’,‘test’,‘all’);语句级别 表，用户，all，successful，fail
sp_noaudit_stmt(‘table’,‘test’,‘all’);

sp_audit_objecit(‘UPDATE’,‘test’,‘test’,‘emp’,‘salary’,‘all’);对象级别
sp_noaudit_objecit(‘UPDATE’,‘test’,‘test’,‘emp’,‘salary’,‘all’);

select username,operation,sql_text form v$auditrecords;

九、AWR报告
AWR报告
select sf_check_awr_sys;
sp_init_awr_sys(1);
call SYS.DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
call SYS.DBMS_WORKLOAD_REPOSITORY.create_snapshot();
select * from sys.wrm$_snapshot;
call SYS.AWR_REPORT_HTML(1,2,‘/dm8’,‘awr1.html’);

十、快速加载
快速加载dmfldr
create TABLE TEST.fldr1 (id int,name VARCHAR,bir DATE);
导入命令：./dmfldr sysdba/sysdba control=‘/dm8/data/fldr1.ctl’
select * from TEST.FLDR1;

create TABLE TEST.da(c1 int,c2 BLOB,c3 CLOB);
insert into TEST.DA VALUES(1,0XAB12354323567,‘jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf’);
insert into TEST.DA VALUES(2,0XAB12354224567,‘jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf’);
insert into TEST.DA VALUES(3,0XAB12353225367,‘jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf’);
commit;

控制文件dafldr.ctl
options(skip=1) 跳过第一行
LOAD DATA
INFILE ‘/dm8/fldr/dafldr.txt’
INTO TABLE TEST.DA
FILEDS ‘|’
(C1,
C2,
C3)

导入命令：./dmfldr sysdba/SYSDBA control=‘/dm8/dafldr.ctl’ lob_directory=‘/dm8’ mode=‘in’
./dmfldr sysdba/SYSDBA control=‘/dm8/dafldr.ctl’ lob_directory=‘/dm8’ badfile=‘/dm8/test.bad’