Oracle 第4章 同义词、序列、视图、索引

Oracle 第3章 锁、表分区
Oracle 第5章 使用PL/SQL

Oracle 第4章 同义词、序列、视图、索引

1、技术目标

• 使用同义词
• 使用序列
• 创建视图
• 创建索引

2、什么是Oracle数据库对象？

Oracle 数据库对象又称模式对象，
数据库对象是逻辑结构的集合，最基本的数据库对象是表，
其他数据库对象包括：

• 同义词
• 序列
• 视图
• 索引

3、同义词

同义词是表、视图、序列、过程、程序包或者其他同义词的别名，使用
同义词访问对象无需指定模式前缀 ，例如，user1访问user2的表Test，
必须使用user2.Test，创建一个名为Test的同义词代表user2.Test，user1
就可使用Test来访问user2.Test

同义词的作用：

• 简化SQL语句
• 隐藏对象的名称和所有者
• 为分布式数据库的远程对象提供位置透明性
• 提供对对象的公共访问

注意：在使用同义词之前要确保用户已得到访问对象的权限

同义词有两种类型：

• 私有同义词
• 公有同义词

4、私有同义词

私有同义词只能被当前模式用户访问，其名称不能与当前模式的对象名相同，
其语法如下：
create [or replace] synonym [schema.] synonym_name
for [shema.]object_name;

语法说明：

• or replace 表示在同义词存在的情况下替换原同义词
• synonym_name 要创建的同义词名称
• object_name 指定要为其创建同义词的对象名

例如：



注意：要创建私有同义词必须拥有CREATE ANY SYNONYM系统权限

5、公有同义词

公有同义词可被所有数据库用户访问，公有同义词可隐藏基表的身份，
降低SQL语句的复杂性，系统在安装时会创建一些字典公有同义词，
比如，同义词"TAB"可用来查询用户所拥有的表和视图对象。
语法：
create [or replace] public synonym synonym_name
for [schema.]object_name;

例如：



注意：要创建公有同义词，必须拥有CREATE PUBLIC SYNONYM系统权限

6、同义词的使用

使用1： 显示当前用户所拥有的表和视图，使用公有同义词"TAB"
select * from TAB;

注意：如果该用户已经创建了名为TAB的表(本地对象)，本地对象优先

使用2：
可查询字典视图USER_SYNONYMS来查看用户创建的同义词详细信息

使用3： 删除同义词EMP
drop synonym EMP;

注意：删除同义词的语法为，
drop [public] synonym [shema.]synonym_name;

7、序列

• 序列是用于生成唯一、连续序号的对象
• 序列通常用来自动生成主键或唯一键的值
• 序列可以是升序的，也可以是降序的

使用CREATE SEQUENCE语句创建序列，语法为：
    create sequence sequence_name
        [start with integer]
        [increment by integer]
        [MAXVALUE integer|NOMAXVALUE]
        [MINVALUE integer|NOMINVALUE]
        [cycle|nocycle]
        [cache integer|nocache];

语法说明：

start with，指定第一个序列号，对于升序序列，默认值为序列的
    最小值，对于降序序列，默认值为序列最大值
increment by，指定序号之间的间隔，默认值为1，如给负数值则
    序列按降序排列
MAXVALUE，指定序列可生成的最大值
NOMAXVALUE(默认选项)，如果指定了NOMAXVALUE，升序序列的
    最大值将为10的27次方
MINVALUE，指定序列的最小值，MINVALUE必须小于或等于start with
    指定的值以及MAXVALUE
NOMINVALUE(默认选项)，如果指定了NOMINVALUE，升序序列的
    最小值将为1，降序序列的最小值将为-10的26次方
cycle，指定序列在达到最大值或最小值后，继续从头开始生成值
nocycle(默认选项)，指定序列在达到最大值或最小值后，不会再
    继续生成值
cache，该选项可预先分配一组序号，并将其保留在内存中，可
    更快的访问序列号，当用完缓存中的所有序号时，将生成
    另一组数值，并将其保留在缓存中
