L10_Oracle数据库编程_QUST.pdf

《L10_Oracle数据库编程_QUST.pdf》由会员分享，可在线阅读，更多相关《L10_Oracle数据库编程_QUST.pdf（50页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、索引概述基本概念 索引是一种树状结构，可以通过该结构迅速访问表中的数据 是一种对象 使用指针加快记录访问速度（用以提高 SQL语句执行的性能）由数据库自动使用和维护 在逻辑上和物理上都独立于表的数据基本概念 索引与书籍中的目录类似 索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据比较 未建立索引时 从第一条记录开始顺序读取 I/O消耗 内存占用比较 有时连续读取的效率较高 表规模较小 需要全表数据进行数值运算（如求和）一定范围内的较多随机读取 考虑 较少的随机读取 返回稀疏的记录特点 通过索引提高查询效率 可为表中的单个列建立索引 也可为一组列（索引项）建立索引 逻辑上和物理

2、上都与相关的表的数据无关 Oracle自动维护索引 索引的使用对用户是透明的特点 索引在数据库中会占用一定的存储空间 索引也会增加系统的负担，进行影响系统的性能 在对数据进行插入、更改和删除操作时，为使索引与数据保持一致，需要对索引进行相应维护 对索引的维护是需要花费时间 为防止使用索引后反而降低性能，应遵循一些基本原则示例 一个销售情况表（SALES）TOPIC列没有特定的顺序 ROWID表示记录的物理存储位置 查询TOPIC为PEN的记录示例 在TOPIC列上建立索引 Oracle对全表进行一次搜索 将每条记录的TOPIC值按升序排列 然后构建索引条目，即（TOPIC值，ROWID值）存储

3、到索引段中 查询TOPIC为PEN的记录存储结构和分类B树 由根、中间节点和叶子节点三部分构成 所有叶子节点都具有相同的深度（平衡）叶子节点是一个双向链表 构建过程查找过程B树ROWID ROWID就是唯一标志记录物理位置的一个ID Oracle的物理扩展ROWID有18位，分别用AZ、az、09、+、/共64个字符表示聚集索引 ClusteredIndex，也称聚簇索引 将数据按照索引项的顺序进行物理排序enoenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CAC

4、S聚集索引聚集索引 SELECT*FROMemployeeWHEREeno=E08聚集索引适用于包含大量非重复值的列运算符（如 BETWEEN、=、和=）使用 ORDERBY或 GROUPBY子句经常被用作连接（JOIN）的列返回大型结果集的查询不适用 频繁更改的列 宽键，即若干列或若干大型列的组合非聚集索引 非聚集索引与图书后边的术语表类似 数据存储在一个地方，术语表存储在另一个地方 数据并不按术语表的顺序存放，但术语表中的每个词在书中都有确切的位置非聚集索引非聚集索引 包含大量非重复值的列 不返回大型结果集的查询 经常作为查询条件使用的列 经常作为连接和分组条件的列比较聚集索引 对表的物理

5、数据按列进行重新排序 一个表只能有一个聚集索引 叶级节点存放数据非聚集索引数据不按非聚集索引关键字值的顺序排序和存储一个表可有多个非聚集索引叶级节点不存放数据 每个索引包含关键字值以及一个（或多个）行定位器，指向该关键字值对应的数据唯一索引 确保索引列不包含重复值 在组合唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的 例如，如果在last_name、first_name和middle_initial列的组合上创建了唯一索引full_name，则该表中任何两个人都不可以具有完全相同的名字 聚集索引和非聚集索引都可以是唯一的唯一索引 如果必须要实施唯一性来确保数据的完整性，则应在列上创建

6、UNIQUE约束或PRIMARYKEY约束，而不要创建唯一索引 例如，如果限制身份证号码（sid）列的取值不重复，则可在sid列上创建UNIQUE约束 实际上，当在表上创建PRIMARYKEY约束或UNIQUE约束时，Oracle自动在这些列上创建唯一索引组合索引 组合索引（复合索引）是一个包含多个字段的索引 组合索引应该根据实际的查询语句来建立 建立组合索引时应将最有可能在where子句中使用的列，并且也是选择性最高的字段放在第一位 对于组合索引在索引中添加额外的列不会显著的提高选择性，添加额外的列反而会造成更高的消耗反向索引 被索引的每个数据列中的数据都是反向存储的，但仍然保持原来数据列的

7、次序 可以高效地打散正常的索引键值在索引叶块中的分布位置，降低索引叶子块的争用，有时会提高系统性能 通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引位图索引对于一些基数很小的列来说，一般的B树索引并不能显著提高查询的速度，位图索引适合建立在有大量重复值的列上 基数指某个列可能拥有的不重复值的个数 例如：性别列的基数为2（只有男和女）当创建位图索引时，Oracle会扫描整张表，并为索引列的每个取值建立一个位图 对表中每一行使用一位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值位图索引位图索引 位图索引的叶子结点存的不是ROWID而是每一个键值的位图比较函数索引

8、对包含有列的函数或表达式创建索引经常访问一些函数或表达式时，可以将其存储在索引中 当下次访问时，由于该值已经计算出来了 可提高那些在WHERE子句中包含该函数或表达式的查询操作的速度函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择 当函数或表达式的结果不确定时采用B树索引 当函数或表达式的结果是固定的几个值时采用位图索引函数索引在SALES表中，TOPIC列的值如果采用首字母大写的方式存储不忽略大小写 SELECT*FROMSALESWHERETOPIC=TEE;/*没有结果*/现在忽略大小写 SELECT*FROMSALESWHEREUPPER(

9、TOPIC)=TEE;由于不是直接查询TOPIC列，即使在TOPIC列上创建了索引也无法使用函数索引 由于函数索引存储了预先计算过的值，查询时不需要对每条记录都再计算一次WHERE条件，从而可以提高查询的速度 在函数索引中可以使用各种算术运算符、PL/SQL函数和内置SQL函数，如LEN、TRIM、SUBSTR等 聚集函数（如SUM、MAX、MIN、AVG等）不能使用操作索引创建索引 CREATEUNIQUE|BITMAPINDEX方案名.索引名ON方案名.表名 表别名(ASC|DESC)UNIQUE 表示唯一索引，默认情况下，不使用该选项 BITMAP 表示创建位图索引，默认情况下，不使用该

