管理数据库对象

1、第3章管理数据库对象本章学习内容1. 同义词2. 序列3. 视图4. 索引本章学习目标1. 掌握同义词的使用2. 掌握序列的使用3. 掌握视图的使用4. 掌握索引的使用本章简介在第2章中，我们已经学习了使用DDL语句、DML语句、SELECT语句、DCL数据控制语言、TC事务控制语言、基本函数等，并能将这些语句用于Oracle的SQL查询语句中，特别是使用基本函数可以提高书写SQL语句的效率。本章将继续学习Oracle方案、同义词、序列、视图和索引等知识。方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名。方案中包含各种对象，如表、视图、索引、同义词、序列、数据库链接等

2、对象。同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性。序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数。在表中序列经常作为表的自动编号列。视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表”。视图是查看和操作表数据的一种方法，使用视图有诸多优点，如提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。索引是一种与表相关的可选的方案对象。通过在表中的一个或多个列上创建索引，能够为数据的检索提供快捷的存取路径，减少查询时的硬盘I/O操作，加快数据的检索

3、速度。3.1 同义词3.1.1 方案方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名。方案中包含各种对象，如表、视图、索引、同义词、序列、数据库链接等对象。一般情况下，一个用户对应一个方案，在创建用户的同时为该用户创建一个与用户名同名的方案，并作为该用户的缺省方案。当然，一个用户还可以使用其他用户的方案。如果访问一个表时没有指明该表属于哪一个用户方案，则系统将会自动地在访问表前加上缺省的方案名。例如，访问SCOTT用户下的emp表的SQL语句为：select * from emp。事实上，这条SQL语句的完整写法应为：select * from scott.emp，因

4、为在数据库中一个对象的完整名称为“方案名.对象名”，而不是“用户名.对象名”。解释用户所持有的是系统的权限及资源，而方案所涵盖的是各种对象，包含了表、视图、序列等对象的“所在地”，并不包括对他们的权限控制。类似于一个房子（方案），里面放满了家具（方案对象），对这些家具有支配权的是房子的主人（用户），而不是房子（方案）。3.1.2 同义词概述同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性。同义词并不占用实际存储空间，只在数据库字典中保存同义词的定义。可以创建同义词的对象主要包括表、视图、同义词、序列、存储过程等对象。在开发数据库应用程序时，应当普遍遵守的规则是：尽量避免直接引用

5、表、视图或其他对象的名称。否则，当DBA改变了这些对象的名称时，就必须重新更改并编译应用程序。因此，DBA应当为开发人员建立对象的同义词，这样即使基础表或其他对象发生了变动，也只需要在数据库中对同义词进行修改，而不必对应用程序作出任何改动。Oracle中可以创建两种类型的同义词：公用同义词和方案同义词。3.1.3 公用同义词与方案同义词1. 公用同义词（public synonym）公用同义词由一个特殊的用户组PUBLIC所拥有，数据库中所有的用户都可以使用公用同义词。SYS用户创建的数据字典视图就是公用同义词的示例。2. 方案同义词（schema synonym）方案同义词由创建它的用户所拥

6、用，也称为私有同义词（private synonym），用户可以控制其他用户是否有权使用属于自己的方案同义词。方案同义词常在应用开发中使用，为应用开发提供命名上的解决方案。值得注意的是，当代码引用一个未限定的表、视图、同义词、序列、函数等对象时，Oracle会按以下顺序来查看这3个位置是否有被引用的对象：（1）当前用户拥有的对象。（2）由当前用户拥有的一个方案同义词。（3）公用同义词。可见，方案同义词的搜索顺序优于公用同义词。如果在这3个地方都没有找到该对象的名称，将会出现错误提示，如“ORA-00942：表或视图不存在”。3.1.4 创建同义词1. 创建方案同义词需要在自己的方案中创建方案同

7、义词时，用户必须具有CREATE SYNONYM系统权限；需要在其他的方案中创建方案同义词时，用户必须具有CREATE ANY SYNONYM系统权限。语法：CREATE OR REPLACE SYNONYM synonym_name schema.object;其中，OR REPLACE表示如果同义词已经存在，将重新覆盖新建同义词。如图1.3.1所示，在被授予CREATE SYNONYM系统权限后，SCOTT用户在其dept表上创建方案同义词syn_dept。1. 授予SCOTT用户create synonym权限2. 创建dept表上的同义词图1.3.1 创建方案同义词2. 创建公用同义词

