同义词/分区/视图

同义词/视图

这两种都用来：

1. 精简语句
2. 隐藏细节

同义词(Synonym)

为 表或对象 创建别名：

• 可以简化 sql 语句
• 可以隐藏细节
• 提供公共访问（公有同义词）

-- 查看跟同义词有关的权限
-- 分两种：
--- synonym, 创建来自己使用
--- public synonym, 创建来所有人都可以使用
select * from system_privilege_map where name like upper('%syn%');

-- 将创建同义词的权限授权给用户
grant create synonym to vip;
grant create public synonym to vip;

-- 创建
create synonym e for scott.emp;
create or replace public synonym d for scott.dept;

-- 可以为表之外的对象创建同义词
create sequence seq_aaaaaaforme;
create synonym seq_aaa for seq_aaaaaaforme;

-- 使用，在一切可以使用原先对象的地方
select * from e join d using (deptno);
desc d;
select seq_aaa.nextval from dual;

-- 删除
drop synonym d;
drop public synonym d;

视图(View)

视图, 默认指的是关系视图, 又叫虚表。

• 不占用数据空间
• 可以简化语句
• 可以隐藏细节
• 可以提升安全

除了关系视图，广义的视图包括：

1. 关系视图(狭义的视图，虚表)
2. 内嵌视图(子查询中的临时结果)
3. 对象视图(面向对象)
4. 物化视图(以空间换时间)

-- 查询跟视图相关的权限
select * from system_privilege_map where name like '%VIEW%';
-- 确保用户有足够权限
grant create view to vip;
grant all on scott.emp to vip;
grant all on scott.dept to vip;

-- 创建视图
create view vemp as select * from scott.emp;  -- 最简形式，为单个表创建视图
create view v_emp_vip as select * from scott.emp where sal >= 2000;  -- 可以为表的部分数据创建视图
create view vvevip as select * from vevip;  -- 可以为视图查询创建视图
create or replace view vevip as     -- 可以为多表联合查询的结果集创建视图。
  select e.*, d.dname, d.loc
    from emp e, dept d
   where e.deptno = d.deptno(+)
     and e.sal >= 2000;

-- 使用
select * from e;
select * from vemp;
select * from v_emp_vip;
select * from vevip;
select * from vvevip;

-- 删除
drop view vemp;


-- force 强迫！！！不管 select 语句是否有错，都要创建。
-- or replace, 如果视图已存在，覆盖
-- with read only, 创建只读视图
create or replace force view vaaa as select * from wohaoshuai with read only;

-- with check option, 防止更新后的数据, 超出视图的范围
insert into emp (empno, ename, sal) values (9988, '张思', 3333);
create or replace view vbbb as select * from emp where sal > 3332;
select * from vbbb; -- 两条数据，King 和 张思
update vbbb set sal = 2000 where empno = 9988;
select * from vbbb; -- 只剩一条数据。
-- 按照道理，update 只是更新数据，不应该导致结果集变少。所以，需要通过 with check option 来限制不合理的修改
create or replace view vcc as select * from emp where sal > 1500  with check option;
update vbbb set sal = 1000 where empno = 9988;  -- 会报错

效率和速度

表分区

分而治之

目的：

1. 安全（鸡蛋不要放在一个篮子里）
2. 效率****（快速找到南方it学院所有姓张的人）
3. 便于维护

场景：

1. 数据量极大（大于 2G）
2. 历史数据比重比较大

分类：

1. 范围分区
2. 列表分区
3. 哈希分区(hash)

在以上分区的基础上，可以两两结合，形成 *复合分区*，但常用的就是两种：

1. 范围-列表分区
2. 范围-哈希分区

范围分区:

-- 创建一个普通表的语句
create table person1 (id int primary key, name varchar2(20), birth date);
-- 数据将会在同一个表空间同一个段内
insert into person1 values (1, 'sss', sysdate);