nocache，使用该选项，将不会预先分配序列号，如果忽略cache
    和nocache，Oracle将默认缓存20个序列号

8、序列的使用

通过序列的伪列可访问序列的值，伪列有：

• NEXTVAL：创建序列后第一次使用nextval时，将返回该序列的初始值，以后再使用时，将使用increment by中的值递增
• CURRVAL：返回序列的当前值

使用1： 给学员表信息表创建序列，在添加学员信息是使用序列生成学号，

--创建studentId序列(类似标识列，其值可自动增长)
create sequence studentId
start with 1
increment by 1;
--添加学员信息，使用序列的值作为学号
insert into tbl_stuInfo
(stuId, stuName, stuBirthday)
values (studentId.NEXTVAL, '张4', '15-9月-2010');

使用2： 查看序列的当前值
select studentId.CURRVAL from dual;

9、更改序列

修改序列的定义使用alter sequence语句，可执行如下操作：

• 设置或删除MINVALUE或MAXVALUE
• 修改增量值
• 修改缓存中的序列号数量

语法如下：
    alter sequence sequence_name
        [increment by integer]
        [MAXVALUE integer | NOMAXVALUE]
        [MINVALUE integer | NOMINVALUE]
        [CYCLE | NOCYCLE]
        [CACHE integer | NOCACHE];

注意：不能修改序列的 start with 参数，升序序列的最小值应小于最大值

使用： 为序列设置一个新的MAXVALUE，并打开CYCLE，
alter sequence 序列名
    MAXVALUE 5000
    CYCLE;

提示：可通过查询USER_SEQUENCES视图获取用户所建序列的详细信息

10、删除序列

使用drop sequence语句可删除序列，还可使用此语句重新开始一个序列，
就是先删除序列，再重新创建该序列，语法 为：
drop sequence [schema.]sequence_name;

11、视图

视图相当于将一个查询(select)语句存储下来，将该查询的结果
当成一张表("虚拟表")，可以针对这张表进行各种查询等操作，
所以视图可以视为"虚拟表"或"存储的查询"，创建视图所依据的
表称为"基表"(查询语句中出现的表)

视图的优点有：

• 提供了另外一种级别的表安全性
• 隐藏的数据的复杂性
• 简化的用户的SQL命令
• 隔离基表结构的改变
• 通过重命名列，从另一个角度提供数据

创建视图的语法如下：
create [or replace] [force | noforce] view view_name
[(alias[, alias]...)]
as
select_statement(查询语句)
[with check option [CONSTRAINT constraint]]
[with read only];

语法说明：

or replace，如果视图存在就重新创建该视图
force，无论基表是否存在都将创建视图
noforce(默认值)，基表存在才会创建视图
view_name，视图名
alias，查询中的表达式或列的别名，其数量必须与
    查询中出现的表达式数目匹配
select_statement，select语句
with check option，指定只能添加或更新视图可访问的行，
    constraint表示check option约束指定的名称
with read only，确保不能在此视图上执行任何修改操作

使用1： 创建一个名为view_ven的试图，该视图与vendorMaster
表具有相应结构，可以通过该视图访问基表所有行，
create view view_ven
as
select * from vendorMaster;

使用2： 创建一个视图，只包含订单表中的"暂挂"订单记录
create view view_pause
as
select * from orderMaster
where ostatus = 'P'

说明： 该视图只显示ostatus列值为P的行

注意：如果使用该视图更新(update)orderMaster
表中的其他行(ostatus列值不为P的行)的值，也会成功，
所以，尽量不通过视图修改基表数据

使用3： 防止修改视图中可显示的行
--创建一个试图可显示ostatus为p的行
create or replace view view_pause
as
select * from orderMaster
where ostatus = 'p'
with check option CONSTRAINT chk_pv;
--通过视图修改ostatus的值
update view_pause set ostatus = 'd'
where ostatus = 'p'

执行update语句后会显示如下内容：