8、需要创建公用同义词时，用户必须具有CREATE PUBLIC SYNONYM系统权限。语法：CREATE OR REPLACE PUBLIC SYNONYM synonym_name schema.object;如图1.3.2所示，在被授予CREATE SYNONYM系统权限后，SCOTT用户在其dept表上创建公用同义词pubsyn_dept。创建dept表上的公用同义词图1.3.2 创建公用同义词注意在Oracle数据库中，公用同义词已经超过2600万条，查询不方便，所以在应用开发中应尽量避免创建公用同义词。3.1.5 使用同义词1. 使用方案同义词使用方案同义词可分为方案用户使用和其他用

9、户使用。（1）方案用户使用自己的方案同义词。方案用户具有对象的所有权限，所以可以像使用原对象一样使用该对象的同义词。SCOTT用户使用自己方案中syn_dept方案同义词的示例如图1.3.3所示。图1.3.3 方案用户使用自己的方案同义词（2）其他用户使用另一个用户创建的方案同义词。由于方案同义词是私有的，所以其他用户无法直接使用另一个用户所创建的方案同义词，但能够以在方案同义词前面加上方案名作为前缀的方式来使用其他用户所创建的方案同义词，如图1.3.4所示。3. 在syn_dept方案同义词前加上“scott”方案名来访问2. 以HR用户登录，访问SCOTT方案中的syn_dept方案同义词

10、1. 授予HR用户查询SCOTT方案dept表的权限图1.3.4 其他用户使用另一个用户创建的方案同义词（2）通过自己的方案同义词访问其他方案中的对象。通过在自己的方案中创建指向其他方案中对象的方案同义词，SCOTT用户给HR用户授予dept表上的SELECT对象权限，HR用户在自己的方案中创建一个指向SCOTT用户的dept表的syn_scott_dept方案同义词，则HR用户可以通过自己的syn_scott_dept方案同义词来查询SCOTT用户的dept表，如图1.3.5所示。4. HR用户通过查询syn_scott_dept来访问SCOTT方案中的dept表3. 授予HR用户SCOTT

11、用户dept表上的SELECT权限2. HR用户使用syn_scott_dept方案同义词权限不足1. HR用户创建关于SCOTT用户syn_scott_dept方案同义词图1.3.5 通过自己的方案同义词访问其他方案中的对象2. 使用公用同义词与方案同义词不同，如果使用公用同义词来访问其他方案中的对象，就不需要在该公用同义词前面添加方案名。但是，如果用户没有被授予访问该公用同义词所引用的对象的对象权限，仍然不能使用该公用同义词，如图1.3.6所示。3. 再次以HR用户登录后，使用公用方案syn_scott_dept来访问SCOTT方案中的dept表失败2. 撤消HR用户关于SCOTT方案中d

12、ept表的SELECT对象权限1. 以HR用户登录，使用公用方案syn_scott_dept来访问SCOTT方案中的dept表图1.3.6 使用公用同义词3.1.6 删除同义词删除同义词后，同义词所引用的基础对象不会受影响。1. 删除方案同义词用户能够删除自己方案中的任何方案同义词，但若需要删除其他用户方案中的方案同义词，必须拥有DROP ANY SYNONYM系统权限。语法：DROP SYNONYM synonym_name2. 删除公用同义词需要删除公用同义词时，用户必须具有DROP PUBLIC SYNONYM系统权限。语法：DROP PUBLIC SYNONYM synonym_nam

13、e3.2 序列3.2.1 序列概述序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数。与视图、约束相似，序列也不占用实际的存储空间，而只在数据字典中保存序列的定义。引入序列的理由或序列的主要用途在于以下几点。1. 主键、外键值应用需求在某些表中，主键可能需要由几个字段组成，如果将这些字段合起来作为主键，就会使问题复杂化。因此，可以使用简单的顺序号来代替多个字段的主键标识记录，以简化子表对于父表的引用关系。如果在一个数据库应用中创建一个序列，使各个表都使用这个序列产生的整数作为主键、外键值，则会对数据库的设计、编程、各种主键和外键关系的建立都起到良好的作用。2