10、选项示例 为Student表的Sno列创建索引CREATEINDEXSno_idx ONStuent(Sno)为Employee表的FirstName和LastName列创建复合索引CREATEINDEXName_idx ONEmployee(FirstName,LastName)为Employee表的Ename创建基于小写函数索引 createindexemp_lower_ix onemp(lower(ename)示例 一个汽车数据表CarInfo，其中包含大量的低基数列，比如汽车颜色color、汽车品牌make、汽车型号model、出厂日期year等，建立位图索引 Createbitmap

11、 indexcar_bmp_index onCarInfo(color,make,model,year)其它操作重建索引SQL ALTER INDEX item_index REBUILD;删除索引SQL DROP INDEX item_index;改名索引SQL ALTER INDEX item_index rename to book_index;结束B树B树和B+树 B树 能自动保持与数据文件大小适应的索引层次 能对所使用的存储空间进行管理，使每个块处于全满半满之间 B+树 特殊的B树，内部结点只存储索引块 叶结点用指针连接B+树特点：特点：n=3最小码数：最小码数：2最小指针数：最小指

12、针数：2（指向数据）（指向数据）指向码值为指向码值为31的记录指向码值为的记录指向码值为37的记录指向码值为的记录指向码值为41的记录指向顺序集上的下一个叶结点典型的叶结点的记录指向顺序集上的下一个叶结点典型的叶结点指向码值指向码值K 23指向码值指向码值23K31 指向码值指向码值31K43指向码值指向码值K95典型的非叶结点典型的非叶结点特点：特点：n=3最小码数：最小码数：1最小指针数：最小指针数：2参数n:每个块存放码值的最大个数.B+树指针和码的数量最大指针数最大码数最小指针数最大指针数最大码数最小指针数(指向数据指向数据)最小码数内部结点最小码数内部结点n+1n(n+1)/2(n+

13、1)/2-1叶结点叶结点n+1n(n+1)/2(n+1)/2根结点根结点n+1n11B+树中的查找查找码为查找码为37的记录的记录13 37313743找到找到37码值为码值为37的记录的记录I/O数：数：4若把第一、第二层结点保存在缓冲区若把第一、第二层结点保存在缓冲区I/O数：数：2B+树的插入三种情况：三种情况：叶结点满内部结点满动作否否把码放在叶结点合适的位置是否叶结点满内部结点满动作否否把码放在叶结点合适的位置是否1.分裂叶结点分裂叶结点2.把码的中间值放在内部结点合适的位置把码的中间值放在内部结点合适的位置3.左侧的叶结点包括小于中间值的码左侧的叶结点包括小于中间值的码4.右侧的叶

14、结点包括大于或等于中间值的码是是右侧的叶结点包括大于或等于中间值的码是是1.分裂叶节点分裂叶节点2.左侧的叶结点包括小于中间值的码左侧的叶结点包括小于中间值的码3.右侧的叶结点包括右侧的叶结点包括大于或等于大于或等于中间值的码中间值的码4.分裂内部结点分裂内部结点5.左侧的内部结点包括小于中间值的码左侧的内部结点包括小于中间值的码6.右侧的内部结点包括右侧的内部结点包括大于大于中间值的码中间值的码7.把码的中间值放在上一级内部结点合适的位置把码的中间值放在上一级内部结点合适的位置如果上一级内部结点已满，则继续分裂上一级结点如果上一级内部结点已满，则继续分裂上一级结点B+树的插入插入码插入码40

15、（属于情况（属于情况3）401.分裂叶结点分裂叶结点2.码中间值上提分裂内部结点码中间值上提分裂内部结点3.码中间值上提码中间值上提B+树的删除三种情况：三种情况：叶结点小于最小充满度内部结点小于最小充满度动作否否叶结点小于最小充满度内部结点小于最小充满度动作否否1.删除叶结点中的码删除叶结点中的码2.对码重新排序对码重新排序3.如果码在上一级内部结点出现，则用下一个码替换是否如果码在上一级内部结点出现，则用下一个码替换是否1.组合叶结点和它的兄弟结点组合叶结点和它的兄弟结点2.相应更改内部结点以反映这种改变是是相应更改内部结点以反映这种改变是是1.组合叶结点和它的兄弟结点组合叶结点和它的兄弟

16、结点2.相应更改内部结点以反映这种改变相应更改内部结点以反映这种改变3.组合内部结点和它的兄弟结点组合内部结点和它的兄弟结点继续组合内部结点，直到找到一个满足充满度的结点或者到达根结点继续组合内部结点，直到找到一个满足充满度的结点或者到达根结点B+树的删除删除码删除码7（属于情况（属于情况2）551.组合叶结点组合叶结点2.更改内部结点以反映这种改变更改内部结点以反映这种改变B+树的删除删除码删除码11（属于情况（属于情况3，需要递归更改内部结点），需要递归更改内部结点）1.组合叶结点组合叶结点5132.更改内部结点以反映这种改变（需要递归）更改内部结点以反映这种改变（需要递归）233143B+树的效率 查询效率 插入效率 实际情况下，因为插入导致重组B树的情况很少 删除效率 一种可选实现：不对删除做修复