第四章 数据库对象
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 ;