14、. 流水号应用需求在现实生活中，很多情况下都需要流水号。例如，商场打印销售单时需要将销售单按顺序编号，一般每张销售单对应一个编号，且编号是依次递增的。如果需要产生诸如此类的单据流水号，则可以借助序列来完成。3. 序列的生成与定义的内容序列由Oracle服务器端产生，所有的序列都出自一处。如果有多个用户同时向序列提出申请，则序列会按照串行机制依次处理各个用户的请求，绝不会生成两个相同的整数。而且序列生成下一个整数的速度很快，即使在并发用户数量很多的联机事务处理环境中，也不会产生明显的延迟。说明序列能生成最大38位的整数。3.2.2 创建序列需要在自己的方案中创建序列时，用户必须具有CREATE

15、SEQUENCE系统权限；需要在其他的方案中创建序列时，用户必须具有CREATE ANY SEQUENCE系统权限。语法：CREATE SEQUENCE sequnce_name START WITH n1 INCREMENT BY n2 MAXVALUE n3 | NOMAXVALUE MINVALUE n4 | NOMINVALUE CACHE n5 | NOCACHE CYCLE | NOCYCLE ORDER;其中：（1）START WITH：指定要生成的第一个序列号。（2）INCREMENT BY：用于指定序列号之间的间隔，默认值为1。如果n为正值，则生成的序列将按升序排列；如果n为

16、负值，则生成的序列将按降序排列。（3）MAXVALUE：指定序列可以生成的最大值，必须大于或等于START WITH中的n1，并且必须大于MINVALUE中的n4。（4）NOMAXVALUE：用于指定序列没有上限，最大值可达1027。（5）MINVALUE：指定序列可以生成的最小值，必须小于或等于START WITH中的n1，并且必须小于MAXVALUE中的n3。（6）NOMINVALUE：用于指定序列没有下限，最小值可达10-26。（7）CACHE：用于指定在高速缓存中可以预分配的序列号个数，默认为20。（8）NOCACHE：用于指定在高速缓存中不预先分配序列号，即序列生成器会每次生成一个序

17、列号，这是默认值。（9）CYCLE：用于指定在达到序列的最大值或最小值后是否循环，即再次从n1开始生成序列号。默认为NOCYCLE，不循环。（10）ORDER：用于指定按顺序生成序列号。在图1.3.7中，SCOTT用户创建了一个seq_id序列，利用这个序列可以为其方案中的表生成唯一的整数。图1.3.7 创建序列该序列从300开始，每次增量为1，最大值为999999999，每次生成10个序列号，到达最大值后不循环。3.2.3 使用序列1. NEXTVAL和CURRVAL列在引用序列时，需要使用到序列的NEXTVAL和CURRVAL两个列：NEXTVAL列返回序列生成的下一个值，CURRVAL列

18、返回序列生成的当前值。2. 序列的初始化在第一次引用CURRVAL列之前，必须引用过一次NEXTVAL列，用于初始化序列的值，否则会出现错误提示，如图1.3.8所示。图1.3.8 序列的初始化3. 在SQL语句中使用序列在SQL语句中，可以直接使用引用序列的值。图1.3.9中先创建了一张tb_test表，然后使用seq_id序列生成的整数作为该表的主键值。2. 使用序列向表中插入值1. 创建表图1.3.9 在SQL语句中使用序列3.2.4 更改序列需要更改自己方案中的序列时，用户必须具有ALTER SEQUENCE系统权限；需要更改其他方案中的序列时，用户必须具有ALTER ANY SEQUE

19、NCE系统权限。需要注意的是，不能修改序列中的起始值，如果要修改起始值，必须删除序列再重建。更改序列包括：（1）修改MAXVALUE和MINVALUE。（2）修改INCREMENT增量值。（3）修改缓存中的序列的数目。语法：ALTER SEQUENCE sequnce_nameINCREMENT BY n2MAXVALUE n3 | NOMAXVALUEMINVALUE n4 | NOMINVALUECACHE n5 | NOCACHECYCLE | NOCYCLE图1.3.10中演示了对seq_id序列进行更改的过程。3. 查看修改seq_id后的下一个序列号2. 修改seq_id增量和最大

