索引、视图、序列

索引

索引类似于书的目录，可以提高查找效率。

索引的优点：

• 提高联表查询的速度
• 实施值的唯一性

索引的缺点/使用原则：

• 耗费时间、存储空间
• 数据发生变化，索引也需更新
• 不应创建不频繁使用的索引

索引的创建

一、语法格式

create [UNIQUE] index 索引名1
on 表名1(列名1[ASC|DESC],...,列名n[ASC|DESC]);

对该语句可做如下中文理解，在表名1的列1…列名n创建名为索引名1的索引/唯一索引

二、注意事项

• UNIQUE为索引类型，表明定义索引的列中无重复值
• DESC、ASC为排序规则，默认为asc
• oracle自动将主键约束的列或唯一索引的列创建唯一索引

三、示例
（1）在emp的empno列创建名为d_index的唯一索引

create unique index d_index
on emp(empno);

运行后效果如下：

索引修改

一、语法格式

--根据原来的索引结构重新建立索引
alter index 索引名 rebuild;

索引删除

drop index 索引名;

视图

视图本身不存储数据是虚表，所有视图结果集来自于查询的基表数据，视图与基表的数据同步更新

视图类型如下：

• 只读视图（with read only）——只能读取，无法更新
• 检查约束视图(with check option)——部分数据更新，前提有检查约束限制

视图优点如下：
①.通过简短视图名简化复杂的查询语句
②.屏蔽基表数据结构,保证数据安全(无法更新基表)
③.不同客户角度,访问自己相关业务的视图即可。举个栗子吧，单个部门只能查看单个部门营业数据
④.视图本质具有独立逻辑结构,并且不占用空间存储

视图创建

一、语法格式

create [OR REPLACE][FORCE] view 视图名 别名
as select语句
[with read only|with check option];

• or replace——如果其中存在同名视图则覆盖替换
• force——基表不存在亦可创建视图，但无法使用，基表创建后可正常使用
• with read only为创建只读视图、with check option为创建检查约束视图
• 视图中不允许出现重复列
• 如果视图数据来自联表查询、或者select子句中包含聚合函数、group by 、distinct、rownum等，则无法对此视图进行增删改

二、授权
普通用户第一次创建视图需要授予权限：

--切换到system用户输入下述语句
grant create view to 用户名;

三、键保留表、非键保留表

复杂视图中可以更新键保留表的列，不可以更新非键保留表的列

对键保留表的解释如下：
若存在a,b两表，a中aid在a中做主键，在b中做外键或者为普通字段，a,b两表联查以aid为条件相连则a做主键的表为非键保留表（a表），键保留表为aid不做主键的表(表b),相关图例如下：

--创建a表及插入相关数据语句
create table a(
       aid number(21) primary key,
       name varchar2(24)
);
insert into a values(2,'李四');
--创建b表及插入相关数据语句
create table b(
       aid number(21),
       sex varchar2(9)
);
insert into b values(2,'女');
--创建视图
create view v1
as select a.*,b.sex from a,b where a.aid=b.aid;
--利用视图修改a表值
UPDATE V1
set NAME='1'
WHERE AID=2;
--利用视图修改b表值
UPDATE V1
set SEX='1'
WHERE AID=2;
--查看原表值
SELECT * FROM B;

运行效果如下（结果过多故在此贴两图）：


有个特殊情况，如果AID既在a表中做主键又在b表中做主键，则a,b两表均会成为键保留表，示例如下：

--创建a表及插入相关数据语句
create table a(
       aid number(21) primary key,
       name varchar2(24)
);
insert into a values(2,'李四');
--创建b表及插入相关数据语句
create table b(
       aid number(21) primary key,
       sex varchar2(9)
);
insert into b values(2,'女');
--创建视图
create view v1
as select a.*,b.sex from a,b where a.aid=b.aid;
--利用视图修改a表值
UPDATE V1
set NAME='1'
WHERE AID=2;
--查看原表值
SELECT * FROM A;
--利用视图修改b表值
UPDATE V1
set SEX='1'
WHERE AID=2;
--查看原表值
SELECT * FROM B;

运行效果如下：

视图删除

drop view 视图名;

序列

序列是oracle中自动生成序列的对象,类似于生活中的计数器

序列创建

一、语法格式

/*n为自然数*/
create sequence 序列名
[start with n]
[increment by n] 
[minvalue|maxvalue n| nomaxvalue]
[cycle|nocycle]
[cache n|nocache];

• start with n 表示序列从n开始，默认值为1
• increment by n定义两个序列号之间间隔，若为负数则递减，默认值为1
• maxvalue 表示序列生成器能产生最大值，nomaxvalue为默认表示无最大值
• minvalue 表示序列生成器能产生最小值，nomaxvalue为默认表示无最小值
• cycle与nocycle表示达到限制后是否循环，如果接cycle且设置了最小值最大值则第一次到达最大值后循环从最小值开始
  相关示例如下：

--创建序列
create SEQUENCE S1
MAXVALUE 5
MINVALUE 0
NOCACHE
CYCLE;
--插入数据7次
insert INTO A VALUES(S1.nextval);
--查询表中数据
SELECT * FROM A;

查询后表A数据如下：

-cache为缓冲区大小设置此处不深究

序列修改

alter sequence 序列名
[increment by n] 
[minvalue|maxvalue n| nomaxvalue]
[cycle|nocycle]
[cache n|nocache];

• 不能修改序列初始值

序列使用

--取序列当前值
序列名.currval
--取序列下一值且序列值增加
序列名.nextval

• 访问序列的第一个值应使用nextval

具体示例如下：
（1）向a表中插入5个利用序列生成的学号

--运行5次该语句即插入5个学号
insert into a
values('s'||M_SEQ3.Nextval);

效果如下：

序列删除

drop sequence 序列名;

拓

在Oracle 11.2中，当创建一个空表或者空分区或空序列时，为了加快创建速度，Oracle并不会立即分配初始段和空间，实际的表段被延迟到第一行数据插入时创建。

--延迟技术关闭，同理开启只需将False改为true即可,该命令需要管理员账户登录操作
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;