10.达梦DCP认证课程-序列同义词物化视图

原创让世界为你转身 2024-10-25

1237

1.序列

1.1 创建序列

示例1：

create SEQUENCE seq_test
START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999999999 MINVALUE 1 NOCYCLE;

示例2：

create SEQUENCE seq1 START WITH 20 INCREMENT by 1 MAXVALUE 200 MINVALUE 1 CACHE 20;
alter SEQUENCE SEQ1 INCREMENT by 100 NOCYCLE;

1.2 序列查询

select seq_test.currval;
select seq_test.nextval;
select * from USER_SEQUENCES;

1.3 删除序列

drop SEQUENCE seq1;

2.同义词

同义词包含普通同义词和公共同义词。

创建同义词：

1.当普通同义词名称和公共同义词名称相同时，默认用户访问的是当前模式下的同义词。

--创建同义词
create SYNONYM emp FOR t_testtab;
--创建公共同义词
create PUBLIC SYNONYM emp FOR dmhr.employee;
select * from emp;

2.换个用户查询，默认查询的是公共同义词

select * from emp;
select * from dmtest.emp;

删除同义词：

--删除普通同义词
drop SYNONYM emp;
--删除公共同义词
drop PUBLIC SYNONYM emp

3.物化视图

3.1 物化视图的语法

3.1.1 创建物化视图

创建语法：

create materialized view [view_name] 
build [immediate|deferred] --创建方式，默认为immediate
refresh [fast|complete|force] --刷新方式，默认为force
[ 
on [commit|demand] |  --刷新时间，默认为demand
start with (start_time) --刷新开始时间
next (next_time)  --刷新间隔时间
with [primary key|rowid] --基于基表的主键或rowid创建，默认为primary key
] 
[enable|disable] query rewrite --是否启用查询重写，默认为disable，dm不支持查询重写 
as  --关键字
{创建物化视图用的查询语句} --基本选取数据的select语句

例子:

--创建on commit方式的快速刷新mv：
CREATE MATERIALIZED VIEW mv_test1
REFRESH FAST ON COMMIT
AS SELECT * FROM test;

--创建on demand方式的快速刷新mv,需要手动刷新:
CREATE MATERIALIZED VIEW mv_test2
REFRESH FAST ON DEMAND
AS SELECT * FROM test;

--创建start方式的快速刷新mv,间隔30s刷新一次：
CREATE MATERIALIZED VIEW mv_test3
REFRESH FAST START WITH SYSDATE 
NEXT SYSDATE + 1/2880
AS SELECT * FROM test;

/*
注意：
on demand可以和start子句组合一起使用;
on commit不能与start子句组合使用；
*/

3.1.2 删除物化视图

删除语法:

--如果在基表创建的有物化视图日志，可以先删除物化视图日志，再删除物化视图
drop materialized view log on [table_name];
drop materialized view [view_name];

例子

drop materialized view log on test;
drop materialized view mv_test;

3.1.3 手动刷新物化视图

手动刷新语法

begin 
DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}  --逗号分隔要刷新的物化视图
   method                 IN     VARCHAR2       := NULL,    --刷新的方式
   rollback_seg           IN     VARCHAR2       := NULL,  
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN        := false,
   out_of_place           IN     BOOLEAN        := false);
   end;

例子：

--手动刷新，c-完全刷新,f-增量刷新,?-强制刷新
dbms_mview.refresh('mv_test2','c');
refresh MATERIALIZED VIEW V_EMP force;

3.2 物化视图数据填充方式

创建方式（Build Methods）：创建物化视图时的数据填充方式，是立即生成数据，还是延迟生成数据。

build [immediate|deferred]

BUILD IMMEDIATE： 在创建物化视图的时候就生成数据。默认为BUILD IMMEDIATE。
BUILD DEFERRED: 在创建时不生成数据，以后根据需要再生成数据。

3.3 物化视图刷新

3.3.1 刷新方式

refresh [fast|complete|force]

