第12章Oracle模式对象的管理

数据库原理与应用（Oracle版）人民邮电出版社2第12章Oracle模式对象的管理12.1索引12.2索引组织表12.3分区表与分区索引12.4外部表12.5临时表12.6簇与簇表12.7管理视图12.8管理序列12.9管理同义词12.1索引什么是索引汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词北京科技大学通信工程系3在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。数据库中的索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。12.1索引索引的建立和维护为了改善查询性能，可以建立索引。索引是根据表中的一列或若干列按照一定顺序建立的列值，与表中记录行之间具有一一对应关系的辅助表结构。索引属于物理存储的路径概念，而不是用户使用的逻辑概念。建立在多个列上的索引被称为复合索引。有两种重要的索引：聚集索引（ClusteredIndex）非聚集索引（Non-clusteredIndex）。北京科技大学通信工程系412.1索引索引的建立和维护聚集索引类似于按姓氏排列数据的电话簿。由于聚集索引规定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。北京科技大学通信工程系512.1索引索引的建立和维护非聚集索引与书本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息则按另一种顺序存储（这可以由聚集索引规定）。与使用书中索引的方式相似，DBMS在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。北京科技大学通信工程系612.1索引索引的优缺点优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新北京科技大学通信工程系712.1索引在Oracle中，可以创建多种类型的索引，以适应各种表的特点。常用的索引类型有B*树索引、位图索引、反向键索引、基于函数的索引、簇索引、全局和局部索引等。其中，簇索引专门用于簇表的索引。主要介绍B*树索引、位图索引、反向键索引和基于函数的索引的工作原理及其这些索引的创建。北京科技大学通信工程系812.1索引B*树索引如果表包含的数据非常多，并且经常在WHERE子句中引用某列或某几列（列的重复值很少），则应该基于该列或该几列建立B*树索引。B*树索引由根节点、分支节点和叶子节点3部分组。北京科技大学通信工程系912.1索引B*树索引B*树索引适用于那些具有高基数的字段，即大部分值都不相同的字段。创建B*树索引的语法如下：CREATE[UNIQUE]INDEX[模式名.]索引名ON[模式名.]表名(字段名[ASC|DESC][,字段名[ASC|DESC]]…)[TABLESPACE表空间名][PCTFREE][INITRANS][MAXTRANS][STORAGECLAUSE][LOGGING|NOLOGGING]北京科技大学通信工程系1012.1索引B*树索引【例12.1】如果在WHERE子句中要经常引用某列或某几列，应该基于这些列建立B*树索引。如果经常执行类似于SELECT*FROMstudentWHEREStuName='王成'的语句，可以基于StuName列建立B*树索引。示例如下：SQLCREATEINDEXind_stunameONstudent(StuName)PCTFREE30TABLESPACEMyTS;人民邮电出版社1112.1索引位图索引在位图索引中，使用一个比特位来对应一条记录的ROWID，并通过位图索引映射函数来实现比特位到ROWID的映射。每个索引条目都有一个键值和RowID组成。创建位图索引的语法如下：CREATEBITMAPINDEX[模式名.]索引名ON[模式名.]表名(字段名[ASC|DESC][,字段名[ASC|DESC]]…)[TABLESPACE表空间名][PCTFREE][INITRANS][MAXTRANS][STORAGECLAUSE][LOGGING|NOLOGGING]人民邮电出版社1212.1索引位图索引【例12.2】给学生表student的性别列sex创建位图索引，该列的取值只有“男”和“女”。SQLCREATEBITMAPINDEXSexBIndexONstudent(sex);人民邮电出版社1312.1索引反向索引反向索引是索引列值按照相反顺序存放的索引。B*树索引既适用于范围查询，也适用于等值查询，而反向索引只适用于等值查询。在创建反向索引时，只需要在CREATEINDEX语句中指定关键字REVERSE。【例12.3】给学生表student的学号列StuNo创建反向索引。SQLCREATEUNIQUEINDEXStuNoIndexONstudent(StuNo)REVERSEPCTFREE30TABLESPACEMyTS;人民邮电出版社1412.1索引基于函数的索引函数索引是基于函数或表达式所建立的索引。【例12.4】因为在SQL语句中需要经常引用函数LOWER(StuName)，所以为了加快数据访问速度，应基于该函数建立函数索引，示例如下：SQLCREATEINDEXfunction_ind_stunameONstudent(LOWER(StuName));人民邮电出版社1512.1索引修改索引修改索引是使用ALTERINDEX命令完成的。一般情况下，修改索引是由索引所有者完成的，如果要以其他用户身份修改索引，则要求该用户必须具有ALTERANYINDEX系统权限或在相应表上的INDEX对象权限。1）修改索引段存储参数SQLALTERINDEXind_stunameSTORAGE(NEXT200KMAXEXTENTS50);2）分配和释放索引空间SQLALTERINDEXind_stunameALLOCATEEXTENT(SIZE1M);人民邮电出版社1612.1索引修改索引3）重建索引SQLALTERINDEXind_stunameREBUILD;4）联机重建索引SQLALTERINDEXind_stunameREBUILDONLINE;5）合并索引SQLALTERINDEXind_stunameCOALESCE;人民邮电出版社1712.1索引删除索引删除索引是使用DROPINDEX命令完成的。一般情况下，删除索引是由索引所有者完成的，如果以其他用户身份删除索引，则要求该用户必须具有DROPANYINDEX系统权限或在相应表上的INDEX对象权限。示例如下：DROPINDEXind_stuname;人民邮电出版社1812.2索引组织表索引组织表是Oracle提供的一种特殊的表，它将表的数据和索引数据存储在一起，即以B*树索引的方式来组织表中的数据。索引组织表可以极大地提高查询效率，其典型的应用类似于字典的情形，主要用于搜索一些有意义的信息。要创建索引组织表，必须在CREATETABLE语句中显示地指定ORGANIZATIONINDEX子句。其次，在索引组织表中必须建立一个主键约束。人民邮电出版社1912.2索引组织表【例12.5】下面的语句就创建了一个索引组织表department。SQLCREATETABLEdepartment(deptnoNUMBER(4)PRIMARYKEY,deptnameVARCHAR2(32),locationVARCHAR2(64))ORGANIZATIONINDEXTABLESPACEusers;人民邮电出版社2012.3分区表与分区索引在当前的企业应用中，需要处理的数据量可以达到几十到几百GB，甚至TB级。管理VLDB（VeryLargeDatabase）数据库时，企业必须面对大数据量对存储和性能的影响，提高对这些巨型数据库和巨型表的读写速度。分区表（PartitionedTable）和分区索引（PartitionedIndex）正是为了满足VLDB系统的需求而设计的。通过使用分区表，允许将一张大表的数据分布到多个表分区段；通过使用分区索引，允许将一个大索引的数据分布到多个索引分区段。使用分区特征时，不同分区彼此独立，从而提高了表的可用性和性能。人民邮电出版社2112.3分区表与分区索引分区的概念在Oracle中，当把一个大表分成若干小表时，可以规定一些范围供数据库使用。这些称作分区（Partition）的小表比大表的管理更加简单。分区是指将巨型的表或索引分割成相对较小的、可独立管理的部分，这些独立的部分称为原来表或索引的分区。人民邮电出版社2212.3分区表与分区索引建立分区表在Oracle11g数据库中，根据对表或索引的分区方法可以创建4种类型的分区表：范围（Range）分区、列表（List）分区、散列（Hash）分区、组合分区。每种分区表都有自己的特点，在创建分区表时，应当根据表应用情况选择合理的分区类型。人民邮电出版社2312.3分区表与分区索引1）范围分区范围分区基于分区字段值的范围将数据映射到所建立的分区上。这是最通用的分区类型，经常使用日期字段来分区。人民邮电出版社24SQLCREATETABLEOrderInfo_Range(orderNoNUMBER(10),orderBalanceNUMBER(10,2)DEFAULT0,--订单总价orderTimeDATE)PARTITIONBYRANGE(orderTime)(PARTITIONjdlVALUESLESSTHAN(TO_DATE('01-04-2012','DD/MM/YYYY')),PARTITIONjd2VALUESLESSTHAN(TO_DATE('01-07-2012','DD/MM/YYYY')),PARTITIONjd3VALUESLESSTHAN(TO_DATE('01-10-2012','DD/MM/YYYY')),PARTITIONjd4VALUESLESSTHAN(TO_DATE('01-01-2013','DD/MM/YYYY')));12.3分区表与分区索引2）列表分区列表分区可以控制如何将行映射到分区中去。可以在每个分区的字段上定义离散的值，这与范围分区和Hash分区是不同的。【例12.7】列表分区的细节可以通过下面的例子来说明。在这个例子中，可以使用籍贯对学生数据分区。这意味着将数据按照地理位置分区。SQLCREATETABLEStudent_List(StuNoNUMBER(10),StuNameVARCHAR2(64),ProvinceVARCHAR2(16))PARTITIONBYLIST(Province)(PARTITIONplVALUES('北京','天津','山西','内蒙','河北'),PARTITIONp2VALUES('湖南','湖北','广东','福建','上海'),PARTITIONp3VALUES('河南','黑龙江','吉林','辽宁','西安'));人民邮电出版社2512.3分区表与分区索引3）Hash分区Hash分区利用Oracle自带的Hash函数计算分区字段，并依据计算结果来对数据进行分区。Hash分区能够很容易对数据进行分区，因为语法很简单，很容易实现。【例12.8】使用Hash分区创建产品信息。SQLCREATETABLEProduct_Hash(productidNUMBER(6),descriptionVARCHAR