ERROR 位于第...行
ORA-01402：视图WITH CHECK OPTION违反WHERE子句

使用4： 创建视图，只显示基表中指定的列
create or replace view_pause
as
select orderNo, oDate from orderMaster;

注意：通过该视图只能更新(update)orderNo, oDate两列

使用5： 创建只读视图，该视图只能查询(select)
create or replace view view_pause
as
select * from orderMaster
with read only;

12、强制创建视图

在创建视图过程中使用force项，即使出现以下情况也会创建视图：

• 视图使用的查询语句引用了不存在的表
• 视图使用的查询语句引用了表中无效的列
• 视图所有者没有所需权限

这时，Oracle只检查create view语句中的语法错误，语法正确将
创建该视图，但是该视图却不能使用，这种视图称为"带错误创建"
的试图

13、基于视图进行添加(insert)、修改(update)、删除(delete)的限制

• 只能修改一个底层基表
• 违法基表的约束条件，无法更新
• 如视图中包含连接运算符、distinct、集合运算符、聚合函数、group by    子句，无法更新
• 如视图中包含伪列或表达式，无法更新
• 如视图中的select为联表查询(这种视图又称"联接视图")，只能修改单个基表，解决这个问题可使用insert of触发器来实现视图同时对多个表进行修改

14、键保留表

在联接视图中，如果视图包含了一个表的主键，且该键也是视图的主键，
则这个键被保留，键所在的表称为"键保留表"，Oracle可通过该视图向
表中添加行，含外联接的视图一般不含键保留表

15、删除视图
语法：drop view 视图名;

16、索引

• 索引是与表相关的一个可选结构
• 通过创建索引，可加快对表执行SQL语句的速度，这好比图书的目录可帮我们定位内容一样
• 合理使用索引是减少磁盘I/O的主要方法
• 索引是在逻辑上和物理上都独立于表的数据
• Oracle会自动维护索引

创建索引的语法：

create index index_name
on table_name (column_list)
[tablespace tablespace_name];

语法说明：

index_name，索引名称
table_name，表名
column_list，需要创建索引的列名，可基于多列创建索引
tablespace_name，为索引指定表空间

使用： 在ItemFile表的itemCode列上创建索引，
create index item_index on ItemFile (itemCode);

Oracle在创建索引的步骤：

• 获取要索引的列，对其进行排序
• 将ROWID连同每一行的索引值存储起来

如对ItemFile表的itemCode列创建索引，Oracle基于itemCode列对表进
行排序，然后按排序顺序用itemCode及其ROWID值加载索引，
使用索引时，Oracle先通过已排序的itemCode值执行快速搜索，然后使用
相关联的ROWID值来定位所要查找的itemCode值的行

索引在逻辑上和物理上都独立于表中的数据，创建或删除索引不会影响表
或其他的索引

一旦创建好索引，Oracle会自动维护，如添加新行、更新现有行或删除行，
Oracle会自动更新索引

注意：为表创建过的的索引会降低更新、删除、添加的性能

删除索引，语法：
drop index 索引名;

重建现有索引，语法：
alter index 索引名 rebuild;
该语句的性能要优于使用drop index和create index重建

17、索引的类型

索引类型图：

    17.1）唯一索引：

• 唯一索引确保在定义索引的列中没有重复值
• Oracle 自动在表的主键列上创建唯一索引
• 用CREATE UNIQUE INDEX语句创建唯一索引

   
    使用： 为ItemFile表的itemCode列创建唯一索引
    create unique index item_index on ItemFile (itemCode);

    17.2）组合索引：

• 组合索引是在表的多个列上创建的索引
• 索引中列的顺序是任意的
• 如果SQL语句的WHERE子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

   
    使用： 为ItemFile表创建组合索引，当查询该表的where子句同时包含
    pCategory, itemRate两列或pCategory一列时，该索引将用于检索数据，
    如只含itemRate列，该索引不会用于检索
    create index comp_index on ItemFile(pCategory, itemRate);
   
    17.3）反向键索引

