ORACLE 数据库
第 3 章
ORACLE 对象
一、视图
(一)什么是视图
视图是一种数据库对象,是从一个或者多个数据表或视图中导出的虚表,视图所对应的数据并不真正地存储在视图中,而是存储在所引用的数据表中,视图的结构和数据是对数据表进行查询的结果。
根据创建视图时给定的条件,视图可以是一个数据表的一部分,也可以是多个基表的联合,它存储了要执行检索的查询语句的定义,以便在引用该视图时使用。
使用视图的优点:
1.简化数据操作:视图可以简化用户处理数据的方式。
2.着重于特定数据:不必要的数据或敏感数据可以不出现在视图中。
3.视图提供了一个简单而有效的安全机制,可以定制不同用户对数据的访问
权限。
4.提供向后兼容性:视图使用户能够在表的架构更改时为表创建向后兼容接
口。
(二)创建或修改视图语法
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH READ ONLY]
选项解释:
OR REPLACE :若所创建的试图已经存在,ORACLE 自动重建该视图;
FORCE :不管基表是否存在 ORACLE 都会自动创建该视图;
subquery :一条完整的 SELECT 语句,可以在该语句中定义别名;
WITH READ ONLY :该视图上不能进行任何 DML 操作。
(三)删除视图语法
DROP VIEW view_name
(四)例子
需求:分页逻辑过于复杂,创建一个分页视图
-- 创建视图
create view t_user_page as
select * from (select id,addTime,userName,password,rownum r from (select id,addTime,userName,password from t_user order by id desc) where rownum <=20) t where t.r>10;
-- 查询视图
select * from t_user_page;
-- 创建或替换视图
create or REPLACE FORCE VIEW t_user_page as
select id,addTime,userName,password from t_user;
二、序列
(一)什么是序列
序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。
(二)创建与使用简单序列
创建序列语法:
create sequence 序列名称
通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
注意:我们在刚建立序列后,无法提取当前值,只有先提取下一个值时才能再次提取当前值。
提取下一个值
select 序列名称.nextval from dual
提取当前值
select 序列名称.currval from dual
create sequence t_user_id_seq;
select t_user_id_seq.nextval from dual;
select t_user_id_seq.currval from dual;
(三)创建复杂序列
语法:
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}];//分配并存入到内存中
1) INCREMENT BY:用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表Oracle序列的值是按照此步长递减的。
2) START WITH: 定义序列的初始值(即产生的第一个值),默认为1。
3) MAXVALUE: 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增Oracle序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
4) MINVALUE:定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是?10的26次方;对于递增序列,最小值是1。
5) CYCLE和NOCYCLE :表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
6) CACHE:(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。
大量语句发生请求,申请序列时,为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存,在发生大量申请序列语句时,可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大,因为在数据库重启时,会清空内存信息,预存在内存中的序列会丢失,当数据库再次启动后,序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。
7) NEXTVAL :返回序列中下一个有效的值,任何用户都可以引用。
8) CURRVAL :中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ,二者应同时有效。
(四)修改和删除序列
修改序列:使用 ALTER SEQUENCE 语句修改序列,不能更改序列的 START WITH 参数
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
删除序列:
DROP SEQUENCE 序列名称;
(五)查看序列
select * from user_sequences ;
可以加上查询条件
(六)插入语句使用序列
-- 使用当前序列
insert into t_user(id,addTime,userName,password)
values(t_user_id_seq.currval+100,sysdate,'张三','123456');
-- 使用下一个序列
insert into t_user(id,addTime,userName,password)
values(t_user_id_seq.nextval+100,sysdate,'张三','123456');
四、索引
(一)什么是索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。
索引是需要占据存储空间的,也可以理解为是一种特殊的数据。形式类似于 下图的一棵“树”,而树的节点存储的就是每条记录的物理地址,也就是我们提 到的伪列(ROWID)
(二)普通索引
语法:
create index 索引名称 on 表名(列名);
drop index 索引名称;
需求:给t_user表的userName创建索引
create index index_t_user_userName on t_user(userName);
索引性能测试:
创建一个两个字段的表
create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30)
);
编写 PL/SQL 插入 100 万条记录(关于 PL/SQL 我们在第四章会学到)
BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;
创建完数据后,根据 name 列创建索引
CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name);
执行下面两句 SQL 执行
SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';
我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短
(三)唯一索引
如果我们需要在某个表某个列创建索引,而这列的值是不会重复的。这是我们可以创建唯一索引。
语法:
create unique index 索引名称 on 表名(列名);
需求:建唯一索引
语句:
create unique index INDEX_T_INDEXTEST_ID on
T_INDEXTEST(id);
唯一索引执行搜索效率更高
(四)索引分类
适当的使用索引可以提高数据检索速度,那Oracle有哪些类型的索引呢?
1、b-tree索引:Oracle数据中最常见的索引,就是b-tree索引,create index创建的normal就是b-tree索引,没有特殊的必须应用在哪些数据上。
2、bitmap位图索引:位图索引经常应用于列数据只有几个枚举值的情况,比如上面说到过的性别字段,或者我们经常开发中应用的代码字段。这个时候使用bitmap位图索引,查询效率将会最快。
3、函数索引:比如经常对某个字段做查询的时候经常是带函数操作的,那么此时建一个函数索引就有价值了。例如:trim(列名)或者substr(列名)等等字符串操作函数,这个时候可以建立函数索引来提升这种查询效率。
4、hash索引:hash索引可能是访问数据库中数据的最快方法,但它也有自身的缺点。创建hash索引必须使用hash集群,相当于定义了一个hash集群键,通过这个集群键来告诉oracle来存储表。因此,需要在创建HASH集群的时候指定这个值。存储数据时,所有相关集群键的行都存储在一个数据块当中,所以只要定位到hash键,就能快速定位查询到数据的物理位置。
5、reverse反向索引:这个索引不经常使用到,但是在特定的情况下,是使用该索引可以达到意想不到的效果。如:某一列的值为{10000,10001,10021,10121,11000,....},假如通过b-tree索引,大部分都密集分布在某一个叶子节点上,但是通过反向处理后的值将变成{00001,10001,12001,12101,00011,...},很明显的发现他们的值变得比较随机,可以比较平均的分部在各个叶子节点上,而不是之前全部集中在某一个叶子节点上,这样子就可大大提高检索的效率。
6、分区索引和分区表的全局索引:这两个索引是应用在分区表上面的,前者的分区索引是对分区表内的单个分区进行数据索引,后者是对分区表的全表进行全局索引。分区表的介绍,可以后期再做单独详解,这里就不累述了。