索引与视图

索引

索引的概念：

在关系型数据库中，索引是对数据库表中一列或多列的值进行排序后单独的物理存储结构。

在Oracle中，索引是除了表之外的另一种重要的数据库方案对象。索引的主要用途是提高数据表的查询速度，它可以独立于表进行存储。通过在表中建立索引，可以在索引中找到符合查询条件的索引值，然后再通过保存在索引中的 ROWID 快速找到表中对应的记录。

• 索引是一个单独的物理存储结构，可以有自己的存储空间，不必与相关联的表处在同一个表空间中。
• 索引由表中一列或多列值的集合和这些值所在的 ROWID（表中数据行的唯一性标识，它不能指示出行的物理位置，但可以用来定位行） 组成。
• 在Oracle系统中，对索引的应用和维护是自动完成的。

• 使用索引的好处：

  • 创建唯一索引后可以保证每行数据的唯一性。
  • 可以加速检索数据的速度。
  • 多表查询时，可以加速表之间的连接。
  • 明显减少分组和排序的时间。

• 建立索引后的不足：

  • 创建和维护索引需要消耗额外的时间和空间。
  • 对表中数据进行DML操作时，也要动态维护索引，降低了处理数据的速度。

• 适合创建索引的字段应具备以下特征：

  • 取值范围较大的字段。
  • Null值较多的字段。
  • 经常作为查询或连接条件的字段。
  • 经常需要排序的字段。

• 不适合建立索引的表、字段具备以下特征：

  • 较小的表。
  • 经常更新的表。
  • 不常作为查询条件或连接条件的字段。

索引的类型：

Oracle可以创建多种不同类型的索引，以适应各种表的特点。常见的索引类型包括Balance-tree索引、位图索引、反向键索引、基于函数的索引、全局索引和局部索引等。

Balance-tree索引：

• Balance-tree索引是最常见的索引结构。
• Oracle采用的默认索引类型。
• 其组织结构类似于一棵树，其中的主要数据都集中在叶子结点上。
• 各叶子结点中包括：索引列的值和数据表中对应的 ROWID。
• 特别适用于检索高基数数据列，即所查询的列的唯一性索引值的个数与其数据行记录数之比接近于1:1的情况（被索引的列值基本没有相同的值）。【我理解为该列数据多样化】

• Balance-tree索引的优点如下：

  • Balance-tree中所有叶子结点基本都处于同一深度，查询时消耗的时间基本相同。
  • Balance-tree的索引结构是自动保持平衡的。
  • Balance-tree为一定范围的查询提供了极好的性能，包括精准匹配和范围查找。
  • Balance-tree的插入、更新和删除效率高。
  • Balance-tree索引的性能不会随着表大小的增长而降低。

• Balance-tree索引的局限性：当数据检索的范围超过表的10%时就不再适合使用Balance-tree索引。

位图索引：

• 位图索引也采用Balance-tree索引，只是索引值全部集中在叶子结点。
• 位图中的每一个位对应一个 ROWID。（例如某一位的值为1，则对应ROWID的行包含该索引键值）这种方式叫做位图的映射功能，即将数据位的位值转化为实际的ROWID。
• 位图索引适用于检索?很少有唯一值的列，例如性别。
• 位图索引的使用情况：如果列中唯一值的个数与表中总的记录行数之比少于1%，则该列可采用位图索引。【我理解为该列数据种类较为单一】
• 位图索引中不再记录ROWID和键值，而是将每个索引值作为一列，用1和0表示该行中是否包含该索引值。
• 由于位图索引的行顺序与原表的行顺序一致，因此给定表的关键起始和终止ROWID，可以在查询过程中对应计算出行的原始物理位置。

反向键索引：

• 反向键索引是一种特殊类型的Balance-tree索引，与常规Balance-tree索引的存储结构相同。
• 特别适合基于有序数列建立的索引。
• 反向键索引是为了避免常规Balance-tree索引的歪树现象时使用的，它会对用户输入的索引键值的字节进行反向处理，使其变为非递增状态，这样在叶子结点的分布就会比较均匀。

基于函数的索引：

• 基于函数的索引也是普通的Balance-tree索引，但它是基于表中某些字段的函数建立的，并不建立在某些字段上。
• 基于函数的索引是为了避免用户在使用基于函数的WHERE搜索条件时，Oracle会被迫进行全表搜索降低效率的情况下使用。

• 基于函数的索引有两个主要作用：

  • 只对限定的行创建索引，节约空间，提高检索速度。
  • 优化WHERE子句中使用了函数的SQL语句。

全局索引和局部索引：

为了便于管理和维护表，Oracle允许将表按照range、hash、list进行分区。表分区后，其上的索引也和普通的索引有区别。分区后的表可以建立3种类型的索引：局部分区索引、全局分区索引和全局非分区索引。
局部分区索引：

• 将表分区后，为每个分区单独建立的索引称为局部分区索引。
• 每个局部分区索引是针对单个分区的，每个分区索引只指向一个表分区，它们互相独立。
• 便于管理，多用于数据仓库环境中。