20、值等信息1. 显示seq_id当前序列号图1.3.10 更改seq_id序列思考如何将seq_id的nextval的值修改为1304?3.2.5 删除序列需要删除自己方案中的序列时，用户必须具有DROP SEQUENCE系统权限；需要删除其他方案中的序列时，用户必须具有DROP ANY SEQUENCE系统权限。语法：DROP SEQUENCE sequence_name图1.3.11中的代码用于删除上述创建的序列seq_id。图1.3.11 删除序列3.3 视图3.3.1 视图概述视图（VIEW）是由SELECT子查询语句定义的一个逻辑表，只有定义而无数据，是一个“虚表”。视图是查看和操作表

21、数据的一种方法。使用视图有诸多优点，如提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。在许多方面，视图的使用和管理都与表相似，例如都可以被创建、更改和删除，都可以通过它们来操作数据库中的数据。除了SELECT之外，视图在INSERT、UPDATE和DELETE方面受到了某些限制。3.3.2 创建视图需要在当前方案中创建视图时，用户必须具有CREATE VIEW系统权限；需要在其他方案中创建视图时，用户必须具有CREATE ANY VIEW系统权限。视图的拥有者必须被明确授予访问在视图定义中所参考的所有基础对象的权限。例如，如果视图的拥有

22、者只具有在SCOTT方案的emp表上INSERT对象的权限，则该视图仅能用于在emp表中插入新行，而不能进行SELECT、UPDATE、DELETE等操作。语法：CREATE OR REPLACE FORCE VIEW view_name(alias1,alias2.)AS select_statementWITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY;其中：（1）OR REPLACE：如果视图已经存在，该选项将重新创建该视图。（2）FORCE：无论基表是否存在，都将创建视图。（3）view_name：指定创建视图时的名称。（4）a

23、lias：指定由视图的查询所选择的表达式或列的别名。别名的数目必须与视图所选择的表达式的数目匹配。（5）select_statement：创建视图时的SELECT语句。（6）WITH CHECK OPTION：在使用视图时，检查涉及的数据是否能通过SELECT子查询的WHERE条件，否则不允许操作并返回错误提示。（7）WITH READ ONLY：创建的视图只能用于查询，而不能用于更改数据。该子句不能与ORDER BY子句同时存在。在图1.3.12中，在给SCOTT用户授予CREATE VIEW系统权限之后，SCOTT用户就能在自己的方案中创建基于emp表的视图v_scott_1。1. 授予创

24、建视图权限2. 创建视图3. 使用视图图1.3.12 创建视图在图1.3.13中，在创建视图时，可以指定WITH READ ONLY选项，使该视图只用于执行SELECT语句，而禁止执行INSERT、UPDATE和DELETE语句。只读视图无法更新世界图1.3.13 创建具有WITH READ ONLY选项的视图在图1.3.14中，在创建视图时，还可以指定WITH CHECK OPTION选项，该选项用于在视图上定义CHECK约束，并可以使用CONSTRAINT选项指定约束的名称。之后在此类视图上执行DML操作时，就要求所操作的数据必须满足SELECT子查询中的WHERE条件。1. 创建with

25、 check option的视图2. 插入违反视图定义的数据，出错图1.3.14 创建WITH CHECK OPTION的视图3.3.3 强制创建视图正常情况下，如果基本表不存在，则创建视图会失败；但如果创建视图的语句没有语法错误，则只要使用FORCE选项即可创建该视图，这种强制创建的视图被称为带有编译错误的视图。此时，这种视图处于失效状态，不能执行该视图，但之后随着基础表的创建，该视图就可以正常运行了。图1.3.15将演示强制创建视图的过程。解释Oracle之所以提供强制创建视图的功能，是为了使基础表的创建和修改与视图的创建和修改之间没有必然的依赖性，从而便于同步工作、提高工作效率。2. 使