-- 创建一个分区表
-- 这里是按照生日进行范围分区
-- 语句的基本格式就是在普通建表的语句上，增加 partition by 语句块
create table person2 (name varchar2(20), birth date)
partition by range (birth)
(
  partition p1 values less than (to_date('19950101','yyyymmdd')),  -- 'values less than'
  partition p2 values less than (to_date('20000101','yyyymmdd')),
  partition p3 values less than (maxvalue)                         -- 默认分区
);
-- 插入，数据会根据分区的情况进入不同的分区内
insert into person2 values ('张三', to_date('19940707'));
insert into person2 values ('李四', to_date('19980707'));
insert into person2 values ('王五', to_date('20040707'));
-- 查询表中所有数据
select * from person2;
-- 查询特定分区上数据
select * from person2 partition (p3);


-- 可以为不同的分区指定不同的表空间
-- 没有指定表空间的分区，使用用户的默认表空间
-- 所以，一个表内的数据可以存在于不同表空间里，也就是可以存放在不同数据文件中，不同磁盘上
-- 因此，分区表能增强数据的安全性
create table person3 (name varchar2(20), birth date)
partition by range (birth)
(
  partition p1 values less than (to_date('19950101','yyyymmdd')) tablespace system,
  partition p2 values less than (to_date('20000101','yyyymmdd')) tablespace sysaux,
  partition p3 values less than (maxvalue) tablespace users
);


-- 可以在其他类型上进行范围分区
-- 也可以在多个字段上进行范围分区
create table person4 (name varchar2(20), birth date, score number)
partition by range (birth, score)
(
  partition p1 values less than (to_date('19900101','yyyymmdd'), 60),
  partition p2 values less than (to_date('19900101','yyyymmdd'), 90),
  partition p3 values less than (to_date('19990101','yyyymmdd'), 60),
  partition p4 values less than (to_date('19990101','yyyymmdd'), 90),
  partition p5 values less than (maxvalue, maxvalue)
);

列表分区:

-- 如果是生日的这样的字段，数据是连续的，应该使用范围分区
create table person (name varchar2(20), birth date)
partition by range(birth)
(
  partition p1 values less than (to_date('19900101', 'yyyymmdd')) tablespace users,
  partition p2 values less than (maxvalue)
);
insert into person values ('aaa', to_date('19871212', 'yyyymmdd'));
select * from person partition (p1);
  

/* 
where birth between 1987 and 1990 
where sex in ('男', '女')
*/

-- 但是像性别、民族等字段，更适合使用的是列表分区
-- 下面一个例子，使用性别作为分区字段，男的一个区，女的一个区
create table person2 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男'),
    partition p2 values ('女')
);
insert into person2 values ('aaa', '男');
insert into person2 values ('bbb', '女');
insert into person2 values ('ccc', '未知');  -- 报错
select * from person2 partition (p2);

-- 默认分区的写法
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男'),
    partition p2 values ('女'),
    partition p3 values (default)
);
insert into person3 values ('ccc', '未知');
select * from person3 partition (p3);


-- 可以为每个分区指定表空间
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男') tablespace users,
    partition p2 values ('女') tablespace system,
    partition p3 values (default)
);

哈希分区:

-- 哈希分区
-- 主要用在一些比较离散，不好分类的数据上，比如产品名字
-- 让 oracle 使用哈希算法自动计算数据的分区

-- 创建语句，非常简单
create table person4 (name varchar2(20), sex varchar2(10))
partition by hash (name)
(
  partition p1,
  partition p2 tablespace users
);
insert into person4 values ('aaa', '男');
insert into person4 values ('收款', '男');
select * from person4 partition (p1);

-- 上面的语句可以进一步简化为：
create table person5 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 5;

-- 为每个分区指定表空间
create table person6 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 3 store in (users, system, sysaux);

范围-列表分区:

-- 首先，按照生日进行列表分区，分了三个区
-- 其次，在每个分区内，又按照性别分了三个区
-- 所以，总共是 3 个分区 9 个子分区
create table person8 (name varchar2(20), sex varchar2(10), birth date)
partition by range(birth)
subpartition by list(sex)
subpartition template
(
   subpartition sp01 values ('男'),
   subpartition sp02 values ('女'),
   subpartition sp03 values (default)
)
(
   partition p1 values less than (to_date('19900101', 'yyyymmdd')),
   partition p2 values less than (to_date('20000101', 'yyyymmdd')),
   partition p3 values less than (maxvalue)
);

