数据对象

第四章 数据库对象

3.1 锁定的概念

锁定是数据库用来控制共享资源并发访问的机制。在多用户环境下，多个用户可同时访问相同的数据。Oracle提供各式锁以确保在多用户环境下数据的完整性和一致性。在提交或回滚事务之前，Oracle会锁定正被修改的数据。在用户完成或回滚事务之后，锁会自动释放。只有在提交或回滚事务之后，其他用户才可以更新这些数据。

这些锁定中有"只读锁"、"排它锁"，"共享排它锁"等多种类型，而且每种类型又有"行级锁"（一次锁住一条记录）和"表级锁"（一次锁住整张表）之分。

若为"行级排它锁"，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改（update）或删除（delete）操作，若为"表级排它锁"，则所有其它用户只能对该表进行查询（select）操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。

oracle9i使用两种锁模式：

l 独占模式（排他）：

不允许其他任何并发会话以任何方式共享锁定的资源，修改数据时需要这种锁。

l 共享模式：

允许对同一块数据的并发读访问。在更改数据时，上升为独占模式。

3.1.1 行级锁

行级锁只对用户正在访问的行进行锁定。行级锁是一种排它锁，防止其他事务修改此行，但是不会阻止读取此行的操作。

insert update delete  隐式加行锁(排他)

select ... for update   显示加行锁(共享)

在锁释放之前，其他用户不可以对锁定的数据行进行(修改，删除)操作，查询可以。假如有其他用户要锁定同一资源：可以使用wait 子句对锁的等待时间控制。如： 在另一用户中：

select * from emp where deptno=30 for update wait 2 

(等待2秒，如2秒钟还未释放资源，系统将会给出提示信息)

Of column 用于多表连接的时候，通过columns来确定到底锁哪些表。要写表实际的列名，写别名无效。

3.1.2 表级锁

表级锁将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。可以使用LOCK TABLE语句显式地锁定表。表级锁用来限制对表执行插入、更新和删除等修改操作。表级锁的锁定模式有：

A: 共享模式（in share mode）

B: 共享更新模式（in share update mode）

C: 排他锁模式（in exclusive mode）

锁定表的通用语法：

1) 共享模式（in share mode)

不允许其他用户插入、更新和删除行,多个用户可以同时在同一表上设置共享锁，这样设置锁的多个用户都只能执行查询。

lock table emp in share mode;

2）共享更新模式（in share update mode)

允许多个用户同时锁定表的不同行, 允许其他用户进行DML（insert update delete select）操作，除了已锁定的行。

如： lock table emp in share update mode;

select * from emp where deptno=30 for update //锁定的行

其他用户不能delete ,update 部门30的雇员信息，但其它行可以更新。

其他用户可以查看锁定的行: select * from emp where deptno=30

3）排他锁模式（限制性强）

不允许其他用户插入，更新和删除行, 但允许查看数据。只有一个用户可以在表中放置排他锁。

lock table emp in exclusive mode;

4.1 同义词

同义词是数据库对象的一个别名。通过使用同义词，用户可以访问其它用户模式下的数据库对象而无需指定模式前缀。同义词具有以下用途：

l 简化SQL语句

l 隐藏对象的名称和所有者

注意：同义词不能代替权限，在使用同义词之前要确保用户已得到访问该对象的权限。

同义词的分类：

l 私有同义词

l 公有同义词

1：私有同义词：

私有同义词只能被当前模式的用户访问。用户要在自身的模式下创建私有同义词，则用户必须拥有CREATE SYNONYM系统权限。

例一：

SQL> grant create synonym to sunjob;

SQL> grant all on emp to sunjob;

SQL> create synonym emp for scott.emp;

2：公有同义词：

公有同义词可被所有的数据库用户访问。公有同义词可以隐藏基表的身份，并降低SQL语句的复杂性。要创建公有同义词，用户必须拥有

CREATE PUBLIC SYNONYM系统权限。

例二：

SQL> grant create public synonym to sunjob;

SQL> grant all on emp to sunjob

SQL> create public synonym emp for scott.emp;