26、用该类视图时出现错误1. 强制创建视图图1.3.15 强制创建视图3.3.4 在连接视图上执行DML操作对于在视图上进行的所有DML操作，最终都会在基础表的数据上完成。可以像对普通表一样对视图进行SELECT操作，但如果需要对视图进行更新（包括UPDATE、DELETE、INSERT）操作，则会受到某些限制，即并非在所有的视图上都可以执行全部的DML操作。对于一个基于基础表的简单视图而言，它只是简单地去掉了基础表中的某些记录或某些列，但如果视图中包括基础表的键（主键、外键），则总可以在其上进行DML操作。与简单视图相对应，当一个视图基于多个基础表（或视图）时，即定义视图的查询是一个连接查询，这

27、类视图被称为连接视图。对于连接视图，有些可以更新，但有些不能更新。1. 可更新连接视图当创建连接视图的SELECT子句满足以下条件时，连接视图是可更新的：（1）不包含集中运算符（UNION、UNION ALL、INTERSECT、MINUS等）。（2）不包含DISTINCT关键字。（3）不包含GROUP BY、ORDER BY、CONNECT BY 或START WITH子句。（4）不包含子查询。（5）不包含分组函数。（6）需要更新的列不是由列表达式定义的。（7）表中所有的NOT NULL列均属于该视图。但是，这只是最基本的条件，即并非在可更新连接视图中就可以进行更新操作了，还需要遵守更新标准

28、，也就是只能对“键值保存表”进行更新。2. 键值保存表如果连接视图中的一个基础表的主键（主键、唯一键）在它的视图中仍然存在，则称这个基础表为键值保存表。创建关于SCOTT方案中DEPT表和EMP表的连接视图，如图1.3.16所示。4. 更新非“键值保存表”上的列失败1. 创建连接视图3. 更新“键值保存表”上的列成功2. EMPNO列可作为视图的主键，而DEPTNO列不能做为视图的主键图1.3.16 对连接视图的键值保存表进行更新在EMP表中，EMPNO列是该表上的主键；而在DEPT表中，DEPTNO是该表中的主键。EMPNO可以同时作为视图v_dept_emp_1视图的主键，但DEPTNO不

29、能作为该视图的主键，所以EMP是键值保存表，而DEPT表则不是。值得注意的是，对于在连接视图上的任何INSERT、UPDATE或DELETE操作，一次只能对视图中的一个键值保存表进行更新。注意一般而言，在由父子关系的两个表组成的连接视图中，子表就是键值保存表。3.3.5 查询视图信息1. 查询视图的可更新列可以使用USER_UPDATABLE_COLUMNS数据字典来查询当前用户方案中所有的表以及视图中所有可修改的列。图1.3.17中的代码用于查询v_dept_emp_1视图中所有可更新的列。图1.3.17 查询v_dept_emp_1视图中所有可更新的列2. 查询视图的定义信息可以使用USE

30、R_VIEWS数据字典来查询当前方案中视图的定义信息。图1.3.18中的代码用于查询v_dept_emp_1视图的定义信息。图1.3.18 查询v_dept_emp_1视图的定义3.3.6 删除视图可以删除当前模式中的各种视图，需要删除其他方案中的视图时，必须拥有DROP ANY VIEW系统权限。语法：DROP VIEW view_name图1.3.19中的代码用于删除上述创建的v_dept_emp_1视图。图 1.3.19 删除视图3.4 索引3.4.1 索引概述索引是一种与表相关的可选的方案对象。通过在表中的一个或多个列上创建索引，就能为数据的检索提供快捷的存取路径，减少查询时的硬盘I/

31、O操作，加快数据的检索速度。与其他具有独立存储结构的方案对象类似，索引需要占用实际的存储空间。一旦创建了索引，在表上执行DML操作时，Oracle就会自动地对索引进行维护，并且由Oracle决定何时使用索引，用户完全不需要考虑在SQL语句中指定使用哪个索引以及如何使用索引。如果将表看成一本书，则索引的作用类似于书中的目录。需要在表中查询指定的记录时，在没有索引的情况下，必须遍历整张表中的记录；但如果存在索引，则只需要先在索引中找到符合查询条件的索引列值，然后通过保存在索引中的ROWID即可快速找到表中对应的记录。因此，为表建立索引能够减少查询操作的时间并减少I/O操作的开销。3.4.2 创建索