先进行表分区，再为每个分区单独建立索引。

全局分区索引：

• 全局分区索引是对整个分区表建立索引，然后再由Oracle对索引进行分区。
• 全局分区索引的各个分区之间并非相互独立，索引分区与分区表之间也并非一对一关系。

直接对整个分区表建立索引，再对这个索引进行分区。

全局非分区索引：

• 全局非分区索引就是对整个分区建立索引，但未对索引进行分区，一个索引对应着表的所有分区。

直接对整个分区建立索引，不对这个索引进行分区。

创建索引：

CREATE [UNIQUE | BITMAP] INDEX [schema.]<index_name>
ON [schema.]<table_name>
(<column_name> | <expression> ASC| DESC,
<column_name>|<expression> ASC| DESC,…)
[TABLESPACE <tablespace_name>]
[STORAGE(<storage_settings>)]
[LOGGING | NOLOGGING]
[NOSORT | REVERSE]
[PARTITION | GLOBAL PARTITION<partition_setting>]

• UNIQUE | BITMAP：UNIQUE表示创建的索引是唯一索引，要求创建索引的表达式或字段值必须唯一，不可重复，创建主键约束或唯一约束时系统自动创建对应的唯一索引；BITMAP表示创建的索引是位图索引。当省略前面这两个关键字时，默认创建的索引是可以重复的Balance-tree索引。
• [schema.] <table_name>：该子句指出了创建索引的表，其中schema指明表所属的方案名，table_name指表名。
• <column_name> | <expression> ASC | DESC：该子句指出了创建索引的列，其中column_name表示基于表中的字段创建索引，expression表示基于某个表达式创建索引。ASC表示创建的索引为升序排列，DESC表示创建的索引为降序排列。创建索引时可以指定多个字段或多个表达式，之间用逗号隔开即可。
• TABLESPACE：表示创建索引时可以为索引指定单独的表空间，可以不与相关联的表位于同一个表空间中。（当索引与所对应的表处于不同的表空间时，可以获得更好的性能）
• STORAGE：该子句设置存储索引的表空间的存储特性。
• LOGGING | NOLOGGING：表示在创建索引时是否创建相应的日志记录。
• NOSORT | REVERSE：NOSORT表示创建的索引与表中的顺序相同，不再对索引进行排序。使用NOSORT子句的目的是节省创建索引的时间和空间。REVERSE表示以相反的顺序存储索引值，即创建的索引是反向键索引。
• PARTITION | GLOBAL PARTITION：PARTITION表示建立分区索引；GLOBAL PARTITION 表示建立全局分区索引；当省略该子句时表示建立非分区索引。

创建索引时需要适当的权限才可以完成。如果用户在自己的方案中创建索引，则应该具有CREATE INDEX系统权限，如果在其他用户的方案中创建索引，则必须具有CREATE ANY INDEX系统权限。

创建Balance-tree索引：

• Balance-tree索引是创建索引时的默认类型。
• 当用户为表创建主键约束时，系统自动为该列创建一个Balance-tree索引。
• 当使用CREATE INDEX命令创建Balance-tree索引时，若包含UNIQUE关键字，则表示创建一个具有唯一值的Balance-tree索引。
• 在Oracle中可以创建基于多个字段的索引，称为“复合索引”，其中各个字段的顺序随意，一般将常用字段前置。
• 在查询时，如果WHERE子句中只包含复合索引的第一个字段，那么只有前置的第一个索引会提高查询速度，因为是前置字段。

创建位图索引：

• 当表中某一个字段的唯一值的个数比较少（基数小）时，在该字段上建立位图索引比较合适。
• 在创建位图索引时，必须显式指定BITMAP关键字。
• 由于建立位图索引的字段有许多重复值。因此位图索引不能是唯一索引。

创建反向键索引：

• 反向键索引的本质也是一个Balance-tree索引，但它不同于一般的Balance-tree索引。
• 如果建立索引的字段值顺序增长或下降，那么使用反向键索引可以避免“歪树”现象的产生。
• 反向键索引适用于严格排序的列，对键值的反向操作由系统自动处理。
• 创建反向索引时必须指定关键字REVERSE。

创建基于函数的索引：

• 在DML操作时如果经常使用某个表达式作为条件，就可以建立基于该函数的索引。
• 在创建基于函数的索引时，Oracle首先对包含索引列的函数或表达式进行求值，然后对这些值进行排序，最后再存储到索引中。
• 基于函数的索引可以是普通的Balance-tree索引，也可以是位图索引，这与函数中字段的取值特点有关系。

应用索引：

补充：Oracle对查询语句的执行过程包括解析代码、优化代码、生成代码和执行代码。

• 解析代码：是指Oracle对用户提交的查询语句进行语法检查和语义分析等操作，查询语句将变成可运行的。
• 优化代码：是指找到执行用户查询的最佳路径。这一步中Oracle可能会使用两种优化器，一种是基于规则的优化器（Rule Based Optimizer,RBO），另一种是基于开销的优化器（Cost Based Optimizer,CBO）。
• 在优化器选择了最佳路径后，Oracle会将其格式化为实际的执行方案，然后由系统的执行引擎去执行，也就是完成了生成代码和执行代码的过程。

