第7章索引.docx
《第7章索引.docx》由会员分享，可在线阅读，更多相关《第7章索引.docx（40页珍藏版）》请在冰豆网上搜索。
第7章索引
【本章导读】
索引是一种能够对数据记录进行物理排序或逻辑排序的机制，对基于索引列的查询来说，其
查询效率得到极大的提升。
Oracle11g提供了多种类型的索引，DBA可以根据需求为数据
表的列创建合适类型的索引。
读者学习完本章以后，应该了解以下内容：
l索引的概述
l创建、修改、查看和删除索引
l创建、修改和删除聚集
7.1索引概述
用户对数据库最频繁的操作是进行数据查询。
一般情况下，数据库在进行查询操作时，
需要
对整个表进行数据搜索，当表中的数据量很大时，搜索数据就需要很长的时间，
这就造成了
服务器的资源浪费。
为了提高检索数据的能力，
Oracle数据库系统中引入了索引机制。
索引（Index）是一个单独的、物理的数据结构，在这个数据结构中包括表中一列或若干列
的值，以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合。
索引提供了数据库
中编排表中数据的内部方法。
索引依赖于数据库的表，作为表的一个组成部分，
一旦创建后，
由数据库系统自身进行维护。
一个表的存储是由两部分组成的，
一部分用来存放表的数据页
面，另一部用来分存放索引页面，索引就存放在索引页面上。
通常，索引页面相对于数据页
面来说小得多。
当进行数据检索时，
系统先搜索索引页面，从中找到所需数据的指针，
再直
接通过指针从数据页面中读取数据。
从某种程度上可以把数据库看作一本书，
把索引看作书
的目录，通过目录查找书中的信息，显然比没有目录的书更方便、快捷。
在关系数据中，一个行的物理位置无关紧要，
除非数据库需要找到它。
为了能找到数据，表
中的每一行均用一个RowID来标识，RowID告诉数据库这一行的准确位置（指出行所在的
文件、该文件中的块、该块的行地址）。
索引结构表没有传统的
OracleRowID
，不过，其主
键起一个逻辑RowID的作用。
Oracle数据库支持几种类型索引，如
B-树索引、反向索引、降序索引、位图索引、函数索
引和interMedia全文索引等，可以用于提高数据库的性能。
1．B-树索引
B-树索引是最常见的索引结构，默认建立的索引就是这种类型的索引。
B-树索引在检索拥有
很多不同的值的列时提供了最好的性能。
当取出的行数占总行数比例较小时，
B-树索引比全
表检索提供了更有效的方法。
但当检查的范围超过表的
10%时，就不能提高取回数据的性
能了。
B-树索引是基于二叉树的，由分支块和叶子块组成。
在树结构中，位于最底层的块被
称为叶子块，包含每个被索引列的值和行所对应的
RowID。
在叶节点的上面是分支块，用
来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图
7-1所示。
图7-1典型的B-树索引布局图
这个树中的最底层块称为叶子块，其中分别包含各个索引键及一个
RowID（指向所索引的
行）。
叶子块之上的内部块称为分支块，用于在结构中实现导航。
索引的叶子块实际上又构成了一个双向链表，
执行索引区间扫描（值的有序扫描）也很容易，
找到第一个值之后，不需要再在索引结构中导航，
而只需根据需要，通过叶子块向前或向后
扫描就可以了。
所以要满足诸如以下的谓词条件将相当简单。
WHERExBETWEEN20AND30
Oracle发现第一个最小值大于或等于20的索引叶子块，然后水平地遍历叶子块链表，直到
命中一个大于
30的值。
B-树索引中不存在非唯一条目。
在一个非唯一索引中，
Oracle
会把
RowID
作为一个额外的
列追加到键上，使得键唯一；而在一个唯一索引中，根据定义的唯一性，
Oracle
不会再向
索引键增加
RowID
。
B*树的特点之一是，所有叶子块都应该在树的同一层上。
B-树是一个绝佳的通用索引机制，
无论是大表还是小表都很适用，
随着底层块较小地增长，
获取数据的性能只会稍有恶化
（或
者根本不会恶化）。
2．反向索引
反向索引是B-树索引的一个分支，它的设计是为了运用在某些特定的环境下。
Oracle推出
它的主要目的就是为了降低在并行服务器环境下索引叶子块的争用。
当B-树索引中有一列
是由递增的序列号产生时，那么这些索引信息基本上分布在同一个叶子块中；当用户修改或
访问相似的列时，索引块很容易产生争用。
反向索引中的索引键将会被分布到各个索引块中，
减少了争用。
反向索引反转了索引键中每列的字节。
3．降序索引
降序索引是B-树的另一个衍生物，它的变化就是列在索引中的存储方式从升序变成了降序，
在某些场合下降序索引将会起作用。
创建降序索引时，Oracle已经把数据都按降序排好了。
4．位图索引
位图索引主要用于决策支持系统或静态数据，不支持行级锁定。
位图索引最好用于低cardi
nality列（即列的唯一值除以行数为一个很小的值，接近零），例如有一个“性别”列，列的
值有“Male、”“Female”、“Null”3种，但一共有300万条记录，那么3/3000000约等于0，
这种情况下最适合用位图索引。
位图索引可以是简单的单列，也可以是连接的多列，但在实践中绝大多数是简单的。
在这些
列上多位图索引可以与AND或OR操作符结合使用。
位图索引使用位图作为键值，对于表
中的每一数据行位图包含了TRUE
（1）、FALSE（0）或NULL值。
位图索引的位图存放在
B-树结构的叶子块中。
B-树结构使查找位图非常方便和快速。
另外，位图以压缩格式存放，
因此占用的磁盘空间比B-树索引要小得多。
5．函数索引
基于函数的索引有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序
的逻辑上提高了查询性能。
使用基于函数的索引有几个先决条件：
l必须拥有QUERYREWRITE（本用户方案下）或GLOBALQUERYREWRITE（其他用户方案下）权限；
l必须使用基于成本的优化器，基于规则的优化器将被忽略；
l必须设置以下两个系统参数：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通过ALTERSYSTEMSET、ALTERSESSIONSET在系统级或线程级设置，也可以通
过在init.ora中添加实现。
7.2创建索引
在Oracle11g中，用户可以通过Oracle提供的SQLDeveloper和PL/SQL等方式来创建索引。
用户必须具有CREATEANYINDEX的系统权限才可以创建索引。
7.2.1使用SQLDeveloper创建索引
在SQLDeveloper中右击Indexes选项，从快捷菜单中选择
“NewIndex，”开始创建索引，如图
7-2所
示。
在打开的“CreateIndex对话框”中，指定索引所在的用户方案和索引名，并指定需要创建索引的表；单击“+”按钮，从“ColumnNameorExpression”下拉列表框内选择需要创建索引的列名，并指定排序类型；其余采用默认值，如图7-3所示。
图7-2选择新建索引
单击“确定”按钮，完成索引的创建操作。
7.2.2使用PL/SQL创建索引
图7-3
配置索引信息
使用
PL/SQL创建索引的基本语法格式为：
CREATE[BITMAP]INDEX[
用户方案
.]<
索引名
>
ON[用户方案
.]<
表名>|<
视图>（列名
ASC|DESC[
，⋯
]|
函数名（列名））
[REVERSE]
[CLUSTER[用户方案.]聚簇]
[INITRANSn]
[MAXTRANSn]
[PCTFREEn]
[STORAGEstorage]
[TABLESPACEtablespace]
[NOSORT]
参数说明：
（1）索引名必须符合PL/SQL标识的规则；
（2）BITMAP指定创建位图索引；
（3）REVERSE指定创建反向索引；
（4）ASC指定创建升序索引，为默认设置，DESC指定创建降序索引；
（5）使用函数名指定创建函数索引；
（6）CLUSTER指定一个聚集（HASHCLUSTER不能创建索引）；
（7）INITRANS、MAXTRANS指定初始和最大事务入口数；
（8）TABLESPACE指定索引所在的表空间名；
（9）STORAGE用于设置存储参数；
（10）PCTFREE指定索引数据块空闲空间的百分比（不能指定PCTUSED）；
（11）NOSORT指定能否排序（存储时就已按升序排序，所以指出不再排序）。
创建索引后要分析索引才能起作用。
ANYLYZEINDEX索引名COMPUTESTATISTICS;
【例7-1】建立关于产品表的产品名称列的唯一索引。
CREATEUNIQUEINDEXSCOTT.idx_PRIDNAME
ONSCOTT.PRODUCT（PRODNAME）;
【例7-2】建立关于产品表的产品类别编号列的索引。
CREATEINDEXSCOTT.idx_CATEGORYID
ONSCOTT.PRODUCT（CATEGORYID）;
【例7-3】建立关于订单明细表的订单编号列和产品编号列的复合索引。
CREATEINDEXSCOTT.idx_OID_PID
ONSCOTT.ORDERDETAILS（ORDERID,PRODUCTID）;
7.3修改索引
在Oracle11g中，用户可以通过Oracle提供的SQLDeveloper和PL/SQL等方式来修改索引。
用户必须具有ALTERANYINDEX的系统权限才可以修改索引。
7.3.1使用
SQLDeveloper
修改索引
在SQLDeveloper中右击Indexes选项，从快捷菜单中选择“Edit，开”始修改索引，如图
在打开的“EditIndex对话框”中，用户可以更改索引的配置信息，如索引的排序类型等，如图
7-4
所示。
7-5所示。
图7-4选择修改索引图7-5修改索引信息
单击“确定”按钮，完成索引的修改操作。
7.3.2使用PL/SQL修改索引
使用PL/SQL修改索引的基本语法格式为：
ALTERINDEX[用户方案.]<索引名>
[INITRANSn]
[MAXTRANSn]
[PCTFREEn]
[STORAGEstorage]
[TABLESPACEtablespace]
[NOSORT]
⋯;
其中各个参数的使用与CREATEINDEX命令的参数使用方法相同。
例如：
修改索引，重新调整INITRANS和MAXTRANS参数的值。
ALTERINDEXSCOTT.idx_PRODNAME
INITRANS5
MAXTRANS10;
7.4查看索引
当需要查看索引信息时，可以使用Oracle提供的视图，具体如表7-1所示。
表7-1Oracle提供的查看索引信息的视图
视图说明
DBA_INDEXES
ALL_INDEXES
USER_INDEXESDBA_IND_COLUMNSALL_IND_COLUMNSUSER_IND_COLUMNSDBA_IND_EXPRESSIONSALL_IND_EXPRESSIONSUSER_IND_EXPRESSIONS
INDEX_STATS
INDEX_HISTOGRAM
VSOBJECT_USAGE
例如：
使用下面的命令可以查看索引
DBA视图，描述了数据库中所有表上的索引
这些视图描述了表中索引的列信息
这些视图描述了基于函数的索引的表达式
存储来自最后一个ANALYZEINDEX⋯VALIDATESTRUCTURE语句的
信息
存储来自最后的ANALYZEINDEX⋯VALIDATESTRUCTURE语句的信
息
包含ALTERINDEX⋯MONITORINGUSAGE函数生成的索引使用信息
IDX_PRODNAME的信息。
SELECTOWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME
FROMSYS.DBA_INDEXES
WHEREINDEX_NAME='IDX_PRODNAME';
以SYSDBA身份执行上述查询，得到的输出结果如下所示：
OWNERINDEX_NAMETABLE_OWNERTABLE_NAME
--------------------------------------------------------------------------------------------------
SCOTTIDX_PRODNAMESCOTT
PRODUCT
1rowsselected
7.5删除索引
在Oracle11g中，用户可以通过Oracle提供的SQLDeveloper和PL/SQL等方式来删除索引。
用户必须具有DROPANYINDEX的系统权限才可以删除索引。
7.5.1使用
SQLDeveloper
删除索引
7-6
在SQLDeveloper
所示。
中，展开
Indexes
选项，右击需要删除的索引，从快捷菜单中选择
“Drop项”，如图
图7-6选择删除索引操作
在打开的Drop对话框中，在Prompts（提示）选项卡中显示了待删除索引的所属用户方案和索引名称，
并提示用户是否需要删除该索引，如图7-7所示。
图7-7
删除索引
单击“应用”按钮，将打开如图
7-8
所示的
Confirmation
对话框，以提示用户该索引已经被删除。
单击“确
定”按钮，完成索引的删除操作。
图7-8确认删除索引
7.5.2使用PL/SQL删除索引
利用DROPINDEX命令可以删除一个或多个当前数据库中的索引。
其语句格式如下：
DROPINDEX'[用户方案]<表名>.<索引名>'[,...n]
【例
7-4】删除产品表中为产品名称创建的唯一索引
idx_PRODNAME。
DROPINDEX'SCOTT.PRODUCT.PRODNAME'
DROPINDEX
命令不能删除由
CREATETABLE或
ALTERTABLE
命令创建的
PRIMARY
KEY或UNIQUE约束索引，也不能删除系统表中的索引。
7.6聚集
聚集（Cluster）用于将不同表中的数据存储到相同的物理数据块中，适用于多表数据需要频繁进行连接查询的情况。
多表数据存储在相同的数据块中后，在对表进行连接查询时，所需读取的物理块数减少，从而提高查询性能。
需要注意的是，对聚集中单表的查询和对聚集中表数据进行修改或插入等事务操作时，性能将受到影响。
每一个聚集存储其中各聚集表的数据并维护该聚集索引，以便对数据进行排序。
聚集索引中的索引列，称为聚集键（ClusterKey），其决定了数据在物理上的存储位置，这些列不经常被更新。
聚集键通常是其聚集表中与某个表的主键相关联的表的外键。
聚集分为索引聚集和哈希聚集两种，其操作方法不尽相同。
1．索引聚集
索引聚集是保存数据表的一种可选方案。
索引聚集在同一个数据块中将多个不同表的相关行存储在一起，从而改善相关操作的存取时间。
共享公共列的表可以聚集在该列的周围，从而加速对这些行的存取。
索引
聚集有利于聚集数据上的连接，因为所有的数据在一个I/O操作中被检索。
数据插入必须基于聚集键，因而聚集可能会降低执行INSERT语句的性能。
另外，索引聚集跨越多个块，
需要扫描更多的数据。
2．哈希聚集
哈希聚集类似于索引聚集，但它使用哈希函数而非索引来引用聚集键。
哈希聚集在同一数据块中将相关的
行存储在一起，依据是这些行的哈希函数结果。
在创建哈希聚集时，Oracle为聚集的数据段分配初始数量
的存储空间。
在基于聚集键的等值查询方面，使用哈希聚集可以发挥其高效的性能。
与索引聚集一样，在哈希表上执行
INSERT语句时，执行性能将下降。
7.6.1创建聚集
在创建聚集之前，建议先估算聚集的空间需求，以更好地规划数据库空间的使用，提升系统的性能。
创建聚集可以分为3个步骤：
l创建聚集本身，它是存储聚集表的逻辑结构；
l创建聚集中的表；
l
创建聚集键上的索引。
1．创建聚集本身
使用
PL/SQL语句创建聚集的基本语法格式如下：
CREATECLUSTER[用户方案.]<聚集>（列数据类型[,
[PCTFREEn|PCTUSEDn|INITRANSn|MAXTRANSn|
|SIZEn
|TABLESPACE<表空间>
⋯）n]
⋯
|{INDEX|HASHKEYSn[HASHIS<表达式>]}
]⋯;
参数说明：
（1）用户方案指定了包含聚集的用户方案名，默认时在当前用户方案中创建聚集；
（2）PCTUSED指定Oracle用于决定额外可以增加到聚集的数据块中的限制；
（3）PCTFREE指定为了扩展，每个聚集数据块中保留的空间；
（4）INITRANS指定为聚集的数据块分配的并发更新事务的初始数量，默认为聚集的表空间的INITRANS
值和2之间的较大者；
（5）MAXTRANS指定并发更新事务的最大数量；
（6）SIZE指定空间的数量，用于存储有相同聚集键或哈希值的所有行；
（7）TABLESPACE指定聚集所在的表空间；
（8）INDEX指定创建索引聚集；
（9）HASHKEYS指定创建哈希聚集，指定哈希聚集的哈希值数量（最小值为2）；
（10）HASHIS指定一个用作哈希聚集的哈希函数的表达式，该表达式的结果必须为正值，不能为常数，默认使用Oracle内部哈希函数。
例如：
创建聚集PRODUCTTYPE，将产品类型表和产品表中的产品类型编号存储到聚集中。
CREATECLUSTERPRODUCTTYPE
（
CATEGORYIDCHAR（4）
）
PCTUSED70
PCTFREE10
SIZE512
TABLESPACEUSERS
STORAGE（INITIAL100K
NEXT50K）;
2．创建聚集中的表
使用带
CLUSTER选项的
CREATETABLE命令可以在聚集中创建表，当前用户必须具有
CREATETABLE或
CREATEANYTABLE的系统权限，但不需要表空间限额或
UNLIMITEDTABLESPACE
系统权限。
创建聚集表的基本格式如下：
CREATETABLE[用户方案.]<表>
（⋯）
CLUSTER<聚集>（列）
例如：
创建产品类型表CATEGORY和产品表PRODUCT作为聚集PRODUCTTYPE的聚集表。
CREATETABLECATEGORY
（
CATEGORYIDCHAR（4）,
CATEGORYNAMEVARCHAR2（20）
）
CLUSTERPRODUCTTYPE（CATEGORYID）;
CREATETABLEPRODUCT
（
PRODIDCHAR（10）,
CATEGORYIDCHAR（4）,
PRODNAMEVARCHAR2（100）,
DESCRIPTIONVARCHAR2（500）
）
CLUSTERPRODUCTTYPE（CATEGORYID）;
3．创建聚集键上的索引
使用CREATEINDEX语句可以创建聚集索引，当前用户必须拥有
系统权限，同时还需要拥有包含该聚集索引的表空间限额或有
CREATEINDEX或CREATEANYINDEX
UNLIMITEDTABLESPACE系统权限。
例如：
为聚集
PRODUCTTYPE创建聚集键。
CREATEINDEXidx_PRODUCTTYPE
ONCLUSTERPRODUCTTYPE;
创建聚集索引后，就可以使用聚集了。
【例
7-5】创建哈希聚集
SUPPLYPRODUCT。
--建立使用数据库默认HASH函数的聚集
CREATECLUSTERSUPPLYPRODUCT
（
SUPPIDCHAR（4）
）
PCTUSED70
PCTFREE10
SIZE10M
TABLESPACEUSERS
STORAGE（INITIAL100K
NEXT50K）
HASHISSUPP_IDHASHKEYS150;
--创建聚集表
CREATETABLESUPPLIER
（
SUPPIDCHAR（4）,
SUPPNAMEVARCHAR2（50）,
LICENSEVARCHAR2（20）,
ADDRESSVARCHAR2（100）,
ZIPCHAR（6）,
PHONEVARCHAR2（20）,
SITEVARCHAR2（100）,
EMAILVARCHAR2（100）,
CONSTRAINTpk_SUPPLIER_SUPPIDPRIMARYKE
升级会员 