32、引可以使用CREATE INDEX命令在一列或若干列的组合上创建索引。需要在自己的方案中创建索引时，必须拥有CREATE VIEW INDEX系统权限；需要在其他用户的方案中创建索引时，必须拥有CREATE ANY INDEX系统权限。语法：CREATE INDEX index_name on table_name (columm list) TABLESPACE tablespace_name其中：（1）index_name：指定所创建的索引名。（2）table_name：指定创建索引的表名。（3）column_list：指定在表上创建索引的列名列表，可以基于多列创建索引。（4）tables

33、pace_name：为索引指定表空间。在创建索引时，会对表进行扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息存储到数据字典中。创建后的索引完全由Oracle自动管理、维护和使用。图1.3.20中的代码用于为SCOTT方案中的emp表创建基于sal的索引。图1.3.20 创建索引即使在表中创建了索引，Oracle也不是机械地为该表上的所有查询都使用索引，而是根据查询的具体情况决定是否使用索引。在运行查询语句之前，Oracle一般需要对其进行优化。优化的目的是找到运行该查询语句的最佳途径。Oracle会使用两种优化器：RBO优化器（Rule Based Optimizer，基于

34、规则的优化器）、CBO优化器（Cost Based Optimizer，基于开销的优化器）。优化器会将使用全表扫描所需的资源开销与使用索引所需的资源开销进行对比，如果使用全表扫描所需的资源开销更节省，则不会使用索引。使用全表扫描所需的资源与表中数据量的大小密切相关。一般而言，当表中数据量达到一定数量时，优化器才会考虑使用索引。注意为表创建过多的索引会降低更新、删除及插入的性能，因为Oracle还必须更新与该表关联的索引。以下通过一个示例来说明索引可以大大提高查询的效率，步骤如下：1. 创建用于测试的表以SYS方案中的dba_objects表为参考数据，在SCOTT方案中创建一张用于测试的tb_

35、idx_test表，如图1.3.21所示。为了使SCOTT用户查询SYS方案中的dba_objects表，必须首先为SCOTT用户赋予SELECT对象权限。1. 授予SCOTT用户SELECT对象的权限2. 创建用于测试的tb_idx_test表图1.3.21 创建用于测试索引的大数据表2. 通过查询查看执行计划以object_name为查询条件，查询tb_idx_test表中的记录，并显示执行计划，如图1.3.22所示。CPU开销为238使用全表扫描图1.3.22 查看查询执行计划在执行计划中，从Operation列的TABLE ACCESS FULL可以看出，由于没有为查询的条件objec

36、t_name创建索引，Oracle在执行该查询时会使用全表扫描；从Cost列可以看出，CPU开销为238；从Time列可以看出执行时间的估计值为03。3. 通过创建索引查询并查看执行计划先在tb_idx_test表的object_name列上创建一个索引，然后再次执行上述查询，并查看执行计划，如图1.3.23所示。CPU开销为3使用索引进行查询图1.3.23 通过创建索引查询并查看执行计划在新的执行计划中，从Operation列的TABLE ACCESSBY BY INDEX ROWID可以看出，Oracle在执行本查询时使用了索引；从Cost列可以看出，CPU开销为3；从Time列可以看出执

37、行时间估计值为01。相比之下，比没有索引时的开销降低了79倍，显然大大地提高了查询效率。3.4.3 索引的分类在Oracle中，索引可分为以下几类：1. 单列索引与复合索引一个索引可以由一个或多个列组成，用于创建索引的列被称为“索引列”。单列索引是基于单个列所创建的索引，复合索引是基于两列或多列所创建的索引。2. 唯一索引与非唯一索引唯一索引是索引列值不能重复的索引，非唯一索引是索引列值可以重复的索引。无论是唯一索引还是非唯一索引，索引列都允许取NULL值。默认情况下，Oracle创建的索引是非唯一索引。3. 标准（B-tree index，B树）索引B树索引是Oracle中最常用的一种索引。

38、在使用CREATE INDEX语句创建索引时，默认创建的就是B树索引。B树索引可以是单列索引或复合索引、唯一索引或非唯一索引。B树索引按B树结构组织并存放索引数据。B树索引中的“B”是指“平衡”（Balanced），B树索引是一棵二叉树。B树索引能够适应多种查询条件，包括使用“=”的精确匹配、使用“LIKE”的模糊匹配、使用“”的比较条件。B树索引的局限性是：当查询数据的范围超过表的10%之后，就不能显现出B树索引的良好性能了。注意4. 位图索引在开始介绍位图索引之前，首先引入基数（Cardinality）的概念。基数是指某个列可能拥有的不重复值的个数。例如，Sex列的基数为2（性别只能是男或