Oracle中索引的应用是由优化器决定的，优化器根据优化的结果自动选择合适的索引来使用。
Oracle在执行命令时，在决定是否应用索引时主要和以下两个因素有关系：

• 数据表的大小。当优化器进行全表扫描时，它会一次读取一批数据块，而不是一次读取一个数据块。假设一个由50个数据块组成的表，如果优化器一次读取10个数据块，则该表需要读取5次完成全表扫描，由于索引需要3次读取，所以这种情况下优化器会使用索引来完成。但是，当表只有20~30个数据块时，全表扫描只需要2~3次读取就可以完成，这时索引就会降低获取数据的速度，因此优化器会使用全表扫描，而不使用索引。
• 用户获取记录的多少。如果用户查询需要读取记录的个数占全表的5%~20%或者更多，那么就会执行全表搜索，而不考虑使用索引。这是因为一个索引项只会指向一个单独的数据块，这样一次只能读取一个数据块，如果使用指向许多数据块的索引，那么就需要执行大量的单独数据块读取操作。

Oracle中可以使用autotrace参数来跟踪执行查询操作时索引的使用情况。
SET AUTOTRACE TRACEONLY;

视图

视图的概念：

视图是Oracle中的数据库对象，它可以针对复杂的查询进行存储为数据库对象，以便日后反复使用。

• 视图是一个逻辑虚拟表。
• 可以对试图执行各种DML操作。
• 试图并不在数据库中真正存储有数据，它的数据来自定义视图的查询所引用的表，且这些数据是在使用视图时动态生成的。
• 视图在数据库中只对应着一个SELECT语句的定义。

视图的优点：

• 简化数据，精简高效。
• 增强数据的安全性，用户被限制在数据的任意子集上，进一步加强了数据的安全访问机制。
• 定制数据，不同用户可访问的数据集不同。
• 合并于分割数据，保持表的原有结构关系，从而使外模式保持不变，应用程序可以通过视图重载数据。
• 利用视图修改源表，动态即时修改。

创建与管理视图：

视图中的数据来源于数据源表，因此在创建视图时用户应该具有对视图所引用表的查询权限。另外，用户如果在自己的方案下创建视图，需要具有CREATE VIEW权限；如果在其他方案下创建视图，需要具有CREATE ANY VIEW权限。

创建视图的语法如下：

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.] view_name [(column[ ,...n])] AS SELECT_statement [WITH CHECK OPTION | WITH READ ONLY]

• FORCE | NOFORCE：FORCE表示不管视图引用的表是否存在，都要强制创建该视图；NOFORCE表示只有基表存在时，才创建视图。省略该选项时默认为NOFORCE。
• column [ ,...n]：表示视图中的一组列名，这是为后面的查询语句中选择的列新定义的名字，替代表中原有的列名。
• SELECT_statement：表示创建视图的SELECT语句。利用SELECT语句可以从一个或多个表或者视图中获取视图中的行和列，也可以使用UNION关键字联合多个SELECT语句。（视图可嵌套）
• WITH CHECK OPTION | WITH READ ONLY：WITH CHECK OPTION表示对视图进行插入或修改时，新数据必须满足查询语句中WHERE字句后面的条件；WITH READ ONLY表示视图是只读的。当省略这两个选项时，新创建的视图是一个可修改的、对其操作不进行条件检查的一般视图。

重新编译视图：

创建视图后，Oracle会验证视图的有效性。如果在以后的操作中修改了数据源表的结构，那么可能会使视图变为无效。这时可以使用ALTER VIEW命令重新编译视图使之有效：

ALTER VIEW view_name COMPILE;

删除视图：

DROP VIEW view_name;

创建可更新的视图：

可更新的视图是指用户可以对视图执行INSERT、UPDATE、DELETE操作的视图，利用该类视图用户可以完成对数据源表的修改。
可更新的视图或视图的可更新列应具有以下特点：

• 创建时不能选择WITH READ ONLY选项。
• 视图中的非计算或非聚合运算（即数据源表中的原始字段）才可以被更新。
• 视图的定义中SELECT语句不能包含DISTINCT（去重）关键字。
• 视图的定义中SELECT语句不能包含集合操作，如UNION、INTERSECT（交集）等。
• 视图的定义中SELECT语句不能包含GROUP BY子句和HAVING子句。
• 用户必须对视图的数据源表具有显示的操作权限。
• 只有在视图中可见的行和列才能被修改或删除。

用户可以通过查询数据字典中的视图USER_UPDATETABLE_COLUMNS了解视图中的可更新列。

---

文章根据《Oracle数据库应用于开发》机械工业出版社 石彦芳、李丹著 一书中总结及补充，仅作本人学习用。
文章中若出现错误，请在评论中指出或联系我?
QQ：354008947
Email：[e-mail]