Oracle基础部分三(视图、物化视图、序列、同义词、索引)

1 视图

1.1概述

视图优点：
1.简化数据操作：视图可以简化用户处理数据的方式
2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中
3.视图提供一个简单而有效的安全机制，可以定制不同用户对数据的访问权限
4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口

1.2 创建普通视图

CREATE [OR PEPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY]

解释：
OR REPLACE：若所创建的视图已经存在，ORACLE自动重建改视图
FORCE：不管基表是否存在ORACLE都会自动创建该视图
subquery：一条完整的SELECT语句，可以在该语句中定义别名
WITH CHECK OPTION：插入或修改的数据必须满足视图定义的约束
WITH READ ONLY：该视图不能执行任何DML操作

1.2.1 创建普通视图

create view view_owners1 as
select * from t_owners where ownertypeid = 1

1.2.2 创建带检查约束的视图

create view view_address2 as
select * from t_address where areaid = 2
with check option

1.2.3 创建只读视图的创建与使用

create or replace view view_owners1 as
select * from t_owners where ownertypeid = 1
with read only

1.2.4 强制创建视图

create force view view_test as  -- view_test不存在
select * from t_test

1.2.5 创建复杂视图

1.2.5.1 多表关联

create or replace view view_owners as 
select ow.id 业主编号, ow.name 业主名称,ot.name 业主类型  from t_owners ow, t_ownertype ot
where ow.ownertypeid = ot.id

1.2.5.2 分组聚合统计的复杂视图

create view view_accountsum as
select year,month,sum(money) money from t_account
group by year,month
order by year,month

1.3 创建物化视图

1.3.1 语法

CREATE METERIALIZED VIEW view_name 
[BUILD IMMEDIATE | BUILD DEFERRED]
REFRESH [FAST|COMPLETE|FORCE]
[
ON  [COMMIT | DEMAND] | START WITH [start_time] NEXT(next_time) 
]
as

解释：
BUILD IMMEDIATE 是在创建物化视图的时候就生成数据（默认）
BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据

COMPLETE 完全
FAST 增量更新
FORCE 自动（默认）

ON COMMIT 在基表做提交操作时刷新物化视图
ON DEMAND 手动刷新(默认)

1.3.2 示例

1.3.2.1 创建手动刷新的物化视图

create materialized view mv_address1 
as
select ad.id, ad.name, ar.name arname  from t_address ad, t_area ar
where ad.areaid = ar.id

1.3.2.2 创建自动刷新的物化视图

create materialized view mv_address2
refresh COMPLETE
on commit  -- 自动刷新
as
select ad.id, ad.name, ar.name arname  from t_address ad, t_area ar
where ad.areaid = ar.id

1.3.2.3 创建不生成数据的物化视图

create materialized view mv_address3
build deferred  -- 创建不产生数据
refresh
on commit
as
select ad.id, ad.name, ar.name arname  from t_address ad, t_area ar
where ad.areaid = ar.id

注意：第一次 必须需要手动刷新，才能查看到数据

1.3.2.4 创建增量刷新的物化视图

注意：前提是必须创建物化视图日志：基表发生了哪些变化，用这些记录去更新物化视图

第一步：创建物化视图日志

create materialized view log on t_address with rowid
create materialized view log on t_area with rowid

第二步：创建增量刷新的物化视图
注意：创建增量刷新的物化视图中的语句中，必须有基表的ROWID，通过ROWID查询关联

create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid,ad.id, ad.name, ar.name arname  from t_address ad, t_area ar
where ad.areaid = ar.id

1.3.2.5 执行物化视图的手动刷新

语法：

begin
  DBMS_MVIEW.refresh('视图','C');
end;

示例：

begin
  DBMS_MVIEW.refresh('mv_address1','c');
end;

1.4 查询视图

1.4.1 语法

select * from 视图名称 where 条件

1.4.2 示例

select * from view_owners1  where addressid = 1

1.5 修改视图数据

1.5.1 语法

update 视图 set 字段 = 值 where 条件

1.5.2 示例

update view_owners1 set name = '范小冰' where id = 1

注意：1、只读视图不能修改；2、修改带检查约束的视图，检查约束不能修改；3、对于复杂视图，只能修改键保留表里面的数据，对于分组聚合统计的复杂视图不能修改，没有键保留表

-- 无法修改成功的语句，因为该视图的条件时 areaid = 2
update view_address2 set areaid = 3 where id =4

2 序列

