老二牛车第四章理论课数据库对象

Oracle数据库应用 理论课 数据库对象 本章 技能 目标 使用同义词 使用序列 创建视图 创建索引 1. 数据库对象 Oracle 数据库对象又称模式对象 。 数据库对象是逻辑结构的集合，最基本的数据库对象是表 。 其他数据库对象包括： 同义词，序列，视图，索引 。 下面我们依次讲解这几个数据库对象的使用。 2. 同义词 同义词是数据库对象的一个别名，这些对象可以是表，视图，序列，过程，函数，程序包，甚至其它同义词，通过使用同义词，用户可以访问其它模式的数据库对象而无需指定模式前缀，例如用 户 USER1 要访问用户 USER2 的表 EMP，必须使用 USER2.EMP,那么 USER1 就可以使用同义词像访问自己的表一样引用 USER2.EMP 了 。 同义词有以下用途： 简化 SQL 语句 隐藏对象的名称和所有者 提供对对象的公共访问 为分布式数据库德远程对象提供了位置透明性 同义词允许应用程序访问数据库对象，不论那个用户或哪个数据库拥有该对象。但是同义词不能代替权限，在使用同义词之前确保用户已得到访问对象的权限。 可以通过同义词执行 SELECT， INSERT， UPDATE， DELETE， LOCK TABLE ,GRANT和 REVOKE等语句。同义词只是表的一个别名，因此对它的操作都会影响到表。 同义词共有两种类型： 公有同义词。 私有同义词。 2.1 私有同义词和公有同义词 第 4章 数据库对象 - 2 - 2 私有同义词只能在其模式内访问，且不能与当前模式的对象同名。 要在自身的模式创建私有同义词，用户必须拥有 CREATE SYNONYM 系统权限。要在其它用户模式的创建私有同义词，用户必须 CREATE ANY SYNONYM 系统权限 .。 创建私有同义词语法 如下 ： Create OR REPLACE SYNONYM schema.synonym_name FOR schema.object_name； 其中： OR REPLACE 表示在同义词存在的情况下替换该同义词。 synonym_name 表示要创建的同义词的名称。 object_name 指定要为之创建同义词的对象的名称。 例 1： 假定两个用户模式： ACCP 和 SCOTT ,SCOTT 用户拥有表， ACCP 用户需要频繁引用表，为了简化语句，需要为 ACCP 用户创建一个同义词。首先，应该给 ACCP用户授予访问 EMP 表的权限，以 SCOTT 用户登录数据库，执行以下命令。 SQLGRANT ALL ON EMP TO ACCP； 再以 ACCP 用户身份登录数据库，执行以下命令。 SQLCREATE SYNONYM EMP FOR SCOTT.EMP； 如果上述命令成功执行，将显示消息 ”同义词已创建 ”,现在， ACCP 用户可以使用下面的语句访问SCOTT 用户的 EMP 表了。 SQLSELECT * FROM EMP； 公有同义词可被所有的数据库用户访问 ,公有同义词可以隐藏基表的身份，并降低 SQL 语句的复杂性。要创建公有同义词，用户必须拥 有 CREATE PUBLIC SYNONYM 系统权限。 创建公有同义词语法 如下 ： Create PUBLIC SYNONYM synonym_name FOR schema.object_name； 例 2 演示创建 公有同义词 例 2： create public synonym emp_syn from scott.emp； 如果用户拥有足够权限的话，就可以创建 公 有同义词。 例 2 演示了如何创建一个名为 emp_syn 的公有同义词。其他模式下的用户可通过访问公有同义词emp_syn 来引用该表。 系统会在安装的时候创建一些数据字典公有的同义词，如名为 ”TAB”的同义词可以用来查询用户所拥有的表和视图对象。为了清楚的理解本地对象和公有对象的概念。 例 3： 登录到 SQL*PLUS，查询公有同义词； SELECT * FROM ； 上述命令显示当前用户拥有的表和视图的列表。 下面创建一个名为 ”TAB”的表，如下： 第 4章 数据库对象 - 3 - 3 CREATE TABLE TAB (A NUMBER)； 创建表之后，再执行如下的命令。 SELECT * FROM ； 结果没有显示行。这是因为刚刚创建的表 ”TAB”中 还没有数据。由此可以理解，当公有对象和本地对象具有相同的名称时，本地对象优先。 可以查询字典视图 User_Synonyms 来查看用户所创建的同义词的详细信息 2.2 删除同义词 Drop Synonym 语句用于从数据库删除同义词。 删除同义词语法： Drop Synonym synonym_name； 删除公有同义词加上一个 Public。 下例演示删除同义词。 SQL DROP PUBLIC SYNONYM emp_syn； 此命令只删除同义词，不会删除对应的表。 3. 序列 序列是用于生成唯一、连续序 号的对象。序列通常用来自动生成主键或唯一键的值。序列可以是升序的，也可以是降序的 。 创建序列语法如下： Create SEQUENCE sequence_name START WITH integer INCREMENT BY integer MAXVALUE integer|NOMAXVALUE MINVALUE integer|NOMINVALUE CYCLE|NOCYCLE CACHE interger|NOCACHE； 其中： START WITH 是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。 INCREMENT BY 是用于指定序列号之间的间隔。其默认值为 1。如果 integer 为正值，则生成的序列将按升序排列，否则按降序排列。 MAXVALUE 指定序列可以生成的最大值。 NOMAXVALUE 这是默认选项，将升序序列的最大值设为 10 的 27 次幂，将降序序列的最大值设为 -1。 MINVALUE 指定序列的最小值。 MINVALUE 必须小于或等于 START WITH 的值，并且必 须小于MAXVALUE。 NOMINVALUE 这是默认选项，将升序序列的最小值设为 1，将降序序列的最小值设为 -10 的 26 次幂。 CYCLE 指定序列在达到最大值或最小值后，将继续从头开始生成值。 NOCYCLE 这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。 CACHE 使用 CACHE 选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。 NOCACHE 此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了 CACHE 和 NOCACHE选项， Oracle 将默认缓存 20 个序 列号 例 4 演示如何创建序列。 第 4章 数据库对象 - 4 - 4 例 4： SQL CREATE SEQUENCE toys_seq START WITH 10 INCREMENT BY 10 MAXVALUE 2000 MINVALUE 10 NOCYCLE CACHE 10； 3.1 访问序列 创建了序列之后， 可以通过 CURRVAL 和 NEXTVAL 伪列来访问该序列的值 。 可以从伪列中选择值，但是不能操作它们的值。 下面分别说民 NEXTVAL 和 CURRVAL。 NEXTVAL 返回序列的下一个值 。 创建序列后第一次使用 NEXTVAL 时，将 返回该序列的初始值。以后在引用 NEXTVAL 时，将使用 INCREAMENT BY 子句的值来增加序列值，并返回这个新值。 CURRVAL 返回序列的当前值 。 即最后一次引用 NEXTVAL 时返回的值。 例 5 演示序列的用法。 例 5： SQLINSERT INTO toys(toyid,toyname,toyprice) values(p|toys_seq.NEXTVAL,TWENTY,25)； SQL INSERT INTO toys(toyid,toyname,toyprice) values(p|toys_seq.NEXTVAL,MAGIC PENCIL,75)； 上述语句 从序列 toys_seq 中选择值插入 toys 表中的 toyid 列。执行成功将会在该表的 toyid列插入值 P10和 P12。 例 6 演示如何查看序列当前值 例 6： Select toys_seq.CURRVAL from dual； 上述语句将显示序列的 CURRVAL 值为 12 3.2 更改序列 ALTER SEQUENCE 命令用于 修改序列的定义。 如果执行下列操作，则会修改序列。 设置或删除 MINVALUE 或 MAXVALUE 修改增量值 修改缓存中的序列号的数目。 修改序列语法如下： ALTER SEQUENCE schema.sequence_name INCREMENT BY integer MAXVALUE integer|NOMAXVALUE MINVALUE integer|NOMINVALUE CYCLE|NOCYCLE CACHE interger|NOCACHE； 注意，不能修改序列的 START WITH 参数。在修改序 列时，应注意升序序列的最小值应小于最大值。 例 7 演示如何更改序列。 例 7： SQL ALTER SEQUENCE toys_seq 第 4章 数据库对象 - 5 - 5 MAXVALUE 5000 CYCLE； 例 7 演示如何设置一个新的 MAXVALUE，并为 toys_seq 序列打开了 CYCLE。 可以查询字典视图 User_Sequences 来查看用户所创建的序列的详细信息 。 3.3 删除序列 使用 DROP SEQUENCE 语句删除序列 ，还可使用该语句重新开始一个序列，方法是首先删除一个序列，然后重新创建该序列。 下面的命令从数据库中删 除了 toys_seq 序列。 SQL DROP SEQUENCE toys_seq； 4. 视图 视图以经过定制的方式显示来自一个或多个表的数据 。 视图可以视为 “虚拟表 ”或 “存储的查询 ”。创建视图所依据的表称为 “基表 ”。 视图的优点有： 提供了另外一种级别的表安全性 隐藏的数据的复杂性 简化的用户的 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 此选项确保不能在此视图上执行任何修改操作。 下面来看一个创建视图的示例。 例 8 演示了如何基于 vendor_master 表创建一个名为 ven_view 的视图 。 例 8： Create VIEW ven_view AS select * from vendor_master； 上述命令 创建一个名为 ven_view 的视图，该视图与 vendor_master 表具有相同的结构。 第 4章 数据库对象 - 6 - 6 例 9 演示 如何基于 SELECT 语句的 WHERE 子句所给定的条件来创建视图。下面的语句创建了一个名为 pause_view 的视图。该视图仅包含暂挂订单的数据。 例 9： CREATE VIEW pause_view AS SELECT * FROM order_master WHERE ostatus = p； 上述命令将创建一个视图，该视图仅显示 ostatus 值为 p 的行。如果视图更新 那些无法通过此视图查看的行， Oracle 将不会产生任何错误，而是直接更新相应得行。 例 10 演示如何修改列值（该列是创建视图时形成 WHERE 子句条件的列）。下列语句将订单状态的值修改为 d,以表明该订单已发出。 例 10： UPDATE pause_view SET ostatus = d WHERE ostatus = p； 虽然可以执行上述 UPDATE 语句，但同时会使该行数据在视图中不可见。视图定义表明，该视图仅显示订单状态为 p 的行。但是由于执行了上述的 UPDATE 语句，将行的订单状态修改为 d，因 此无法通过该视图察看这些行。 在创建视图时，通过使用“ WITH CHECK OPTION”，可以防止发生这种情况，如例 11 所示。 例 11： CREATE OR REPLACE VIEW pause_view AS SELECT * FROM order_master WHERE ostatus = p WITH CHECK OPTION CONSTRAINT chk_pv； 上述语句创建了一个视图，可以显示 ostatus 列的值为 p 的行。如果试图修改 ostatus 的值。 Oracle 将发出错误消息。 如 例 12 所示。 例 12： 在 SQL 提示符下，执行下列视图修改 ostatus 列值得 UPDATE 语句。 UPDATE pause_view SET ostatus = d WHERE ostatus = p； 则显示如下内容。 ERROR 位于第 1 行： ORA-01402：视图 WITH CHECK OPTION 违反 WHERE 子句 可以创建视图来显示表中特定列的数据。 例 13 演示了如何用制定列来创建视图。 例 13 ： 下列语句通过从 vendor_master 表中选择 orderno 和 odate 列来创建视图 。 CREATE OR REPLACE VIEW pause_view AS SELECT orderno, odate FROM order_master； 如果选择特定的列来创建视图。则通过该视图只能更新这些列。 UPDATE pause_view SET ostatus = d； 如果试图更新其他列。 Oracle 都将返回一个错误。 ERROR 位于第 1 行： ORA-00904：“ OSTATUS”：无效的标识符 如果基表的其他列接受空值，甚至可以向视图中插入行。如果该表的任何列（在视图的查询中 没有选择的第 4章 数据库对象 - 7 - 7 列）被定义为非空，则不能插入行 。 下面创建一个只读视图。例 14 演示了如何创建只读视图。除了选择操作，不允许在此视图上执行其它任何操作。 例 14： 执行下列语句，以创建一个名为 pause_view 的只读试图。 CREATE OR REPLACE VIEW pause_view AS SELECT * FROM order_master WITH READ ONLY； 只能从该视图选择和查询数据。 UPDATE pause_view SET ostatus = d； 其他任何操作都将产生如下 所示的错误消息。 ERROR 位于第 1 行： ORA-01733：此处不允许虚拟列 4.1 视图中的 ORDER BY 子句 可以在创建视图时在 SELECT 语句中使用 ORDER BY 子句，以便按照指定的顺序对行进行排序。这样 ： 在查询时视图即使不使用 Order By 子句， 结果集也会按指定的顺序排列行。 例 18： CREATE OR REPLACE VIEW ord_ven AS SELECT * FROM vendor_master ORDER BY venname； Create OR REPLACE VIEW ven_view(编号 ,日期 ) AS select orderno,odate from vendor_master order by venname； 当检索视图时，数据将基于 venname 列按升序排序。 4.2 创建带有错误的视图 创建带有错误的视图：使用 FORCE 选项 。 在以下情况下， Oracle 也会创建视图： 视 图 定义的查询引用了一个不存在的表 ； 视图定义的查询引用了现有表中无效的列 ； 视图的所有者没有所需的权限。 例 16： 下面创建一个基于 venmast 的视图。但数据库中并不存 在名为 venmast的表。 Create FORCE VIEW ven AS select * from venmast； 如果执行上述语句，即使名为 venmast 的表不存在，也将创建 该 视图。 但是 Oracle 将返回警告消息。 如果稍后创建名为 venmast 的表，可以使用 ALTER VIEW ven COMPILE； 4.3 DML 语句和 联接视图： 此处的 DML 语句是指用于修改数据的 INSERT， DELETE 和 UPADTE 语句。因为视图是一个虚拟的表，所以这些语句也可以与视图一起使用。如果一个视图基于单 个基表，那么可以在此视图中进行第 4章 数据库对象 - 8 - 8 INSERT， DELETE 和 UPADTE 操作，这些操作世纪上是在基表中插入，更新和删除行。 一般情况下不通过视图修改数据，而是直接修改基表，因为那样调理清晰。在视图上使用 DML 语句有如下的限制： 在视图中使用 DML 语句只能修改一个底层的基表。 如果对记录的修改违反了基表的约束条件，则无法更新视图。 如果创建的视图包含连接运算符， DISTINCT 运算符，集合运算符，聚合函数和 GROUP BY 子句，则无法更新视图。 如果创建的视图包含伪列或表达式，则将无法更新视图。 联接视图 是在 FROM 子句中制定多个表或视图的视图。在 联接视图 中使用 DML 语句只能修改单个基表，如果修改多个基表， SQL 就会显示错误。但是， Oracle 提供了“ INSTEAD OF 触发器”，使用该触发器，可以通过视图同时对多个表执行 DML 操作。 例 17 演示如何创建联接视图 。 例 17： Create OR REPLACE VIEW ven_ord_view AS select vm.vencode,venname,orderno,odate,ostatus from vendor_master vm,order_master om where vm.vencode=om.vencode； 例 17 创建了联接 vendor_master vm 与 order_master 的 ven_ord_view 视图。 联接可分为内联接和外联接，内联接从两个表中选择匹配的行。外联接允许用户从一个表中选择所有行并从另一个表中选择匹配的行。例 18 演示如何创建外联接视图。 例 18： CREATE OR REPLACE VIEW ven_ord_outj_view AS SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm, order_master om WHERE vm.vencode = om.vencode(+)； 提示：在 WHERE 子句中使用（ +）符号是 Oracle 特有的外联接语法 。 SELECT vm.vencode, venname, orderno, odate, ostatus FROM vendor_master vm LEFT OUTER JOIN order_master om ON vm.vencode = om.vencode； 4.4 键保留表 在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，则这个表被称为键保留表。 Oracle 可以通过此视图向表中插入行。包含外部联接的视图通常不包含 键保留表 ，除非外部联接生成非空的值。 Oracle 可以确定哪些表是键保留的，只有键保留表才能使用 DML 语句。通过例 19 创建的ven_ord_view 视图可以理解此概念。 ven_ord_view 视图以 vendor_master 和 order_master 这两个表为基表。 order_master 表被视为键保留表，因为 orderno 既是 order_master 表 的主键，也是视图的主键。而 vendor_master 表不被视为键保留表，因为 vendor_master 表的主键 vencode 是联接字段，不是视图的主键。 例 19： UPDATE ven_ord_view SET odate = odate+1 WHERE vencode =V003； 第 4章 数据库对象 - 9 - 9 上述语句 成功修改了记录，因为 odate 列属于键保留表 order_master。 例 20 对于非键保留表 vendor_master 的 venname 列的更新则是不允许的 ，豫剧失败返回 错误消息。 例 20： UPDATE ven_ord_view SET venname = Michael WHERE vencode = V004； ORA-01779：无法修改与非键值保存表对应的列 可以查询字典视图 User_Updateble_Columns 来查看联接视图中可更新的列。 SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = VEN_ORD_VIEW； 提示：运行以上两个示例，必须确保基表的主键已正确创 建，否则 Oracle 不能识别键保留表，不允许更新联接视图中的任何列。 4.5 视图中的函数 在 视图可以使用单行函数 (由数字、字符、日期组成 )、分组函数和表达式。 例 21 演示了函数在视图中的用法。 例 21： Create VIEW vendor_master_view AS select vencode,UPPER(venname) vendor_name from vendor_master； 上述 示例用 UPPER 函数创建视图，注意使用函数或表达式时，应赋予列一个别名。 4.6 删除视图语法 如果要 从数据库删除视图，可以使用 DROP VIEW 命令。 例 22 演示如何删除视图。 例 22： Drop VIEW view_name ； 可以通过查询 USER_VIEWS 数据字典视图来获得用户创建的视图信息。 5. 使用索引 索引是与表相关的一个可选结构 ,可以明确地创建索引，以加快对表执行 SQL 语句的速度。就像书的索引一样可以帮助我们更快地查询信息一样， Oracle 中的索引也提供了一种更快递访问表数据的途径。 当索引键列用于 SQL 语句的 WHER 条件子句中时，该索引将直接指向包含这些值得行的位置。 合理使用索引是减少磁盘 I/O 的主要方法。不管索引是否存在，都无需修改任何 SQL 语句的定义。索引只是一种快速访问数据的途径，它只影响执行的速度。 可以使用 CREATE INDEX 命令在一列或若干列的组合上创建索引。 创建普通索引的语法 如下 ： Create INDEX index_name ON table_name(column_list) TABLESPACE tablespace_name； 其中： index_name 指所创建索引的名称。 第 4章 数据库对象 - 10 - 10 table_name 表 示为之创建索引的表名。 column_list 是在其上创建索引的列名列表，可以基于多列创建索引。 tablespace_name 为索引指定表空间。 例 23 演示如何在 itemfile 表的 itemcode 列上创建索引 。 例 23： create index item_index on itemfile(itemcode)； 创建索引时， Oracle 将获取要创建索引的列，并对其进行排序。然后，将 ROWID 连同每一行的索引值存储起来。 Oracle 基于 itemcode 列对 itemcode 表进行排序。然后按照此排序顺序，用itemcode 及其相应得 ROWID 加载索引，使用索引时， Oracle 首先通过已排序的 itemcode 值进行快速搜索。然后使用相关联的 ROWID 值来定义具有所要查找的 itemcode 值的行。 索引在逻辑上物理上都独立于关联表中的数据，在任何时候都可以创建或删除索引，而不会影响基表或其它索引。 如果删除索引，所有的应用程序都将继续运行，但在访问原先被索引的数据时，速度会降低。与视图不同的是，索引是独立的结构，因此需要存储空间。 一旦创建了索引， Oracle 会 自动维护和使用它们。只要修改了数据，如添加新行，更新现有行或删除行， Oracle 都会自动更新索引。即使插入了新行，已索引的数据的检索性能也几乎保持不变。但是为表创建过多的索引会降低更新，删除以及插入的性能。因为 Oracle 还必须更新与该表关联的索引。 索引有各种类型，除了标准索引外，还有一些特殊类型的索引： 唯一索引 组合索引 反向键索引 位图索引 基于函数的索引。 ALTER INDEX 语句的 REBUILD 选项可以用来重建现有的索引。该选项提供的性能要优于使用 DROP INDEX 和 CREATE INDEX 语句重新创建索引 。例 24 演示如何 重建索引 。 例 24： ALTER INDEX item_index REBUILD； DROP INDEX 语句用于删除索引。 例 25 演示如何删除索引。 例 25： Drop INDEX item_index； 5.1 唯一索引 索引可以是唯一的，也可以是非唯一的，唯一索引确保在定义索引的列中没有重复值，表的任意两行的值都不相同。非唯一索引没有在列值上规定此限制。 Oracle 自动在表的主键列上创建唯一索引，可以使用 CREATE UNIQUE INDEX 语句创建唯一索引 。例 26 演示 如何创建唯一索引。 例 26： Create UNIQUE INDEX item_index ON itemfile(itemcode)； 上述语句 在 itemfile 表的 itemcode 列上创建了一个名为 item_index 的唯一索引。 第 4章 数据库对象 - 11 - 11 5.2 组合索引： 组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。 创建组合索引时，应注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最前面。 例 27： Create INDEX comp_index ON itemfile(p_category,itemrate)； 上述语句 在 itemfile 表上创建了一个名为 comp_index 的组合索引，当查询该表的的 WHERE 子句同时包含这两个列或只包含 p_category 列时，以下示例语句创建的索引将用于检索数据，但如果单独使用 itemrate 列，则索引不能用于检索数据。 5.3 反向键索引： 反向键索引 是一种特殊的索引， 如果一个标准索引基于一个含有这种数据的列，往往会因为数据过于密集而降低读取性能。反向键索引通过简单的反向被索引的列中的数据来解决问题，首先反向每个列键值的字节， 然后在反向后的新数据上进行索引，而新数据在值得范围上的分布通常比原来的数据更均匀。 通常建立在一些值连续增长的列上，例如列中的值是是由序列产生的情况 。 可以在 CREATE INDEX 语句中制定关键词 REVERSE 创建反向 键索引 。例 28 演示如何创建反向键索引 。 例 28： Create INDEX rev_index ON itemfile(itemcode) REVERSE； 上述语句 在 itemfile 表上创建了一个名为 rev_index 的反向键索引。注意使用 REVERSE 关键字。 使用关键字 NOREVERSE 可以将反向键索引重建为标准索引。 例 29 演示如何将 反向键索引 重建为标准索引。 例 29： ALTER INDEX rev_index REBUILD NOREVERSE； 注意：不能将标准索引重建为反向键索引。 5.4 位图索引： 使用 位图索引 的优点在 于，它适合创建在低基数列上，也就是不同值得数目闭表的行数少的列。 例如：类别，操作员，部门 ID,库房 ID 等 。 如果某个列的值重复超过一百次，则可以考虑在该列上创建位图索引。 例如，如果一个表的数据有 100 万行，其中一个列有小于 1000个不同的值， 则可以考虑在该列 上创建位图索引 。 例 30： Create BITMAP INDEX bit_ind1 ON order_detail(itemcode)； itemcode 是 order_detail 表中的低基数列，因为货物编码在大多数订单中都是重复的，因此适合在该列上创建位图索引。 位图索引为什么可以提高基数比较小的表的查询速度呢 ?这主要是因为在创建位图索引的时候，数据库往往会对整个表进行扫描，并对未索引列的每个取值建立一个位图 (位图索引的名字也由此而来 )。在这个位图中，为表中的每一行使用一个位元来表示该行是否包 含该位图的索引列的取值。位元到行的 ROWID 的对应关系通过位图索引中的映射函数来完成。如此的话，位图索引就能够以一种完全不同的内部机制来完成索引相同的功能。 第 4章 数据库对象 - 12 - 12 位图索引 具有以下优势： 减少响应时间 相比 其他索引，节省空间占用 在配置低的终端硬件上，也能获得显著的性能。 位图索引可以快速的解析查询的 WHERE 子句中的 AND 或者 OR 操作符。如果查询结果中的行的数目很少，可以很快地相应查询，而不必对表进行完全扫描。 位图索引不应当用在频繁发生的 INSERT,UPDATE,DELETE 操作的表上。位图索引 最适合于数据仓库和决策支持系统。 用于 要为读操作的表 。 5.5 索引组织表： 索引组织表与普通表的不同之处在于，该表的数据存储在与其关联的索引中。对表数据进行的修改，如添加新行，更新行，只会导致对索引的更新。 索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间， 数据库系统仅维护一个索引，该索引包含相应的已编码键值和与其关联的列值。 行的实际数据存储在索引中，而不是将行的 ROWID 作为索引条目的第二个元素。 使用 ORGANIZATION INDEX 子句来创建索引组织表。 例 31 演示如何创建索引组织表。 Create table ind_org_tab ( vencode NUMBER(4) primary key, venname VARCHAR2(20) ) organization index； 注意： primary key 是创建索引组织表所必需的。不允许使用分区。 索引组织表适合于通过主键来访问数据。 与唯一索引一样，索引组织表没有重复的键值，因为只有非键列的值与该键存储在一起。 普通表与索引组 织表的比较 区别点 普通表 索引组织表 数据访问 有隐式的 ROWID 列，根据 ROWID 访问数据 根据主键访问数据，没有隐式的 ROWID 列 唯一标识 行仅由 ROWID 唯一地标识行允许使用UNIQUE 约束条件 行仅由主键标识，不允许使用 UNIQUE 约束条件 主键 主键可以有选择地指定 必须指定主键 分区 支持分区 不支持分区 索引组织表的优点如下： 由于行存储在索引中，对于要求精确匹配的查询和基于范围的搜索，索引组织表提供了一种更快的，基于键的表数据访问方法。 由于键列在表和索引中不重复，因 此降低了存储需求。 与键一起存储的数据行仅包含非键列的值。而且将数据行同键存储在一起，还可以消除 ROWID 所需的额外存储空间，而对于普通表的索引，需要使用 ROWID 将键值与相应的行链接起来。 5.6 基于函数的索引： 第 4章 数据库对象 - 13 - 13 如果在 WHERE 子句的算术表达式或函数中已经包含了某个列，则不会使用该列上的索引。 为了方便此类的操作， Oracle 提供了一个选项，可以基于一个或多个列上的函数或表达式创建索引。用于创建索引的函数可以是算术表达式，也可以是包含 PL/SQL 函数，程序包函数或 SQL 函数的表达式。不能在表达式包含任何聚合函 数， LOB 列、 REF 列或包含 LOB 或 REF 的对象类型上创建基于函数的索引。 以下例情况为例 ： venname 是 vendor_master 表的一个列，用于存储供应商的姓名，假定所有供应商的姓名都以混合大小写的形式存储 (如： John Smith、 Dave Jones、 Tony Greig 等等 )，同时假定我们经常需要根据供应商的姓名来查询表的数据。由于 姓名是以混合大小写的形式存储的，因此可能很难给出姓名的正确大小写形式。 假定在查询的 WHERE 子句中使用 UPPER（）函数，则查询会花费很长时间，因为索引值是混合 大小写形式的姓名，由于索引中没有适合于大写姓名的条目，因此 Oracle 无法适用在该列上创建的标准索引。 可以通过下面的索引，可以解决此问题。 可以创建如下索引： Create INDEX vn_ind ON vendor_master(UPPER(venname)； 要创建基于函数或表达式的索引，必须具有 QUERY REWRITE 系统权限 。 例 32 演示如何使用前面创建的基于函数的索引检索数据。 例 32： select * from vendor_master where UPPER(venname)=SMALL； 上述语句在查询的 WHERE 子句中使用了 UPPER 函数作为条件，可以利用前面创建的 vn_ind 索引。 5.7 索引中的分区 与对表分区类似， Oracle 也允许对索引分区。与表分区一样，索引分区可以存储在不同的表空间中，与分区有关的索引有 2 种类型，局部分区索引，全局分区索引。 局部分区索引 局部分区索引是在分区表上创建的一种索引，在局部分区索引中， Oracle 为表的每个分区建立一个独立的索引，因为每个分区有一个索引，所以这些索引对于分区来说是“局部”的，通过在 CREATE INDEX 语句中指定 LOCAL 属性，可以在分区上创建局部索引。 Oracle 在与基础表相同的列上对索引分区，创建数目相同的分区。并指定相同的分区边界。 当添加，删除或拆分基础表的分区时， Oracle 会自动维护索引分区。 例 33： 如下所示创建分区表。 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) )； 接着在上面创建的分区表上创建局部索引。 第 4章 数据库对象 - 14 - 14 CREATE INDEX myind ON order_mast(orderno) LOCAL； 这是一个局部分区索引。如果查询名为“ USER_SEGMENTS”的字典视图，可以发现该索引是作为单独得段为所有分区创建的。 例 34： SELECT segment_name, partition_name, segment_type, tablespace_name FROM USER_SEGMENTS WHERE segment_name = MYIND； 上述查询的输出结果如下： SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME MYIND OE1 INDEX PARTITION ACCPSAMPLE MYIND OE2 INDEX PARTITION ACCPSAMPLE MYIND OE3 INDEX PARTITION ACCPSAMPLE 全局分区索引 全 局分区索引是指在分区表或非分区表上创建的索引，全局索引的键可以引用存储在多个分区中的行。例 35 演示如何创建全局索引。 例 35： 在上面创建的分区表上创建全局索引。 CREATE INDEX glb_ind ON order_mast(orderno) GLOBAL PARTITION BY RANGE (orderno) ( PARTITION ip1 VALUES LESS THAN (1500), PARTITION ip2 VALUES LESS THAN (MAXVALUE) )； 上述语句在有 3 个分区的表上创建两个分区的索引。 下面分别对这 2 种索引的特点和局限性做个总结。 局部索引 local index 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。 如果局部索引的索引列以分区键开头，则称为前 缀局部索引。 如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。 局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向 n 个表分区，同时，一个表分区，也可能指向 n