-- Oracle 视图,序列,索引
-- 简单视图的创建
create or replace view view_owners as (select * from t_owners t where  t.ownertypeid=1);
-- 利用视图进行查询
select * from view_owners where ownertypeid=1;
-- 更新视图,视图改变,数据表也跟着改变
update view_owners set name='王刚' where id=2;
select * from t_owners where id=2;
-- 更新数据表,视图也跟着改变
update t_owners set name='张无忌' where id=8;
select * from view_owners;
-- 带检查约束的视图
-- 根据地址(t_address)创建视图view_address2,内容为区域id为2的记录
create or replace view view_address2 as (select * from t_address where id=2) with check option;
-- 更新语句
 update view_address2 set name='东城成' where id=4;
 -- 只读视图的创建 ,将view_address2修改为只读视图
 create or replace view view_address2 as (select * from t_address where id=2) with read only;
 -- 创建带错误的视图view_temp,创建的视图表可能不存在,但是以后可能存在
 create or replace force view view_temp as select * from t_temp;  
 -- 复杂视图的创建
 -- 多表关联的查询,创建视图(view_owner),显示业主编号,业主名称,业主类型名称
 create or replace view view_owner as (
 select t.id,t.name 业主名称,ot.name 业主类型名称 from t_owners t,t_ownertype ot where t.ownertypeid=ot.id
 );
 -- 视图查询
 select * from view_owner ;
 -- 视图修改,
 update view_owner  set 业主名称='张安峰' where id=1;   
 update view_owner  set 业主类型名称='普通居民' where id=1;  -- 错误,不能更新 ,
-- 分组聚合统计
create or replace view view_account as
select year,month,sum(money) montysum from t_account group by year,month order by year,month;
-- 查询
select * from view_account;
-- 物化视图
-- 创建手动刷新的物化视图
create materialized view  my_address as
select ad.id,ad.name adname,ar.name arname from t_address ad,t_area  ar
where ad.areaid=ar.id;
-- 查询
select * from my_address;
-- 向地址插入一条记录,--插入的记录没有出现在物化视图里
insert into t_address values(9,'物美',5,4);
-- 手动刷新物化视图
begin
DBMS_MVIEW.refresh('my_address','C');
end;
-- 创建自动刷新的物化视图
create materialized view  my_address1
refresh  on commit
as
select ad.id,ad.name adname,ar.name arname from t_address ad,t_area  ar
where ad.areaid=ar.id;
-- 查询
select * from my_address2;
-- 创建时不生成数据的物化视图
create materialized view  my_address2 build deferred
refresh  on commit
as
select ad.id,ad.name adname,ar.name arname from t_address ad,t_area  ar
where ad.areaid=ar.id;
-- 手动刷新生成数据
begin DBMS_MVIEW.refresh('my_address2','C');end;
-- 创建增量刷新的物化视图,首先创建物化视图日志
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid;
create materialized view log on t_owners with rowid;
-- 创建物化视图
create materialized view my_address3 refresh fast as
select ad.rowid adrowid,ad.id,ad.name adname,ar.rowid arrowid,ar.name arname from t_address ad,t_area  ar
where ad.areaid=ar.id;
-- 插入数据
insert into t_address values(11,'西5旗',7,6);
select * from my_address3;
-- 手动刷新
begin DBMS_MVIEW.refresh('my_address2','C');end;
-- 序列,创建使用简单序列
create sequence seq;
-- 提取下一个值
 select seq.nextval from dual;
-- 创建复杂序列,有最大值的非循环序列,cache默认缓存20个数,最大值需要比总缓存数大
create sequence seq_test1 increment by 10 start with 10 maxvalue 200 minvalue 5 cycle;
-- 提取序列的值
select seq_test1.nextval from dual;
-- 带缓存的数据
create sequence sseq_test6     increment by 10 start with 10 maxvalue 200 minvalue 5 cycle;
-- 删除序列
drop sequence sseq_test1;
-- 同义词(别名)
-- 为表t_owners 创建同义词
create synonym owners for t_owners;
-- 查询
select * from owners;
-- 索引 ,普通索引,为业主表的name创建索引
create index index_owner_name on t_address(name);
-- 创建两个字段的表
create table t_indextest(id number,name varchar(30));
-- 插入100万条记录
begin
  for i in 1..1000000
    loop
      insert into t_indextest values (i,'AB'||i);
      end loop;
      commit;
 end;
-- 根据name创建索引
create index index_name on t_indextest(name);
-- 查询数据
select * from t_indextest;
select * from t_indextest where id=765432;
select * from t_indextest where name='AB765432';
-- 创建唯一索引,业主表的水表编号
create unique index index_watermeter on t_owners(watermeter);
-- 复合索引,根据地址和门牌号对用户表创建索引
create index index_owners on t_owners(housenumber,addressid);
-- 反向键索引
create index index_id on t_owners(id) reverse;
-- 位图索引,我们在t_owners表的ownertypeid列上建立位图索引,适合创建在低基数列上
create bitmap index index_ownertype on t_owners(ownertypeid);