39、女），MaritalStatus列的基数为3（婚姻状况只能是未婚、已婚、离异）。对于一些基数很小的列，B树索引处理方式的效率比较低。对于基数很小、只存在有限的几个固定值的列（如性别、婚姻状态、行政区、职称），为了加快查询效率，应该在这些列上创建位图索引，而并非B树索引。注意当某列的基数与表的总行数的比例小于1%时，Oracle建议在列上创建位图索引。另外，在表上创建一个单独的位图索引是无意义的。例如，如果只在Customer表的MaritalStatus列上创建一个位图索引，则使用该位图索引进行查询时，将会返回大量的记录。因此，位图索引的作用来源于与其他位图索引的结合，例如在Region列上的

40、位图索引，当在这两个列上查询时，Oracle就可以针对这两个列上的位图进行AND和OR等操作，以达到更好的效果。5. 函数索引在Oracle中，不仅能够对表中的列创建索引，还可以对包含有列的函数或表达式创建索引，这种索引被称为“函数索引”。根据函数或表达式的结果的基数情况，函数索引既可以采用普通的B树索引，也可采用位图索引。例如，emp表中的hiredate列存储了雇员的出生日期，如果需要搜索1980年以后出生的雇员信息，则使用的SQL语句如下：select * from emp where extract(year from hiredate)1980虽然该语句能够正常运行，但即使在hire

