Oracle数据库对象
注：以下数据库对象中， 物化视图、序列、同义词为Oracle数据库特有
视图
含义：视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。
使用视图的优点：
- 简化数据操作：视图可以简化用户处理数据的方式。
- 着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。
- 视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
- 提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。
语法：
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS SELECT语句
[WITH CHECK OPTION ]
[WITH READ ONLY]
--创建简单视图
create view view_owners1 as
select * from t_owners where ownertypeid=1;
--查询简单视图
select * from view_owners1 where addressid=1;
--修改视图数据
update view_owners1 set name='齐天' where id=1;
commit;
select * from t_owners;
--待检查约束的视图
create view view_address2 as
select * from t_address where areaid = 2
with check option;
select * from view_address2;
--无法修改成功的语句，因为该视图的条件是areaid=2
update view_address2 set areaid=3 where id = 4;
--只读视图
create or replace view view_owners1 as
select * from t_owners where ownertypeid=1
with read only;
--修改只读视图数据 不能修改成功
update view_owners1 set name='大圣' where id = 1 ;
--创建带错误的视图
create force view view_test as
select * from t_test;
--只是强行保存了语句，还是不能使用
select * from view_test;
--复杂视图-多表关联
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;
--查询复杂视图 (多表关联)
select * from view_owners where 业主类型='居民';
--修改复杂视图的数据 可以修改成功
update view_owners set 业主名称='齐天大圣' where 业主编号=4;
--不能修改成功
update view_owners set 业主类型='商业' where 业主编号=4;
--键保留表 ：把主键保留的那个表 才可以修改数据
--聚合统计的复杂视图
drop view view_account;
create view view_accountsum as
select year,month ,sum(money) money
from t_account
group by year , month
order by year, month;
select * from view_accountsum where year = '2012' and month = '03';
--不能修改
update view_accountsum set money = 10000 where year='2012' and month = '03';
物化视图
含义：视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。
物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。比普通视图的查询效率要快。
语法：
CREATE METERIALIZED VIEW view_name
【BUILD IMMEDIATE | BUILD DEFERRED】
REFRESH 【 FAST | COMPLETE | FORCE】
【ON COMMIT | DEMAND ] | START WITH (start_time) NEXT
(next_time)】
AS
查询语句
START WITH：自定自动刷新的起点时间
NEXT：指定自动刷新的间隔,start with and next合起来确定下次刷新的时间，如果在忽略了sart with ，只有next，那么刷新时间依据物化视图创建的时间和next来确定下次刷新的时间，如果只有start with 没有next，那么就会刷新一次。
--物化视图 提高查询效率，但会增加空间占用
--COMPLETE 完全刷新 把原来的物化视图删除，再重新查询一遍
--FAST 增量更新 在原有视图上增加更新，有条件限制
--FORCE （缺省）自动选择 自动选择完全还是增量更新
--ON COMMIT 在基表做提交操作时刷新物化视图
--ON DEMAND （缺省）手动刷新
--创建手动刷新的物化视图
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;
drop materialized view mv_address1;
--查询物化视图
select * from mv_address1;
--向基表插入数据
insert into t_address values(9,'东八儿',2,2);
commit;
--查询基表数据
select * from t_address;
--此时物化视图没有添加刚刚在基表中添加的数据
--执行下列语句来进行刷新 PL/SQL
begin
--此语句是Oracle内置的存储过程,C是完全刷新
DBMS_MVIEW.refresh('MV_ADDRESS1','C');
end;
/*
命令行执行以下语句也能刷新
SQL> EXEC DBMS_MVIEW.refresh('mv_address1','c')
PL/SQL procedure successfully completed
*/
--创建自动刷新的物化视图 --基表发生commit操作自动刷新物化视图
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid = ar.id;
--查询物化视图
select * from mv_address2;
--向基表中插入数据
insert into t_address values(10,'南八儿',2,2);
commit;
--创建时不生成数据的物化视图
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;
--查询物化视图
select * from mv_address3;
insert into t_address values(11,'北八儿',2,2);
commit;
--第一次必须手动执行刷新
begin
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;
--创建增量刷新的物化视图
--前提是必须创建物化视图日志：记录基表发生了哪些变化，用这些记录去更新物化视图
--with后面可以是主键或者rowid
create materialized view log on t_address with rowid;
create materialized view log on t_area with 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;
select * from mv_address4;
--插入数据
insert into t_address values(12,'西九儿',2,2);
commit;
insert into t_address values(13,'西十儿',2,2);
commit;
--删除数据
delete from t_address where id = 12;
commit;
--手动刷新
begin
dbms_mview.refresh('mv_address4','c');
end;
序列
含义：序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象 。
创建简单序列语法：create sequence 序列名称
通过序列的伪列来访问序列的值
NEXTVAL
返回序列的下一个值
CURRVAL
返回序列的当前值
注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。
创建复杂序列语法：
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
[START WITH n] //开始的值,递增默认是minvalue 递减是maxvalue [{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
--以下语句由WATERUSER用户执行
--创建简单序列
create sequence seq_test;
--查询序列的下一个值
select seq_test.nextval from dual;
--查询序列的当前值
select seq_test.currval from dual;
--有最大值的非循环序列
create sequence seq_test1
maxvalue 20;
--超过最大值会报错
select seq_test1.nextval from dual;
create sequence seq_test2
increment by 10
start with 10
maxvalue 100;
select seq_test2.nextval from dual;
create sequence seq_test3
increment by 10
start with 10
minvalue 5
maxvalue 100;
--最小值作用没体现，还是从10开始
select seq_test3.nextval from dual;
--循环的序列
--一次循环的值的个数要>缓存的值的个数（默认缓存20个）
drop sequence seq_test4;
create sequence seq_test4
increment by 10
start with 10
minvalue 10
maxvalue 210
cycle;
--多一个就行
create sequence seq_test5
increment by 10
start with 10
minvalue 10
maxvalue 201
cycle;
select seq_test5.nextval from dual;
create sequence seq_test6
increment by 10
start with 10
minvalue 10
maxvalue 100
cycle
cache 5;
--一次缓存的数有多少 , cache值*增长值
--一次循环的值不能小于 , 一次缓存的数
--不循环的话就无上述约束
create sequence seq_test7
increment by 10
start with 10
minvalue 10
maxvalue 100
cache 20;
----序列不真的属于任何一张表，但是可以逻辑和表做绑定。
----序列：默认从1开始，依次递增，主要用来给主键赋值使用。
----dual：伪表，只是为了补全语法，没有任何意义。
create sequence s_person;
select s_person.nextval from dual;
----添加一条记录
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;
select * from person;
同义词
含义：同义词实质上是指定方案对象的一个别名。
同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。
语法：
create [public] SYNONYM 同义词名字 for object;
object 表示表， 视图 ，序列等我们要创建同义词的对象的名称。
--创建私有同义词
create synonym owner for t_owners;
select * from owner;
--创建公有同义词
create public synonym owners2 for t_owners;
select * from owners2;
--收费员表
create table t_ttt(
id number,
name varchar(30) --varchar是varchar2的同义词
);
--以下语句由SYSTEM用户执行
--私有同义词其他用户不能直接访问
select * from owner;
--因为有DBA权限所以可以调wateruser用户来访问
select * from wateruser.owner;
select * from wateruser.t_owners;
--公有同义词 可以直接访问
select * from owners2;
索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低
i/o 次数从而提高数据访问性能 。
普通索引：
create index 索引名称 on 表名 列名
唯一索引：
create unique index 索引名称 on 表名 列名
复合索引：
create index 索引名称 on 表名 (列名 列名…)
反向键索引：
create index 索引名称 on 表名 列名 reverse
应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。
位图索引：
create bitmap index 索引名称 on 表名 列名
使用场景：位图索引适合创建在低基数列上
位图索引不直接存储ROWID ，而是存储字节位到 ROWID 的映射
优点：减少响应时间，节省空间占用
--创建普通索引
create index index_owners_name on t_owners(name);
select * from t_owners where name='张哲';
--性能测试 创建一个表，向表中插入100万条记录
create table t_indextest(id number,name varchar2(30));
begin
for x in 1..1000000
loop
insert into t_indextest values(x,'aa'||x);
end loop;
commit;
end;
--创建索引
create index index_test on t_indextest(name);
--最短时间0.047s
select * from t_indextest where id=766664;
--最短时间0.016s
select * from t_indextest where name='aa766664';
--获取rowid AAAM3lAAGAAAAjSACQ
select rowid,t.* from t_indextest t where name='aa766664';
--用rowid查询 0.015
select * from t_indextest where rowid='AAAM3lAAGAAAAjSACQ';
--创建唯一索引 同时也创建了唯一约束
--如果该字段有重复数据就不能创建
create unique index index_owners_meter on t_owners(watermeter);
--创建复合索引 一棵树 查询顺序和建索引的顺序一样
create index index_owners_ah on t_owners(addressid,housenumber);
select * from t_owners where addressid=1 and housenumber='1-3';
--方向键索引，用于原本有顺序的数据，将其顺序打乱再索引到树上，防止数太深。
--位图索引 在低基数列上建
--每个值建一个位图
--只能用=查询 不能用> < like等的范围查询 但是也能查出结果，不过没用到索引
create bitmap index 索引名 on 表名(列名);
上一篇： SEO B2B行业网站优化经验
下一篇： 数据库主键方案