3：删除同义词：

DROP SYNONYM语句用于从数据库中删除同义词。要删除同义词，用户必须拥有相应的删除权限。

SQL> grant drop any synonym to sunjob;

SQL> drop synonym emp;

4.2 序列

序列是用来生成唯一、连续的整数的数据库对象。序列通常用来生成主键或唯一键的值。序列可以按升序排列，也可以按降序排列。语法如下：

1：访问序列：

创建序列之后，可以通过CURRVAL和NEXTVAL伪列来访问序列的值。可以从伪列中选择值，但是不能操纵它们的值。

l NEXTVAL：

创建序列后第一次使用时，只能使用它。以后每次增长一个新值。

l CURRVAL：

返回序列的当前值。

例一：

2：更改序列：

ALTER SEQUENCE命令用于修改序列的定义。序列可以修改的地方为：

l 设置或删除MINVALUE或MAXVALUE

l 修改增量值

l 修改缓存中的序列号的数目

l Start with不能够修改

3：删除序列：

DROP SEQUENCE命令用于删除序列。

SQL> drop sequence seq_flowid;

4.3 视图

视图以经过定制的方式显示包含在一个或多个表(或视图)中的数据。视图获取查询的输出结果，并将其作为表来处理，因此，可以将视图视为“已存储的查询”或“虚拟表”。在可以使用表的大多数场合都可以使用视图。创建视图所依据的表称为“基表”。

视图具有以下优点：

Ø 通过限制对表中预定的一组行和列的访问。

Ø 视图隐藏了数据的复杂性。

Ø 视图简化了用户的SQL命令。

创建视图的语法如下：

CREATE [OR REPLACE] [FORCE] VIEW
view_name [(alias[, alias]...)] 
AS select_statement
[WITH CHECK OPTION]
[WITH READ ONLY];

4.3.1: 创建视图

示例1：（基于顾员表而创建的视图）

一：所有列

create or replace view vwEMP as select * from emp;

二：选择列

create or replace view vwEMP as 
select empno, ename, sal, deptno from emp;

三：选择记录

create or replace view vwEMP as 
select empno, ename, sal, deptno from emp where sal >= 2000;

示例2：（WITH CHECK OPTION）

该选项限定了所创建的视图对基依据的基表数据的修改。只有在视图中存在的记录，且修改后的记录依然能显示在视图中的记录才能被更新。

create or replace view vwEMP as 
select empno, ename, sal, deptno from emp where sal >= 2000
with check option;

以上视图如果按以下的更新方式将会出现：

1：NG

update vwEMP set sal=1500 where empno=7788;

2：OK

update vwEMP set sal=3500 where empno=7788;

示例3：（WITH READ ONLY）

此选项确保不能通过该视图去修改其所依据的基表数据。

create or replace view vwEMP as 
select * from emp with read only;

示例4：（ORDER BY）

create or replace view vwEMP as 
select * from emp order by sal;

示例5：（FORCE / NOFORCE）

该选项用于当创建视图时，如果其所依据的基表不存在或引用了表中无效的字段列，再或者没有所需的权限，而强制执行创建视图。这样创建出来的视图是不可使用的，但随后可以补建视图所依据的基表，这时对视图进行重编一下即可。

create or replace force view vwEMP as 
select * from MYEMP order by sal;  //MYEMP表不存在

select * from vwEMP;  //不能访问操作

create table MYEMP as select * from emp; //补建基表

select * from vwEMP;  //可以访问了

或：alter view vwEMP compile; //重新编译视图

4.3.2: DML语句在视图中的应用

如果一个视图基于单个基表，那么可以在此视图中进行INSERT/UPDATE/

DELETE操作，这些操作实际上是在其基表中进行。

在视图上使用DML语句有如下限制（相对于表）：

Ø 只能修改一个底层的基表

Ø 如果修改违反了基表的约束条件，则无法更新视图

Ø 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图

Ø 如果视图包含伪列或表达式，则将无法更新视图

4.3.3: 联接视图

