10索引、视图、同义词和序列
10.1 索引对象
10.1.1 索引概述
创建索引时,Oracle会自动在用户的默认表空间或指定的表空间中创建一个索引段,为索引数据提供空间。
需要注意的几个问题:
限制索引个数。索引主要用于加快查询速度,但会降低 DML(Data Manipulation Language)速度。索引越多,DML 操作速度越慢。极大的影响 INSERT 和 DELETE 操作速度,因此需要权衡查询和DML的需求。
指定索引块空间使用参数。基于表创建索引时,Oracle会按照 PCTFREE 参数将相应的表列数据添加到索引块,并预留部分空间。该预留空间是为 INSERT 操作准备的。如果将来在表上执行大量的 INSERT 操作,那么应该在建立索引时设置较大的 PCTFREE。
在大表上建立索引时,使用 NOLOGGING 选项可以最小化重做记录。降低索引建立时间、提高索引并行建立的性能。
不要在小表上建立索引。
为了提高多表连接的性能,应该在连接字段上建立索引。
10.1.2 创建索引
创建索引时,Oracle首先对将要建立索引的字段进行排序,然后将排序后的字段值和对应记录的 ROWID 存储在索引段中。
1、B树索引
这是默认的索引类型。以B(balance)树结构组织并存储索引数据。这种索引方式可以确保无论索引条目位于何处,都只需要花费相同的I/O获取它。
2、位图索引
基数表示在索引列中是指所有取值的数量与表中行的数量的比。基数低,指的是索引列所有取值的数量比表中行的数量少(比如说,性别字段有1000行记录,但是性别的取值却只有男、女两种,这就是基数低,基数为2/1000 =0.2%)。Oracle推荐当一个列的基数小于1%时,这些列不再适合建立B树索引,而适用于位图索引。
3、反向索引
是一种特殊的B树索引,在顺序递增列上建立索引时非常有用。在存储结构方面与常规的B树索引相同。它会反向索引字段的每个数据值,然后再进行索引。这样使得有序数值变得无序,分布比原来有序的数值更加均匀。
4、基于函数的索引
是基于B树索引,但它存放的数据是由表中数据应用函数后得到的,而不是直接存放在表中的数据本身。
5、语法格式
CREATE [UNIQUE]|[BITMAP] INDEX index_name
ON tab_name{[col_name1[ASC|DESC][,col_name2…]]|[expression]}
[REVERSE]
[PCTFREE number]
[TABLESPACE tablespace_name]
[STORAGE{INITIAL number [K|M]}];
UNIQUE:表示建立唯一性索引。
BITMAP:表示建立位图索引。
ASC|DESC:用于指定索引的排列顺序,ASC 表示升序排列,DESC 表示降序排列,默认为 ASC。
REVERSE:表示建立反向索引。
expression:可以使用表达式代替字段名,建立基于函数的索引。例:
create index ind_empjob on emp(lower(job));
其余参数都是用于指定存储空间、数据块参数设置等等。
10.1.3 修改索引
1、合并索引
主要用于清理碎片。
ALTER INDEX index_name COALESCE [DEALLOCATE UNUSED];
2、重建索引
ALTER INDEX index_name REBUILD
[REVERSE]
[PCTFREE number]
[TABLESPACE tablespace_name]
[STORAGE{INITIAL number [K|M]}];
如果使用 REVERSE 关键字,会重建反向索引。
重建索引实际上是在指定的表空间内重新建立一个新的索引,然后再删除原来的索引。
10.1.4 删除索引
DROP INDEX index_name;
10.2 视图对象
视图是一个虚拟表,数据库只在数据字典中存储视图的定义信息。可以通过创建替换触发器(详见6.3.4 替换触发器)对视图进行 DML 操作。
10.2.1 创建视图
CREATE [OR REPLACE] VIEW view_name [(alias1[,alias2,…])] AS
select_sentence
[WITH CHECK OPTION] [CONSTRAINT constraint_name]
[WITH READ ONLY];
WITH CHECK OPTION:用于指定在视图上定义的条件(检查)约束,可以定义该约束名。
WITH READ ONLY:用于定义只读视图。
注意创建视图只能使用 AS 关键字,与过程、函数、触发器和包区分开。
10.2.2 重新编译视图
ALTER VIEW view_name COMPILE;
10.2.3 删除视图
DROP VIEW view_name;
10.3 同义词对象
同义词是表、索引、视图等模式对象的一个别名。在开发数据库应用程序时,应尽量避免直接引用表、视图或其他数据对象的名称,而引用这些对象的同义词。可以避免当管理员对数据库对象做出改动和变动之后,必须重新编译应用程序;使用同义词后,即使引用对象发生变化,也只需在数据库中对同义词进行修改,不必对应用程序做任何改动。
某模式创建同义词必须具有相应权限。创建公有同义词需具有create public synonym系统权限,在当前模式创建私有同义词需具有create synonym系统权限,在其他模式创建私有同义词需具有create any synonym系统权限。
10.3.1 公有同义词
CREATE PUBLIC SYNONYM syn_name FOR tab_name;
公有同义词属于public用户组,所有的用户都可以直接引用它。
10.3.2 私有同义词
CREATE SYNONYM syn_name FOR tab_name;
私有同义词只有当前模式(用户)可以直接引用,其他用户在引用时必须带模式名。
10.3.3 删除同义词
DROP PUBLIC SYNONYM syn_name;--删除公有同义词
DROP SYNONYM syn_name;--删除私有同义词
删除同义词后,同义词的基础对象不受到任何影响,但是所有引用该同义词的对象将处于 INVALID(无效的)状态。
10.4 序列对象
用于生成一系列唯一数字的数据库对象,序列会自动生成顺序递增的序列号,以实现自动提供唯一的主键值。序列可以在多用并发环境中使用,并且可以为所有用户生成不重复的顺序数字,而不需要额外的 I/O 开销。序列并不占用实际存储空间,只是在数据字典中保存它的定义信息。
10.4.1 创建序列
CREATE SEQUENCE seq_name
[START WITH number]
[INCREMENT BY number]
[MINVALUE number|NOMINVALUE]
[MAXVALUE number|NOMAXVALUE]
[CACHE number|NOCACHE]
[CYCLE number|NOCYCLE]
[ORDER|NOORDER];
START WITH:指定序列起始值,默认情况下,递增序列起始值为 MINVALUE,递减序列起始值为 MAXVALUE。
INCREMENT:指定序列的增量。正数为递增序列,负数为递减序列,默认值为1(即递增序列)。
MINVALUE:指定序列生成的最小值。
MAXVALUE:指定序列生成的最大值。
CACHE:指定是否产生序列号预分配,并存储在内存中。
CYCLE:当序列达到极值时,可以复位并继续下一次循环。NOCYCLE:当序列达到极值时,不能复位循环,若再获取下一个值时会返回错误。
ORDER:指定按照顺序生成序列。NOORDER:只能保证序列值得唯一性,不保证序列值的顺序。
10.4.2 使用序列
使用序列时会用到序列的两个伪列 NEXTVAL 和 CURRVAL。其中 NEXTVAL 是序列生成的下一个序列号,CURRVAL 是序列的当前序列号。**首次引用序列必须使用 NEXTVAL **。
例:在scott用户下创建seq_empno的序列。
create sequence seq_empno
maxvalue 99999
start with 9000
increment by 100
cache 50;
使用该序列为emp表的新记录提供empno。
insert into emp(empno,ename,deptno) values(seq_empno.nextval,'sm',20);
查询该序列的当前序列号。
select seq_empno.currval from dual;
10.4.3 管理序列
可以使用alter语句对序列属性进行修改,注意序列的起始值不能被修改!若要修改起始值,必须先删除序列再重建。
例:
alter sequence seq_empno
maxvalue 10000
increment by 50
cache 100;
10.4.4 删除序列
DROP SEQUENCE seq_name;
10.5 总结
1、视图、同义词和序列,只在Oracle的数据字典中保存其定义描述,不占用实际的存储空间。
2、实际上,在为表生成主键值时,通常是为表创建一个行级触发器,然后在触发器主题中使用序列值替换用户提供的值。