《Oracle优化器模式与Oracle索引优化规则》由会员分享，可在线阅读，更多相关《Oracle优化器模式与Oracle索引优化规则（39页珍藏版）》请在金锄头文库上搜索。
1、Oracle优化器介绍 Oracle索引介绍 SQL优化规则介绍 索引优化规则介绍 Oracle优化器模式 Oracle在执行一个SQL之前 首先要分析一下语句的执行计划 然后再按执行计划去执行 分析语句的执行计划的工作是由优化器 Optimizer 来完成的 Oracle优化器 Optimizer 是Oracle在执行SQL之前分析语句的工具 Oracle的优化器有两种优化方式 基于规则的优化方式 Rule BasedOptimization 简称为RBO 和基于代价的优化方式 Cost BasedOptimization 简称为CBO RBO方式 优化器在分析SQL语句时 所遵循的是Ora
2、cle内部预定的一些规则 比如我们常见的 当一个where子句中的一列有索引时去走索引 CBO方式 CBO是看语句的代价 这里的代价主要指Cpu和内存 优化器在判断是否用这种方式时 主要参照的是表及索引的统计信息 统计信息给出表的大小 有少行 每行的长度等信息 这些统计信息起初在库内是没有的 是你在做analyze后才出现的 在Oracle8及以后的版本 Oracle列推荐用CBO的方式 优化器的优化模式 OptermizerMode 包括Rule Choose Firstrows Allrows这四种方式 Rule 不用多说 即走基于规则的方式 rboChoose 指的是当一个表或或索引有统
3、计信息 则走CBO的方式 如果表或索引没统计信息 表又不是特别的小 而且相应的列有索引时 那么就走索引 走RBO的方式 FirstRows 它与Choose方式是类似的 所不同的是当一个表有统计信息时 它将是以最快的方式返回查询的最先的几行 从总体上减少了响应时间 对于排序分页页显示这种查询尤其适用 AllRows 也就是我们所说的Cost的方式 当一个表有统计信息时 它将以最快的方式返回表的所有的行 从总体上提高查询的吞吐量 没有统计信息则走基于规则的方式 查看缺省的Oracle优化器 SQL showparametersoptimizer mode 可以在init文件中对整个instanc
4、e的所有会话设置 也可以单独对某个会话设置 SQL ALTERSESSIONSEToptimizer mode FIRST ROWS 查看统计信息 1 selecttt table name tt num rows tt blocks tt empty blocks tt avg row lenfromdba tablesttwherett owner SCOTT 2 selectttt index name ttt num rows ttt distinct keys ttt avg leaf blocks per key ttt clustering factorfromdba index
5、estttwherettt owner SCOTT 人工进行统计 对某一个用户下的所有表和索引执行统计分析 executedbms stats gather schema stats ownname SCOTT cascade true 对单个表执行统计分析 EXECUTEdbms stats gather table stats ownname SCOTT tabname EMP estimate percent 50 cascade true 从OracleDatabase10g开始 Oracle在建库后就默认创建了一个名为GATHER STATS JOB的定时任务 用于自动收集CBO的统
6、计信息 调用DBMS STATS GATHER DATABASE STATS JOB PROC收集统计信息 默认情况下在工作日晚上10 00 6 00和周末全天开启 一起运行的还有另外一个Job AUTO SPACE ADVISOR JOB 可以通过以下查询这个JOB的运行情况 select fromDba Scheduler JobswhereJOB NAME GATHER STATS JOB 关闭自动统计功能 SQL execBMS SCHEDULER DISABLE GATHER STATS JOB Oracle索引介绍 Oracle索引简介 索引是建立在表的一列或多个列上的辅助对象 目
7、的是加快访问表中的数据 加快查询 索引由根节点 分支节点和叶子节点组成 上级索引块包含下级索引块的索引数据 叶节点包含索引数据和确定行实际位置的rowid 查询DBA INDEXES视图可得到表中所有索引的列表 注意只能通过USER INDEXES的方法来检索模式 schema 的索引 访问USER IND COLUMNS视图可得到一个给定表中被索引的特定列 通过每个行的ROWID 索引Oracle提供了访问单行数据的能力 ROWID其实就是直接指向单独行的线路图 索引分类 逻辑上 单列索引 多列索引 唯一索引 非惟一索引 物理上 B Tree索引 反向索引 位图索引 单列索引和复合索引 单列
8、索引是基于单个列所建立的索引 多列索引是基于两列或多列所建立的索引 单列索引Createindexemp ind1onemp ename 复合索引Createindexemp ind2onemp ename job 惟一索引和非惟一索引 惟一索引是索引列值不能重复的索引 非惟一索引是索引列值可以重复的索引 无论是惟一索引还是非惟一索引 索引列都允许NULL B tree索引 B Tree索引是最常见的索引结构 默认建立的索引就是这种类型的索引 B Tree索引在检索高基数数据列 高基数数据列是指该列有很多不同的值 时提供了最好的性能 当取出的行数占总行数比例较小时B Tree索引比全表检索提供
9、了更有效的方法 但当检查的范围超过表的10 时就不能提高取回数据的性能 B Tree索引是基于二叉树的 由分支块 branchblock 和叶块 leafblock 组成 在树结构中 位于最底层底块被称为叶块 包含每个被索引列的值和行所对应的rowid 在叶节点的上面是分支块 用来导航结构 包含了索引列 关键字 范围和另一索引块的地址 创建索引 1 createindexSTUDENT IND nameonSTUDENT NAME tablespaceUSERS 2 createindexSTUDENT IND nameonSTUDENT NAME AGE tablespaceUSERS B
10、tree索引 B tree索引 假设我们要找索引中值为80的行 从索引树的最上层入口开始 定位到大于等于50 然后往左找 找到第2个分支块 定位为75 100 最后再定位到叶块上 找到80所对应的rowid 然后根据rowid去读取数据块获取数据 如果查询条件是范围选择的 比如wherecolumn 20andcolumn 80 那么会先定位到第一个包含20的叶块 然后横向查找其他的叶块 直到找到包含80的块为止 不用每次都从入口进去再重新定位 反向索引 反向索引是B Tree索引的一个分支 它的设计是为了运用在某些特定的环境下的 Oracle推出它的主要目的就是为了降低在并行服务器 Orac
11、leParallelServer 环境下索引叶块的争用 当B Tree索引中有一列是由递增的序列号产生的话 那么这些索引信息基本上分布在同一个叶块 当用户修改或访问相似的列时 索引块很容易产生争用 反向索引中的索引码将会被分布到各个索引块中 减少了争用 反向索引反转了索引码中每列的字节 建立反向索引 createindexreversed ind emponemp Order ID reverse 反向索引 如果有一个名称为order id的列值在某行上是12345 oracle就将它颠倒为54321 这样连续的几个数不会在同一个页块里 不过反向索引又一个缺点就是不能在所有使用常规索引的地方使
12、用 在范围搜索中其不能被使用 例如 wherecolumn value 因为在索引的叶块中索引码没有分类 所以不能通过搜索相邻叶块完成区域扫描 注意 只有索引中的值是逆向的 表中的值保持不变 位图索引 位图索引主要用于决策支持系统或静态数据 不支持行级锁定 位图索引最好用于低cardinality列 即列的唯一值除以行数为一个很小的值 接近零 例如又一个 性别 列 列值有 Male Female Null 等3种 但一共有300万条记录 那么3 3000000约等于0 这种情况下最适合用位图索引 创建位图索引 createbitmapindexinx bitmap emponemp sex 位
13、图索引的格式 行值12345678910Male1000000011Female0111001100Null0000110000 位图索引 如果搜索wheregender Male 要统计性别是 Male 的列行数的话 Oracle很快就能从位图中找到共3行即第1 9 10行是符合条件的 如果要搜索wheregender Male orgender Female 的列的行数的话 也很容易从位图中找到共8行即1 2 3 4 7 8 9 10行是符合条件的 如果要搜索表的值的话 那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块 聚簇 聚簇是根据码值找到数据的物理存储
14、位置 从而达到快速检索数据的目的 聚簇索引的顺序就是数据的物理存储顺序 叶节点就是数据节点 非聚簇索引的顺序与数据物理排列顺序无关 叶节点仍然是索引节点 只不过有一个指针指向对应的数据块 一个表最多只能有一个聚簇索引 使用Oracle聚簇索引 在Oracle当中 聚簇不是索引的组织形式 而是表的组织形式 多用于表之间的连接字段 例 dept deptno dnma eloc 和表emp empno ename job mgr deptno 常在deptno上进行连接 可以针对deptno字段建立聚簇 然后建立基于该聚簇的索引 并让两个表都使用上该聚簇 Oracle聚簇索引 CREATECLUS
15、TERpersonnel department numberNUMBER 2 SIZE512STORAGE INITIAL100KNEXT50K CREATETABLEemp empnoNUMBERPRIMARYKEY enameVARCHAR2 10 NOTNULLCHECK ename UPPER ename jobVARCHAR2 9 mgrNUMBER commNUMBER 9 0 DEFAULTNULL deptnoNUMBER 2 NOTNULL CLUSTERpersonnel deptno CREATETABLEdept deptnoNUMBER 2 dnameVARCHAR2
16、 9 locVARCHAR2 9 CLUSTERpersonnel deptno CREATEINDEXidx personnelONCLUSTERpersonnel Oracle聚簇索引 这样可以让两个表同时用上聚簇索引 保证两个表的记录按照depno值尽量存放到同一个物理块当中 使用索引的一些规则 1 索引对大表最有用 不要在小表上加索引 2 为每个表中的主码指定一个唯一索引 3 索引对于那些频繁出现在SQL命令中的where子句中的列最有用 不管这些列在选择中用来限定行还是为了表连接 4 当一个属性中存在很多不同的值时 可以使用索引 Oracle建议当一个属性中有少于30个不同值时 索引不是很有用 当属性中有100或更多不同值时索引就很明显地有用了 相似地 只有当使用索引进行查询的结果不超过文件中所有记录总数的20 时 使用索引才有帮助 5 检查你的DBMS对索引的限制 即便要在每个表允许的索引个数上 许多系统不超过16个索引而且限制每个索引键值的大小 对一个表创建的索引数一般不超过5个 7 对于包含空值的属性建立索引时要小心 在很多DBMS里有空值的行不能在索引中作为参照 8