联接视图是在FROM子句中指定多个表或视图而创建的视图。在联接视图中使用DML语句只能修改单个基表，如果修改多个基表，SQL就会显示错误。基于些，ORACLE提供了视图上的“INSTEAD OF 触发器”，使用该触发器，可以通过视图同时对多个基表执行DML操作，详见第八章内容。

u 联接可分为内联接和外联接

（内联接）：从两个表中选择匹配的行

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a, BB b where a.deptno = b.deptno;

//或写成如下方式也可:

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a inner join BB b on a.deptno = b.deptno;

（外联接）：允许用户从一个表中选择所有的行并从另一个表中选择匹配的行(又分为左外联接和右外联接)

1：左外联接：左边表的所有记录 + 右边表的匹配行记录

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a, BB b where a.deptno = b.deptno(+);

//或写成如下方式也可:

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a left outer join BB b on a.deptno = b.deptno;

2：右外联接：左边表的匹配行记录 + 右边表的所有记录

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a, BB b where a.deptno(+) = b.deptno;

//或写成如下方式也可：

select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno 
from AA a right outer join BB b on a.deptno = b.deptno;

4.3.4: 键保留表

在联接视图中，如果视图包含了一个表的主键，且也是这个视图的主键，则这个表称为键保留表，ORACLE可以通过此视图向表中插入行。

通过数据字典视图USER_UPDATABLE_COLUMNS，可以确定联接视图中可更新的列。

select * from user_updatable_columns 
where table_name='vwEMP';

4.3.5: 删除视图

如果要删除视图，可以使用DROP VIEW命令。

drop view vwEMP;

可以通过查询USER_VIEWS数据字典视图来获得用户所创建的视图信息。

select * from user_views;

4.4 索引

索引是与表关联的一种可选数据库对象。可以明确地创建索引，以加快对表执行SQL语句的速度。合理地使用索引是减少磁盘I/O的主要方法。索引只是一种快速访问数据的途径，它只影响执行的速度。可以使用create index命令在表的一列或若干列的组合上创建索引。

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

创建索引时，ORACLE将获得要创建索引的列，并对其进行排序。然后，将ROWID与其索引值存储起来。

索引在逻辑上和物理上都独立于关联表中的数据。在任何时候都可以创建或删除索引，而不会影响基表或其它索引。如果删除索引，所有的应用程序都将继续运行，但在访问原先被索引的数据时，速度可能会降低。与视图不同的是，索引是独立的结构，因此需要存储空间。

一旦创建了索引，ORACLE会自动维护和使用它们。只要修改了数据，ORACLE都会自动更新相关的索引。

4.4.1: 索引的分类

· 索引是与表相关的一个可选结构

· 用以提高 SQL 语句执行的性能

· 减少磁盘I/O

· 在逻辑上和物理上都独立于表的数据

· Oracle 自动维护索引

Ø 唯一索引

索引可以是唯一的，也可以是非唯一的。唯一索引可以确保在定义索引的列中，表的任意两行的值都不相同。ORACLE自动为表的主键列创建唯一索引。可以使用create unique index命令明确地创建唯一索引。

create unique index no_index on emp(empno); //主键列

Ø 组合索引

组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。如果在SQL语句的条件句中引用了组合索引中的所有列或大多数列，则组合索引可以提高数据检索的速度。在创建索引时，应注意定义中的列的顺序。通常，最频繁访问的列应放置在列表的最前面。

create index comp_index on emp(empno, deptno);

Ø 反向键索引

反向键索引是一种特殊类型的索引，在索引基于大量有序数的列时非常有用。反向键索引通过简单的反向被索引的列中的数据来解决问题，首先反向每个列键值的字节，然后在反向后的新数据上进行索引，而新数据在值的范围上的分布比原来的有序数更均匀。

可以在create index语句中指定关键字reverse创建反向键索引。

create index num_index on tbtemp(itemcode) reverse;

123451154321
123452254321
123453354321
123454454321

使用关键字NOREVERSE可以将反向键索引重建为标准索引，反之不行。

alter index num_index rebuild noreverse;

Ø 位图索引