• 一种特殊类型的索引，在索引基于含有序数的列时非常有用
• 反向键索引反转索引列键值的每个字节，在反向后的新数据上进行索引，因为新数据在值范围上的分布会比原来的有序数更均匀
• 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上，例如，列中的值是由序列产生的
• REVERSE关键字创建反向索引

   
    使用1： 为ItemFile表创建反向索引rev_index
    create index rev_index on ItemFile (itemCode) reverse;

    提示：使用关键字NOREVERSE可将反向键索引重建为标准索引

    如，alter index rev_index rebuild NOREVERSE;

    注意：标准索引不能重建为反向索引

    17.4）位图索引

    该索引用于不同值的数目比表的行数少的列，如某列的值重复
    了超过100次，可考虑位图索引，如果表有100万行，而某列
    有小于1000个不同的值，可考虑位图索引
   
    位图索引使用每个列值的位图，而不使用ROWID， 位图中的
    每个位对应一个可能的ROWID，如果设置了这个位，表示拥有
    此ROWID的行包含该列值
   
    create bitmap index语句创建位图索引

    使用： 为订单明细表的订单号(itemCode)一列创建位图索引
    create bitmap index bit_index on orderDetail(itemCode);

    位图索引的优势：

• 对于大批量即席查询，可减少响应时间，在将所生成的位图转换为ROWID前，通过在位图上直接执行相应的布尔运算，可快速解析查询条件中的AND和OR条件，如果查询结果中的行很少，可很快的响应查询，而不用对表进行完全扫描
• 相比其他索引技术，占用空间明显减少，使用标准索引对一个大型表进行完全索引极其浪费空间，因为索引数据可能比表中的数据大几倍，而位图索引的大小通常仅是表中被索引数据的一小部分
• 即使在配置很低的硬件上也能获得显著的性能
• 位图索引最适合于数据仓库和决策支持系统

    注意：位图索引不应用在频繁发生insert、update、delete操作
    的表上，这些操作在性能方面代价很高，他们会引起位图级的加
    锁发生，而且要求动态重建所有可能值的位图

    17.5）基于函数的索引

    有时需要在where子句的条件中使用表达式，如果在where子
    句的表达式或函数中已经包含了某个列，则不会使用该列上的
    索引，为了此类操作，可以基于一个或多个列上的函数或表达
    式创建索引

    用于创建索引的函数可以使算术表达式、PL/SQL函数、程序包
    函数或SQL函数的表达式，该表达式有如下限制：

• 不能包含聚合函数
• 不能在LOB列、REF列或包含LOB或REF的对象类型上创建基于函数的索引

   
    问题： venName是vendorMaster表的一列，用于存储供应商
    姓名，已为该列创建了唯一索引以加快查询速度，假设所有
    供应商的姓名都以混合大小写的形式存储（如：John Smith、
    Dave Jones、Tony Greig等），同时假设需要经常根据供应
    商的姓名来查询表数据，由于采用了混合大小写的形式存储，
    可能很难给出姓名的正确大小写形式，如果在where子句中
    使用upper()函数，则查询要花费很长时间，因为索引值是混
    合大小写形式，由于索引中没有适合于大写姓名的条目，因此
    Oracle无法使用该列的标准索引
   
    解决： 基于upper函数创建索引，
    create index vn_index on vendorMaster (upper(venName));
   
    使用： 将列值转换为全大写，然后与输入值进行比较
    select * from venderMaster
    where upper(venName) = 'DAVE JONES';
    注意：列值进行比较时会区分大小写
   
    另一种用法： 基于表达式创建索引，
    create index exp_index on ItemFile (qtyHand * itemRate);
   
    注意：要创建基于函数或表达式的索引，必须具有QUERY REWRITE系统权限

18、索引组织表

索引组织表与普通表的不同之处在于，该表的数据存储在于其关联的索引
中，对表数据进行的修改，如添加、更新、删除，只会对索引进行更新