41、date列上创建过索引，Oracle也不会使用该索引，而会采用全表扫描。因为对列值进行数据类型转换后，该值不会存于索引中，所以Oracle被迫使用全表扫描。为了解决此类问题，可以使用函数索引，例如，创建如下的函数索引来解决此类问题：CREATE INDEX funidx_extract_hiredate on emp(extract(year from hiredate)创建之后，Oracle就可以使用funidx_extract_hiredate索引来查询，从而提高查询效率。3.4.4 创建各类索引1. 唯一索引在emp表的ename列上创建一个唯一索引idx_emp_ename，创建之后该

42、表中的ename列就不允许出现重复值，如图1.3.24所示。图1.3.24 创建唯一索引2. 复合索引如果SELECT语句中的WHERE子句引用了复合索引中的所有列或大多数列，则使用复合索引可以显著地提高查询速度。创建此类索引时，应该注意定义中使用的列的顺序，通常，最频繁访问的列应该放置在列表的最前面。图1.3.25中的代码用于创建复合索引。图1.3.25 创建复合索引创建位图索引图1.3.24中对emp表中的ename和job列创建了复合索引，当查询emp表时，如果WHERE条件类似于“WHERE name=JACK AND job=CLERK”语句，则Oracle将会使用复合索引。3. 位

43、图索引由于emp表的job列、deptno列的取值范围有限，并且经常需要基于这些列进行查询、统计、汇总工作，所以应该基于这些列创建位图索引。图1.3.26中的代码用于创建位图索引。图1.3.26 创建位图索引在图1.3.27中，从执行计划可知，由于在表emp的job列上创建了位图索引，所以在以job为条件查询emp表时，Oracle就会使用以上位图索引。图1.3.27 Oracle查询使用位图索引4. 函数索引为了使在查询条件中包含函数（包括SQL的内置函数或用户自定义函数）和表达式的查询语句的执行效率提高，可以适当地创建函数索引。在创建函数索引时，Oracle首先对包含索引列的函数值或表达式

44、值进行求值，然后对求值后的结果进行排序，最后再将结果存储到索引中。图1.3.28中的代码用于创建位图索引，并通过执行计划查看查询效果。图1.3.28 创建函数索引3.4.5 合并索引随着不断地对表进行更新操作，表的索引中会产生越来越多的存储碎片，这将对索引的工作效率产生负面影响。此时，用户可以通过两种方式来清除碎片：合并索引、重建索引。图1.3.29的代码用于合并索引。图1.3.29 合并索引3.4.6 重建索引实际上，重建索引是重新创建一个新的索引，然后再删除原索引。如果在索引列上频繁地执行UPDATE或DELETE操作，则应该定期重建索引，以提高空间利用率。图1.3.30中的代码用于重建索

45、引。图1.3.30 重建索引3.4.7 监视索引的使用情况已经创建的索引是否能够有效地工作，取决于在执行SQL语句的过程中Oracle是否会使用到该索引。这一点并非由用户来决定，但DBA必须了解。Oracle提供了一种比较简便的方法来监视索引的使用情况，即在V$object_usage数据字典动态性能视图中记录索引的使用情况。监视图索引的步骤如下：（1）使索引idx_bm_emp_job处于被监视状态。查询V$object_usage，了解索引的被监视情况，如图1.3.31所示。3. 再次查询V$object_usage数据字典，确定索引处于被监视状态1. 查询V$object_usage数据

46、字典，查看被监视的索引2. 修改idx_emp_ename_job索引，使其处于被监视状态图1.3.31 使索引处于被监视状态在图1.3.31中，如果MON列的值为YES，则表示该索引已经处于监视状态；如果USE列的值为NO，则表示从开始监视（START_MONITORING表示开始时间）以来还未被使用过。（2）执行SQL语句，查看被监视索引的使用情况，如图1.3.32所示。由图可知，idx_bm_emp_job行对应的USE列的值为YES，表示从开始监视以来已经使用过索引。图1.3.32 执行SQL语句，查看被监视索引的使用情况（3）关闭索引监视状态。先关闭idx_bm_emp_job索引的

47、监视关闭，然后查询V$object_usage数据字典，如图1.3.33所示。由图可知，END_MONITORING列已经被设置了时间，表示此次对该索引的监视结束了。关闭索引监视状态图1.3.33 关闭索引监视状态提问现在有3条SQL语句，如何使用索引监视技术获取执行每条SQL索引的使用情况？3.4.8 查看索引信息可以使用user_indexes数据字典视图查看一个表中所有的索引信息，如图1.3.34所示。图1.3.34 查看指定表中所有的索引其中：（1）INDEX_NAME：表示索引名。（2）INDEX_TYPE：表示索引类型。其中，FUNCTION-BASED NORMAL表示基于函数的

48、B树索引，BITMAP表示位图索引，NORMAL表示普通的B树索引。（3）UNIQUENES：表示该列是否是唯一索引。3.4.9 创建索引的原则并不是所有的表或者表中所有的列都需要创建索引，也不是在一张表上创建的索引越多越好，DBA应当只在适当的表或列上创建适当的索引。以下是创建索引应该遵循的一般原则：（1）一般不需要为数据量很小的表创建索引。（2）对于数据量比较大的表，如果经常需要查询的记录数小于表中所有记录数的15%，则可以考虑为该表创建索引。这个百分比并不是绝对的，它主要取决于进行一次全表扫描的速度，全表扫描的速度越快，百分比越低。（3）应该为大部分列值不重复的列创建索引。（4）对于取值

49、范围较大的列（如ename列），应该创建B树索引；对于取值范围较小的列（如sex列），应该创建位图索引。（5）对于包含很多个NULL值，但是经常需要查询所有非NULL值记录的列，应当创建索引。（6）不能在LONG或BLOB等大对象数据类型的列上创建索引。（7）对于经常需要进行连接（join）查询的多个表而言，在用于连接的列上创建索引能够显著提高查询的速度。（8）Oracle会自动地为具有PRIMARY KEY约束和UNIQUE约束的列创建索引，但是不会自动地为FOREIGN KEY列创建索引，所以用户需要为连接查询中使用到的外键列创建索引。（9）虽然一张表可以拥有任意数目的索引，但是表中的索引数目越多，维护索引所需的开销就越大。向表中插入、删除或更新一条记录时，Oracle都必须对该表的所有索引进行更新。因此，用户必须在表的查询速度和更新速度之间找到一个合适的平衡点。如果在大部分情况下只需要对表执行只读操作，就可以为该表创建更多的索引以提高查询速度；如果在大部分情况下需要对表执行更新操作，则应该为少创建一些索引，以提高更新速度。本章