insert into person8 values ('aaa', '男', to_date('19900202'));
-- 查询这条数据，有以下三种方式：
select * from person8;
select * from person8 partition (p1);
select * from person8 subpartition (p1_sp01);

范围-哈希分区:

-- 先按照生日，将数据分为三个区
-- 然后在每个分区内，又按照哈希算法分成了三个区
-- 这样就保证了每个分区内的数据尽量的少，而且分区进行平衡
create table person7 (name varchar2(20), birth date)
partition by range (birth)
subpartition by hash (name) subpartitions 3
(
  partition p1 values less than (to_date('19900101', 'yyyymmdd')),
  partition p2 values less than (to_date('20000101', 'yyyymmdd')),
  partition p3 values less than (maxvalue)
);

相关字典表:

select * from user_objects where object_name ='PERSON8';
select * from user_tables where table_name = 'PERSON8';
select * from user_tab_partitions where table_name = 'PERSON8';
select * from user_tab_subpartitions where table_name = 'PERSON8';

操作表分区:

-- 添加分区
alter table person add partition p9 values less than (MAXVALUE);
alter table person add partition p9 values (1, 2);   -- 针对 list 分区
alter table person add partition;                    -- 针对 hash 分区

-- 删除分区
alter table person drop partition p3;

-- 删除分区内数据
alter table person truncate partition p3;

-- 合并相邻分区
alter table person merge partitions p2, p3 into partition p8;

-- 拆分分区
alter table person split partition p2 at (3000) into (partition p3, partition p14);     -- 范围分区的拆分
alter table person split partition p2 values (1,2) into (partition p3, partition p4);   -- 列表分区的拆分
alter table person split partition p2 into (partition p3 values (1, 2), partition p4 values (3), partition p5);   -- 列表分区的拆分

-- 重命名分区
alter table person rename partition p2 to p12;

物化视图

以空间换时间

物化视图，可以看做是 *加了同步功能的临时表*，它占用实际的存储空间。

创建的物化视图，可以在 user_tables 字典表里查到，因为本质上它就是一张表。

如果要创建这样的临时表，需要考虑几个方面：

1. 什么时候初始化数据

   build immediate   -- 建表的时候顺带初始化数据，默认
   build deferred    -- 数据延迟创建
       

2. 如果基表中的数据发生变化的时候，以什么样的策略保持同步

   refresh complete  -- 全量刷新，即将旧的完全删掉，再重建
   refresh fast      -- 增量刷新
   refresh force     -- 由 Oracle 自动判断刷新方式，默认
   refresh never     -- 不同步数据
       

3. 如果基表中的数据发生变化，数据同步的时机

   on commit         -- 基本的事务提交立刻同步到物化视图
   on demand start with 开始时间 next 下一个时间  -- 定时同步
       

4. 如果查询基表中的数据，而这些数据同时包含在物化视图中，那么，是不是让 Oracle 将查询语句更改为查询物化视图

   enable query rewrite
   disable query rewrite  -- 默认
       

创建物化视图的示例：

-- 创建一个简单的物化视图的例子
create materialized view mv_emp1
   build immediate  -- 创建物化视图的时候，立刻初始化数据
   refresh complete -- 采取全量同步的方式
   on commit        -- 基表中数据提交会触发同步行为
   -- on demand sysdate next sysdate + 1 -- 每天更新一次
   enable query rewrite
as
select * from emp where sal > 3000;
-- 查询
select * from mv_emp1;
-- 向基表中插入数据
insert into emp (empno, ename, sal) values (8377, '王武', 4444);
-- 事务的提交才会触发同步，所以这一步不可少
commit;
-- 查询，看数据是否已经同步
select * from mv_emp1;



-- 如果要增量同步，需要记录在上次更新到下次更新时间内，基表内所有变化
-- 在这里，使用的是物化视图日志
-- 创建语句如下:
create materialized view log on emp [with rowid/primary key];
-- 然后就可以指定增量同步的方式了
create materialized view mv_emp2
   build immediate 
   refresh fast                       -- 采取增量同步的方式
   on demand sysdate next sysdate + 1 -- 每天更新一次，on demand 后面还有其他很多不同形式的命令
   enable query rewrite
as
select * from emp where sal > 3000;


-- 删除
drop materialized view log on emp;
drop materialized view mv_emp2;