使用位图索引的优点在于，它最适用于低基数列，也就是不同值的数目比表的行数少的列。如：如果某个列的值重复了一百次，则可以考虑在该列上创建位图索引。可以使用create bitmap index命令来创建位图索引。

create bitmap index bit_index on order_detail(itemcode);

位图索引不应当在频繁发生DML操作的表上。

Ø 基于函数的索引

有时，可能要在条件子句中使用表达式及函数。如果在该表达式和函数中用到了某个列，而该列上是有索引的，则ORACLE不会应用该列上的索引。为了方便此类操作，且使基于该列有索引可用，ORACLE提供了一个选项，允许基于一个表达式或函数创建索引。在创建此类基于表达式或函数的索引时，该表达式不能包含任何聚合函数，也不能在LOB列、REF列或包含LOB或REF的对象类型上创建基于函数的索引。

1：（基于函数）

create index vn_index on vendor_master(upper(venname));

2：（基于表达式）

create index vn_index on itemtb(qty_num * itemrate);

3：（应用）

select * from vendor_master where upper(venname)='SMALL';

要创建基于函数或表达式的索引，必须具有query rewrite系统权限。

4.4.2: 索引组织表

索引组织表与普通表的不同之处在于，该表的数据存储在与其关联的索引中。对表数据进行的修改，只会导致对索引的更新。索引组织表适合于通过主键来访问数据。与唯一索引一样，索引组织表没有重复的键值。

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

create table index_tb
(
  vencode number(4) primary key,
  venname varchar2(20)
)
organization index;

// primary key是创建索引组织表所必需的

索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间。

n 普通表与索引组织表的比较

4.4.3: 索引中的分区

与对表进行分区类似，ORACLE也允许对索引分区。与表分区一样，索引分区可以存储在不同的表空间中。索引的分区有下面几种类型：

索引分区类型：

 分区索引（局部分区索引 + 全局分区索引）

（局部分区索引）：

局部分区索引是在分区表上创建的一种索引，在局部分区索引中，ORACLE为表的每个表分区建立一个独立的索引。通过在create index语句中指定local属性，可以在表分区上创建局部索引。

步骤一： 创建分区表

create table order_mast
(
 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 part_index on order_mast(orderno) local;

步骤三：插入相应数据信息

insert into order_mast values(100, 'AAAA');
insert into order_mast values(900, 'BBBB');
insert into order_mast values(1500, 'CCCC');
insert into order_mast values(2500, 'DDDD');
insert into order_mast values(3000, 'EEEE');

步骤四：查看局部索引分区信息

通过查询名为“user_segments”的数据字典视图，可以了解相关信息

select segment_name, partition_name, 

segment_type, tablespace_name

from user_segments where segment_name='PART_INDEX';

（全局分区索引）：

全局分区索引是指在分区表或非分区表上创建的索引。

create index glo_index on order_mast(orderno) global
partition by range(orderno)
(
 partition ip1 values less than(1500),
 partition ip2 values less than(maxvalue)
);

※ 不能在散列分区或子分区建立全局索引。

 非分区索引

非分区索引是指在分区表上创建的全局索引，它类似于在非分区表上的索引，索引的结构不会被分割。

4.4.4: 删除索引

如果要删除索引，可以使用DROP INDEX命令。

drop index index_name;

查询索引信息

可以通过查询USER_INDEXS数据字典视图来获得用户所创建的索引信息。

select * from user_indexes;

select * from user_ind_partitions;

select * from user_ind_columns;

select * from user_ind_expressions;

select * from user_ind_subpartitions;

 建立索引需要为表的1.2倍的硬盘空间和内存，所以索引不是越多越好，而且维护起来也很影响效率，所以对于有些不经常查询的字段，逻辑型字段不要建立索引，索引一般是针对数据量很大的表

平时我们建立的索引是B-tree 索引：

SQL>create  index  index_name  on  table_name(column_name); 

重建索引

比如说某张表从默认的表空间移动到了另一个表空间，要重建索引

SQL> alter  index  index_name rebuild ;

删除索引

SQL>drop index  index_name ;