第9章 视图与索引

Select ROWID,deptno,dname,loc from dept;
ROWID -----------------AAAMfNAAEAAAAAQAAA AAAMfNAAEAAAAAQAAB AAAMfNAAEAAAAAQAAC AAAMfNAAEAAAAAQAAD
DEPTNO DNAME
ON scott.emp(sal desc)
TABLESPACE users;
例9.2 在scott.dept表的dname字段上创建一个 具有唯一性的B树索引，索引值按字母序排序。
CREATE UNIQUE INDEX index_dname ON scott.dept(dname)
例9.3 在scott.emp表的ename和deptno字段上 创建一个复合索引。
3、反向键索引
在oracle中，系统会自动为表的主键列建立索引（普通B 树索引）。对于主键值是按顺序添加的情况，随着行的插 入，索引树的层级递增很快，B树很快会变成“歪树”。
反向键索引是一种特殊类型的B树索引，特别适合基于有 序数列建立的索引。在存储结构方面，与常规的B树索引 相同。
但如果用户使用序列编号在表中输入新记录，则反向键索 引首先反向每个列键值的字节，然后在反向后的新数据上 进行索引。
创建索引的语法如下：
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
9.2.4 创建基于函数的索引
DML操作中若经常使用某个表达式当作条 件，那么可以建立基于该表达式的索引（ 或称基于函数的索引）。
基于函数的索引可以是普通的B树索引，也可 以是位图索引，这与函数中字段的取值特点有 关系。
9.2.4 创建基于函数的索引
例9.6 在scott.emp表中的hiredate字段上创 建一个基于函数的索引。
数据伪列——ROWID
以一个ROWID为例，说明它的组成： AAAMfNAAEAAAAAQAAA 数据对象号：AAAMfN 相对文件号：AAE 数据块号：AAAAAQ 数据行号：AAA
在Oracle系统中，对索引的应用和维护是 自动完成的。
当用户执行了INSERT、UPDATE、DELETE 操作后，系统自动更新索引列表。
基于函数的索引也是一种普通的B树索引，它是 基于表中某些字段的函数建立的。
函数索引的两个主要作用：
只对限定的行创建索引，节约空间，提高检索速度。 优化WHERE子句中使用了函数的sql语句。
5．全局索引和局部索引
局部分区索引
将表分区以后，为每个分区单独建立的索引，每个局 部分区索引是针对单个分区的，每个分区索引只指向 一个表分区，彼此相互独立。多用于数据仓库环境中 。
ROWNUM
• 系统为每一个显示的记录自动地随着查询生成的行 号，并不是永久固定的，每次动态的重新生成的。 Select ROWNUM,empno,ename,job,sal from emp;
ROWID
数据伪列——ROWID
ROWID是表中数据行的唯一性标识，可以 用来定位行。
观察如下查询：
CREATE INDEX index1 ON scott.emp(ename,deptno);
CREATE INDEX index2 ON scott.emp(deptno,ename);
如果查询时WHERE子句中只包含ename字段， 那么只有第一个索引会提高查询速度，因为 ename出现在deptno之前。
例如，用户输入索引键1008，就反向为8001进行索引。 2011会作为1102进行索引。当进行反向键索引时这两个 号是非递增的。这意味着如果将其添加到叶子结点，可能 会在任意的叶子结点中进行。这样，新添加的叶子结点分 布会比较均匀。
4．基于函数的索引
用户在执行UPDATE、DELETE、SELECT操作 时，经常会使用基于函数的WHERE搜索条件。 如果索引是依据表的原始字段值建立的，那么基 于函数的搜索是用不上索引的，oracle将被迫进 行全表搜索，降低效率。
B树的索引结构是自动保持平衡的。 为一定范围内的查询提供了极好的性能，包括精确匹配和范
围查找。 插入、更新和删除的效率高。 性能不会随表的大小的增长而降低。
当数据检索范围过大（超过表10%）就不再适合用B树 索引。
假设查询编号为80的结点，先查根结点，确定查右分支， 找到80所在的叶子结点，并根据该索引条目中的ROWID 值找到要查询的记录位置。
生成代码 执行代码
使用企业管理器管理索引
索引管理页面
创建索引页面
1．输入索 引名称，选 择方案和表 空间
2．选择表
3．置入列 4．设置排序 和顺序
索引选项页面
创建索引
选择索引 类型
选择执行 选项
保存
删除索引
DROP INDEX语句删除指定索引。
【例】删除索引index_username： DROP INDEX index_username;
CREATE INDEX index_hire ON scott.emp(to_char(hiredate, 'yyyy-mmdd'));
如果对scott.emp表执行下面的查询，那么该 索引可以提高查询速度。
SELECT * FROM scott.emp WHERE to_char(hiredate,'yyyy-mm-dd')>'2002-5-1'
9.2.1 创建B树索引
B树索引是创建索引时的默认类型。
当用户为表创建主键约束时，系统将自动为该列 创建一个B树索引。
也可以使用CREATE INDEX命令创建B树索引。
例9.1 在scott.emp表的sal字段上创建一 个名为index_sal的B树索引，按字段值的 降序排列。
CREATE INDEX index_sal
9.1.2 索引的类型
常见的索引类型包括B树索引﹑位图索引﹑ 反向键索引﹑基于函数的索引﹑全局索引 和局部索引等。
1、B树索引
Oracle默认的索引类型。主要数据都集中在叶子结点。 各叶结点中包括索引列的值和数据表中对应的ROWID 具有如下优点：
B树中所有叶子结点基本处于同一深度，查询所花费的时间基 本相同。
PARTITION<partition_setting>]
9.2 创建索引
创建索引需要适当的权限。
用户在自己的方案中创建索引，应该具有 CREATE INDEX系统权限。
在其他用户的方案中创建索引，必须具有 CREATE ANY INDEX系统权限。
注意，如果一个列已经包含了索引那么无法 在该列上再创建索引。
AS SELECT_statement
[WITH CHECK OPTION | WITH READ ONLY]
当用户执行SELECT、UPDATE、DELETE操 作时，系统自动选择合适的索引来优化操作。
适合创建索引的字段应具备以下特征：
取值范围较大的字段。 null值比较多的字段。 经常作为查询或连接条件的字段。 经常需要排序的字段。
不适合建立索引的表或字段具备的特征：
较小的表。 经常更新的表。 不常作为查询条件或连接条件的字段。
9.5.1 修创改建视图和应用视当在图数时据强源制不创存建
视图
创建视图的语法如下：
为视图中的数
CREATE [OR REPLACE] [FOR据指C定标E新题的| 列
NOFORCE] VIEW
定义视图的select语句
[schema.]view_name[(column [ ,...n ] )]
Oracle 数据库
第9章 索引与视图的创建
本章要点
1
索引的定义与存储
2
索引的创建与应用
3
视图的概念与优点
4
创建与管理视图
35
利用视图更新数据源表
9.1.1 索引的概念
索引是建立在数据表之上的数据库模式对 象，其作用就像图书目录一样，可以帮助用 户快速查找需要的数据，提高SQL查询语 句的速度。
---------- --------------
10
ACCOUNTING
20
RESEARCH
ห้องสมุดไป่ตู้
30
SALES
40
OPERATIONS
LOC -------NEW YORK DALLAS CHICAGO BOSTON
每一条记录的ROWID都不会重复，即便表中所 有列的数据内容都重复了，ROWID也不重复。
2、位图索引
位图索引中不再记录ROWID和键值，而将每一个索引值作为 一列，用0和1表示该行中是否包含该索引值。
位图中的每个位对应一个ROWID。如位值为1，则意味着对应的 ROWID的行包含该索引键值。
位图的映射功能是将数据位的位置转化为实际的ROWID。
位图索引的行顺序与原表行顺序一致。如果给定表的关键起始和终止 ROWID，就可以在查询过程中对应计算出行的原始物理位置。
9.2.2 创建位图索引
表中某一个字段的唯一值的个数比较少（ 基数小）时，在该字段上建立位图索引比 较合适。
创建位图索引时，必须指定BITMAP关键字。
例9.4 在scott.emp表中的job字段上创建 位图索引bit_index。
CREATE BITMAP INDEX bit_index ON scott.emp(job) TABLESPACE users;
单击此按钮 确认删除索引
9.4 视图的概述
视图是一个虚拟表，其内容由查询定义。
逻辑的组织数据，可以像表一样操作 但是并不永久性的存数据
视图的形成：
9.4.2 视图的优点
1．简化数据操作
包含的数据较少 将对多个表的操作简化为对一个视图的操作
2．增强数据的安全性
可以将安全性控制到任意的数据子集
9.2.3 创建反向键索引
反向键索引本质也是一个B树索引，为了避 免“歪树”的产生，反向键索引对键值进行 反向存储。
创建反向键索引时必须指定关键字REVERSE。
例9.5 在scott.emp表中的empno字段上创 建反向索引re_index。
CREATE INDEX re_index ON scott.emp(empno) REVERSE;
索引是一个单独的物理存储结构,可以有自 己的存储空间，不必与相关联的表处在同一 个表空间中。
索引是由表中一列或多列值的集合和这些 值所在行的ROWID（相当于页码）组成。
增加知识点：数据伪列
数据伪列指的是不需要用户处理的列，由 oracle自行维护的列。
在oracle数据库中有两个数据伪列：
5．全局索引和局部索引
全局分区索引 对整个分区表建立索引，然后再由Oracle对索 引进行分区，各个分区之间不是相互独立的， 索引分区与分区表之间也不是一对一的关系。
5．全局索引和局部索引
全局非分区索引 对整个分区表建立索引，但不对索引进行分区 ，一个索引对应表的所有分区。
9.2 创建索引
3．定制数据
不同级别的用户可以看到不同用途的数据集
4．合并与分割数据 5．利用视图修改源表
9.5 创建与管理视图
创建视图需要的权限：
具有对视图所引用表的查询权限 用户在自己的方案中创建视图，应该具有
CREATE VIEW系统权限。 在其他用户的方案中创建视图，必须具有
CREATE ANY VIEW系统权限。
9.3 应用索引
Oracle中索引的应用是由优化器决定的， 优化器根据优化的结果自动选择合适的索 引使用。
Oracle对查询语句的执行过程包括：
解析 优化:找到执行用户查询的最佳途径，选择索引
• 数据表的大小 （表数据量大用索引） • 用户获取记录的多少 （查询记录占全表量小时用索
引）