Oracle之索引、视图、序列(七)
1、索引
1、索引概述
- 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
- 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
- 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
- 在删除一个表时,所有基于该表的索引会自动被删除
- 通过指针加速 Oracle 服务器的查询速度
- 通过快速定位数据的方法,减少磁盘 I/O
2、创建索引
- 自动创建:在定义 primary key 或 unique 约束后系统自动在相应的列上创建唯一性索引
- 手动创建:用户可以在其它列上创建非唯一的索引,以加速查询
create index 索引名称 on 表名(列名[,列名,…])
index:索引关键字
如:
create index ind_emp_name on emp(ename);
3、适合创建索引的场景
- 列中数据值分布范围很广
- 列经常在 where 子句或连接条件中出现
- 表经常被访问而且数据量很大,访问的数据大概占数据总量的 2%到 4%
4、不适合创建索引的场景
- 表很小
- 列不经常作为连接条件或出现在 where 子句中
- 查询的数据大于 2% 到 4%
- 表数据增删改比较频繁
5、索引的分类
单列索引:给某个列建一个索引,叫单行索引
联合索引:给多个列创建一个索引,叫联合索引。如果联合索引在查询时使用了第一个索引列作为条件,那么它会使用索引提高查询效率。如果使用了非第一个索引列作为条件,它不会使用索引。
注意:使用索引列作为查询条件时,不要在索引列上进行数字运算
create index ind_emp_sal on emp(sal);
select * from emp where sal*12>10000; --这样写条件不会使用索引
select * from emp where sal>10000/12; --这样他就会使用索引
6、删除索引
drop index 索引名;
drop index ind_emp_sal;
drop index ind_emp_name;
删除索引的注意事项:
- 只有索引的拥有者或拥有 drop any index 权限的用户才可以删除索引
- 删除索引是不可回滚的
2、视图
1、视图概述
- 视图以经过定制的方式显示来自一个或多个表的数据
- 视图可以视为“虚拟表”或“存储的查询”
- 创建视图所依据的表称为“基表”
2、视图的优点
- 提供了另外一种级别的表安全性
- 隐藏数据的复杂性
- 简化用户的SQL命令
- 隔离基表结构的改变
- 避免重复访问相同的数据
- 通过重命名列,从另一个角度提供数据
3、创建视图的语法
create [or replace] [force] view view_name[(别名1,别名2,…)]
as select语句 [with check option] [with read only];
view:视图关键字
or replace:如果视图已经存在,则替换旧视图
force:即使基表不存在,也可以创建该视图,但是该视图不能正常使用,当
基表创建成功后,视图才能正常使用。
view_name:视图名称,须符合标识符命名规范
别名:别名的数量要和后面的查询语句的列的数量相同
with check option 子句可以保证让你只能在视图的条件之内对视图进行DML
with read only:表示是一个只读视图
create view view_dept_emp as select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
select * from view_dept_emp;
注:创建视图时可以使用单行函数,分组函数和表达式必须为使用函数或者表达式的字段指定名字
4、简单视图和复杂视图的区别
特性 | 简单视图 | 复杂视图 |
---|---|---|
表的数量 | 一个 | 一个或多个 |
函数 | 没有 | 有 |
分组 | 没有 | 有 |
DML 操作 | 可以 | 有时可以 |
5、视图上使用 DML 语句的限制
1、当视图定义中包含以下元素之一时不能使用update
- 组函数
- GROUP BY子句
- DISTINCT 关键字
- ROWNUM 伪列
- 列的定义为表达式
2、当视图定义中包含以下元素之一时不能使insert
- 组函数
- GROUP BY 子句
- DISTINCT 关键字
- ROWNUM 伪列
- 列的定义为表达式
- 表中非空的列在视图定义中未包括
3、屏蔽 DML 操作
- 可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作
- 任何 DML 操作都会返回一个Oracle server 错误
6、删除视图
删除视图只是删除视图的定义,并不会删除基表的数据
drop view 视图;
3、序列
1、序列概述
序列可供多个用户用来产生唯一数值的数据库对象
1、自动提供唯一的数值
2、共享对象
3、主要用于提供主键值
4、将序列值装入内存可以提高访问效率
5、如果不讲序列的值装入内存(nocache),可使用表 user_sequence 查看序列当前的有效值
6、序列在以下情况时出现裂缝:
- 回滚
- 系统异常
- 多个表同时使用同一序列
2、创建序列语法
create sequence 序列名
start with startValue
increment by inrValue
minValue Minvalue
maxValue maxValue
cache | nocache
cycle | no cycle;
sequence 序列关键字
start with startValue:指定序列的初始值,startValue 是一个自然数
increment by inrName:指定序列的值每次增加多少,in人Value 是一个自然数,一般用1
minValue minValue:指定序列的最小值,minValue 是一个自然数据
maxValue maxvalue:指定序列的最大值,maxValue 是一个自然数据
cache | no cache:指定序列缓存,默认缓存 20个
cycle | no cycle:指定序列是否循环生成
如:
--创建一个序列
create sequence my_seq start with 1 increment by 1;
3、序列的两个属性
nextval 返回序列中下一个有效的值,任何用户都可以引用
currval 取序列的当前值,序列值不会变
nextval 应在 currval 之前指定,否则会报 currval 尚未在此会话中定义的错误
如:
select my_seq nextval from dual;
select my_seq currval from dual; --不能直接取当前值
insert into 表名 values(my_seq.nextval,'值','值');
4、使用 alter sequence 语句修改序列
alter sequence stu_seq maxValue 5000 cycle;
修改序列的注意事项:
1、必须是序列的拥有者或对序列有 alter 权限
2、只有将来的序列值会被改变
3、不能更改序列的 start with 参数,要想更改此初始值,只能通过删除序列之后重建序列的方式实现
5、使用 drop sequence 语句删除序列,删除之后,序列不能再次被引用
drop sequence stu_seq;