索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表
和索引维护两个独立的存储空间，数据库只需维护一个索引，该索引包
含相应行的已编码键值和与其关联的列值

行的实际数据存储在索引中，而不是将行的ROWID作为索引条目的第二
个元素

可使用带有organization index子句的create table命令来创建索引
组织表

使用： 创建索引组织表 indOrgTab
create table indOrgTab
(
    venCode number(4) primary key,
    venName varchar2(20)
)
organization index;
注意：primary key是创建索引组织表所必须的

索引组织表适合于通过主键来访问数据，与唯一索引一样，索引组织表
没有重复的键值，因为只有非键列的值与该键存储在一起

操作索引组织表与操作普通表一样，但是数据库会通过操作相应的索引
来执行所有操作

常规表与索引组织表的区别 ，如下：

普通表                        索引组织表
=============================
ROWID 唯一地标识行    主键唯一地标识行
隐式的 ROWID 列         没有隐式的 ROWID 列
基于 ROWID 的访问      基于主键的访问
顺序扫描返回所有行       完全索引扫描返回所有行，并按主键顺序排列
支持分区                     不支持分区

19、索引中的分区

与表分区类似，可以对索引进行分区，索引分区可存储在不同的表空间
中，与分区有关的索引有3种类型：

• 局部分区索引
• 全局分区索引
• 全局非分区索引

注意：对索引分区，取决于是否需要在索引结构上执行分区来保障分区后与分
区前有同样的查询响应时间

    19.1）局部分区索引

    局部分区索引是在分区表上创建的一种索引，该索引中，Oracle
    为表的每个分区建立一个独立的索引，所以这些索引对于分区来
    说是"局部"的，通过在create index语句中指定local属性，可在
    分区上创建局部索引

    Oracle在于基础表相同的列上对索引进行分区，创建相同数量
    的分区并指定相同的分区边界

    添加、删除或拆分基础表的分区，Oracle会自动维护索引分区

    使用： 创建分区表，然后在分区表上创建局部索引
    --创建分区表
    create table orderMaster
    (
        orderNo number(4),
        venName varchar2(20)
    )
    partition by range(orderNo)
    (
        partition oe1 values less than (1000),
        partition oe2 values less than (2000),
        partition oe3 values less than (MAXVALUE)
    );
    --在分区表上创建局部索引
    create index my_index on orderMaster (orderNo) LOCAL;

    提示：可查询"USER_SEGMENTS"字典视图获取索引信息
    select * from USER_SEGMENTS
    where segment_name = "my_index";
    观察查询结果可发现，该索引是作为单独的段为所有分区创建的

    19.2）全局分区索引

    全局分区索引是指在分区表或非分区表上创建的索引，全局索引
    的键可以引用存储在多个分区中的行，

    使用： 在之前创建的分区表orderMaster上创建全局索引，
    在有3个分区的表上创建两个分区索引，
   
    create index glb_index on orderMaster (orderNo) GLOBAL
    partition by range (orderNo)
    (
        partition ip1 values less than (1500),
        partition ip2 values less than (MAXVALUE)
    );
    注意：不能在散列分区或子分区建立全局索引
   
    19.3）全局非分区索引

    在分区表上创建的全局普通索引，索引没有被分区，索引结
    构不会被分割

20、获取索引的信息

与索引有关的数据字典视图有：

• USER_INDEXES － 用户创建的索引的信息
• USER_IND_PARTITIONS － 用户创建的分区索引的信息
• USER_IND_COLUMNS － 与索引相关的表列的信息

使用：
select index_name, table_name, column_name
from USER_IND_COLUMNS
order by index_name, column_position;

21、总结

• 同义词是现有数据库对象的别名
• 序列用于生成唯一、连续的序号
• 视图是基于一个或多个表的虚拟表
• 索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能
• 索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引
• 索引组织表基于主键访问数据

Oracle 第3章 锁、表分区

Oracle 第5章 使用PL/SQL