2.1 语法

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增如果是负数就递减 默
认是1
[START WITH n] 1/开始的值,递增默认是 minvalue 递减是 maxvalue
[MAXVALUE n|NOMAXVALUE)] //最大值
[(MINVALUE n|NOMINVALUE)//最小值
KCYCLE|NOCYCLE]] //循环/不循环（默认不循环）
[(CACHE n|NOCACHE)];//分配并存入到内存中（默认缓存20个）

2.2 创建序列

2.2.1 创建普通序列

create sequence seq_test

2.2.2 创建复杂序列

2.2.2.1 创建有最大值的非循环序列

create sequence seq_test1
maxvalue 20;

2.2.2.2 创建有开始值和最大值的非循环序列

create sequence seq_test2
increment by 10
start with 10
maxvalue 100

2.2.2.3 创建有开始值和最大值的循环序列

create sequence seq_test3
increment by 10
start with 10
minvalue 10
maxvalue 210
cycle 

2.3 修改序列

2.3.1 语法

ALTER SEQUENCE 序列名称 修改的需求参数

2.3.2 示例

ALTER SEQUENCE seq_test MAXVALUE 5000 CYCLE

2.4 删除序列

2.4.1 语法

drop SEQUENCE 序列名称

2.4.1 示例

drop sequence seq_test5

3 同义词

3.1 创建同义词

同义词相当于别名

3.1.1 语法

create [public] SYNONYM synooym for objec

解释：其中synonym表示要创建的同义词的名称，object表示表，视图，序列

3.1.1 创建示例

3.1.1.1 创建私有同义词

私有同义词只有此用户才能使用

create synonym owners for t_owners

3.1.1.1 创建共有同义词

共有同义词所有用户都能够使用

create public synonym owners2 for t_owners

4 索引

4.1 概述

1）索引是什么？
(1) 一种供服务器在表中快速查找一行的 ‘数据库结构’
(2) 可以理解为：一本书中的 ‘目录’
2）索引的优点
(1) 加快数据的 ‘检索速度’
(2) 可以保证列值的 ‘唯一性’（unique、主键）
(3) 实现表与表之间的 ‘参照完整性’（外键）
(4) 在使用 order by、group by 子句时，可以减少排序和分组的时间
3）索引的缺点
(1) 当对表数据进行 DML 操作的时候，索引自动维护，‘降低 DML 操作的速度’
(2) 索引需要占 ‘物理空间’，同数据表的 ‘表空间’ tablespace 一样
(3) 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
4）索引的使用原则
(1) ‘主键’ 和 ‘外键’ 会自动创建索引，无需人工操作
(2) 选择正确的表：经常检索包含大量数据的表中小于 15% 的行
(3) 选择正确的列：多个表之间的关联关系
(4) 合理安排索引列：（A，B，C） A 最常用，B 次之，C 最末

4.2 创建索引语法

4.2.1 创建普通索引

create index 索引名 on 表名(列明)

4.2.2 创建唯一索引

唯一索引：不能重复，主键索引自动创建的唯一索引

create unique index 索引名称 on 表明(列名)

4.2.3 创建复合索引

复合索引：顺序也有要求，按照查询习惯建复合索引

create  index 索引名称 on 表明(列名1, 列明2......)

4.2.4 创建反向键索引

反向键索引：(顺序)10进制 → 二进制 → 反向键 → 十进制(随机数))，层级更均衡
反向键索引优点：
1、使用传统的B-tree索引，当索引的列是按顺序产生时，相应的索引键值会基本分布在同一个叶块中。当用户对该列进行操作时，难免会发生索引块的争用
2、使用反向索引，将索引列的键值进行反转，实现顺序的键值分散到不同的叶块中，从而减少索引块的争用。

create  index 索引名称 on 表明(列名) reverse;

4.2.4 创建位图索引

使用场景：位图索引适合创建在低基数列上
位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
优点：减少响应时间，节省空间占用
位图索引只在使用等于时生效，范围查询时位图索引不生效

create bitmap index 索引名称 on 表明(列名)

4.3 创建索引示例

4.3.1 创建普通索引

create index index_owners_name on t_owners(name);

4.3.2 创建唯一索引

create unique index index_owners_meter on t_owners(watermeter);

4.3.3 创建复合索引

create index index_owners_ah on t_owners(addressid,housenumber);

4.3.4 创建位图索引

create bitmap index index_owners_typeid on t_owners(ownertypeid)