FAST： 快速刷新，采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须在基表上创建物化视图日志。在dm8中，一般单表的物化视图，单表聚合、多表关联不涉及聚合的查询可以使用快速刷新。

COMPLETE： 完全刷新，会删除表中所有的记录，然后根据物化视图中查询语句的定义重新生成物化视图。

FORCE：默认的数据刷新方式，会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

3.3.2 刷新时机

on [commit|demand] |start with ... next...
never refresh

ON COMMIT： 一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

ON DEMAND： 在用户需要的时候进行刷新，可以手工通过refresh materialized view mv_emp或者dbms_mviews.refresh来进行刷新，也可以通过JOB定时进行刷新，即更新物化视图，以保证和基表数据的一致性；

START WITH … NEXT： START WITH 用于指定首次刷新物化视图的时间， NEXT 指定自动刷新的间隔；

NEVER REFRESH： 物化视图从不刷新

3.3.3 刷新类型

with [primary key|rowid]

WITH PRIMARY KEY： 基于基表的主键创建物化视图，基表上需要有主键，默认为primary key.

WITH ROWID： 基于基表的rowid创建物化视图，不能对基表执行分组函数、多表连结等需要把多个rowid合成一行的操作（因为不知道以哪个rowid为准），支持只有一个单一的基表；

3.4 物化视图创建

3.4.1 创建完全刷新的物化视图

创建物化视图：

--创建物化视图
create MATERIALIZED view v_emp REFRESH FORCE on DEMAND
as
select b.department_name, count(*), sum(a.salary)
from dmhr.employee a, dmhr.department b
where a.department_id= b.department_id
group by b.department_name;
--查询物化视图
select * from v_emp;

修改物化视图的刷新方式：

--定时刷新,系统会自动创建一个数据库级触发器，修改为手动刷新后会自动删除
alter MATERIALIZED VIEW V_EMP REFRESH START WITH trunc(sysdate,'dd')+1 NEXT
trunc(sysdate,'dd')+1;
--从不刷新
alter MATERIALIZED VIEW V_EMP NEVER REFRESH;
--需要时手动刷新
alter MATERIALIZED VIEW V_EMP REFRESH FORCE on DEMAND;

手动刷新：

refresh MATERIALIZED VIEW V_EMP force;
dbms_mview.REFRESH('V_EMP','C');

查看物化视图：

select * from USER_MVIEWS;

3.4.2 创建快速刷新的物化视图

创建快速刷新物化视图：

物化视图日志的创建，可以使用with primary key或者with rowid。默认是with primary key，with primary key要求基础表要有主键。

--1.创建基表
create table t_dept as select * from dmhr.DEPARTMENT;
--2.在基表上创建物化视图日志
create MATERIALIZED VIEW LOG on t_dept WITH rowid(DEPARTMENT_ID, DEPARTMENT_NAME);
--3.创建快速刷新物化视图，需先创建物化视图日志，基于rowid的物化视图日志，物化视图创建时需要包含rowid列
create MATERIALIZED view v_deptinfo REFRESH FAST on COMMIT WITH ROWID
as
select b.DEPARTMENT_ID, b.DEPARTMENT_NAME, b.rowid dept_rowid
from t_dept b;
--4.查询物化视图
select * from v_deptinfo;
--5.更新基表
insert into t_dept(DEPARTMENT_ID, DEPARTMENT_NAME)
values(888888, 'TEST');
--6.查询物化视图日志,MLOG$_为前缀
select * from "MLOG$_T_DEPT";
commit;  --提交后物化视图日志信息被删掉
--7.查询基表和物化视图
select * from v_deptinfo;
select * from t_dept;

修改物化视图刷新时机：

alter MATERIALIZED VIEW v_deptinfo REFRESH FAST on DEMAND;
delete from t_dept where DEPARTMENT_ID < 1003;
commit;
select * from "MLOG$_T_DEPT";
update t_dept set DEPARTMENT_ID =0 where DEPARTMENT_ID=2;
--未手动刷新，物化视图和基表数据不一致
select * from v_deptinfo;
select * from t_dept;