第三章 SQL 调整 3. 稳定执行计划 xV&c ) l>}
第三章 SQL 调整 5.索引
x0||' 0I0
|l Mc6C >)ed ha*W] 索引 @ ;7Ht Z` ORACLE 9i 中有六种索引:二叉树索引,压缩的二叉树索引,位图索引,基于函数的索引,反向索引,索引组织表; +c tJV> ,!QtVi A7 二叉树索引 b !T-{Ns6 · 二叉树索引将索引栏位值和ROWID一起保存树状结构中,适用于只存取表中总记录的5%以下的查询; \u*[mrX_B: · 出现在SQL的WHERE条件中,集势高的栏位适于当作索引栏位; ;v 5Jps2^] · 随着基表数据的不断增加,索引块会不断分裂以保持二叉树的平衡,树的层次(从根结点访问到叶结点要经过的数据块数,DBA_INDEXES.BLEVEL)也不断增加,层次大于4的索引宜重建; \ t]_UNGyW · 基表数据删除时,索引项也随之删除,但叶块上的空间并不能被重用,除非该叶块上所有的索引项都被删除,当删除项占所有项超过20%时,这个索引也需要重建(ANALYZE INDEX … VALIDATE STRUCTURE;分析索引后查看index_stats.del_lf_rows_len 和 index_stats.lf_rows_len 可以知道被删除的索引项占用的空间和所有叶行占用的空间); W?ge lu ] · 有三种重建索引的方法: iCy $ rC 1. 先删除再重建;这种方法耗费最多的资源,是早期版本的唯一方法; q> m [vvt" 2. ALTER INDEX … REBUILD; 这种方法高效快速,但需要额外的磁盘空间;用这种方法可以指定许多选项如ONLINE(在线重建可减少锁争用), TABLESPACE(移动段到其它表空间), COMPUTE STATISTICS(统计), PARALLEL(并行), NOLOGGING(尽可能少地产生日志); {j %7/T{ 3. ALTER INDEX … COALESCE; 这种方法快速,无需额外空间,锁争用也少,缺点是选项少。 ( H -kW T @ ; Xa&* 压缩的二叉树索引 vP ^ V 3 · 压缩的二叉树索引对重复的索引键值只保存一次,后跟所有的有这个键值行的ROWID; aK-- D2@}i · 有两种方法创建: s,~g| I\ 1. CREATE INDEX … COMPRESS; L/)Q 1Mm 2. 先创建普通索引,再更改:CREATE INDEX …; ALTER INDEX … COMPRESS; J#?z/3 v( BO'7c 1FU 位图索引 Sw` +4 4 · 位图索引适于创建在数据相对稳定的表的低集势栏位上; s/ Q8(sF5 · 位图索引针对每一个键值创建一个二进制位图,位图中的每位对应表中的一行,1表示这一行是当前键值,0表示这一行为其它键值; KdC '#$ · 与二叉树索引相比,位图索引占的空间很少,如果WHERE条件中出现同一表的多个位图索引栏位时,无论是AND或是OR操作,都能用位图合并操作快速地定位到ROWID; j>#ywh*A · 当基表上有DML操作时,整个位图都会被锁住,但是,每一个DML操作,位图只需更新一次; =2 1m| 8c · 相关参数: !wH' dsriD 1. SORT_AREA_SIZE 创建索引排序位图栏位和ROWID时会用到; _J>!K'Dz 2. PGA_AGGREGATE_TARGET 创建位图索引以及位图合并时会用到; z~# d@c \ 3. CREATE_BITMAP_AREA_SIZE, BITMAP_MERGE_AREA_SIZE 这是8i的两个与位图创建和合并有关参数,在9i建议不再使用。 ?7TuE!!M KU ;J2Kt 基于函数的索引 o T "7O 5v · 当栏位以函数表达式的形式出现在WHERE条件中,基于这些栏位的索引不会被优化器用到,除非是基于这些栏位的函数索引; = ,Z5F`d 4 · 要创建基于函数的索引需要QUERY REWRITE 或 GLOBAL QUERY REWRITE系统权限,有CREATE ANY INDEX权限是不行的; >9t+lr1 · 要想优化器使用基于函数的索引,OPTIMIZER_MODE必须设成某种CBO模式,QUERY_REWRITE_ENABLED须设成TRUE(这点从试验中无法证明,设成FALSE似乎也能用到函数索引)。 Q$ A;Fk}- l fqsoIn; 反向索引 Nh1 , w · 反向索引是建立在索引栏位值的反向值上的,可以使相临的记录在索引段里变得离散,可以减少某些DML操作引发的块竞争; X 1}U · 反向索引多建立在用序列号生成的栏位上,这样的栏位在普通索引中容易形成更多的层(设计过一些试验,但一直未观察到这种现象); / @0 · 有两种产生方法:CREATE INDEX … REVERSE; ALTER INDEX … REVERSE; lV gin5 4Q · 反向索引适于WHERE中的等于或不等于的比较,对于范围查找(>,< between)是无能为力的。 6NG QU%H d Og?P5&C"9D 索引组织表 J/\^ 3r CB · 前面五种索引的基表数据存储是随机的,这种表称为堆表; spT I hZ · 索引组织表的数据存储在二叉树索引中,所以,如果通过主键来存取数据,索引组织表能更快地返回数据,因为不需要先取索引块再取数据块,可以直接从索引中返回数据; \{ff7_mLo · 创建索引组织表时必须指定一个主键栏位,用这个作为索引; C W?R7A/ · 索引组织表上不能建唯一约束,也不能将索引组织表建立在簇上面; )V9 wU1. · 索引组织表的语法要点: o Fp4* Ø ORGANIZATION INDEX 指明当前表是索引组织表; z W* Z Ø PCTTHRESHOD 后跟一个0到50的数,默认值为50,指明容纳一行数据可用使块空间的百分比; P F0AU T Ø INCLUDING 后跟一个字段,如果数据行的长度超过了PCTTHRESHOLD指定的可用空间,从这个字段之后将数据行分为两段,后面的部分放入溢出段中; rM)#}eZ K! Ø OVERFLOW TABLESPACE 指定溢出段所在的表空间; DVw 04ay% Ø MAPPING TABLE 当在索引组织表上创建位图索引时创建关联的映射表;堆表位图索引的每个位对应到表的一个ROWID,索引组织表的ROWID会随着索引的分裂而发生改变,如果和堆表一样处理,位图索引很容易就失效或者维护成本很高,映射表就是为解决这个问题而引入的,映射表存放索引组织表的ROWID和逻辑行间的对应关系,索引位图中的位对应到逻辑行;dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率,这个值大于30时推荐重建位图索引;一个索引组织表只有一个映射表。 %( #kJZ · 通过dba_tables.iot_name, dba_tables.iot_type 可以查看到索引组织表的溢出表段,映射表段; ] ".SW5b_ · 索引组织表的相关段的段名都是由系统生成的,这些段名的共同特性是SYS_IOT_XXX_YYY, XXX in (TOP 索引段, OVER 溢出表段, MAP 映射表段),YYY是索引组织表的OBJECT_ID。 0E/ 16@ 6= oC|']r 6 标识未使用过的索引 il kN 3J · 索引创建后是否使用是由优化器来控制的,某些索引可能不会使用到,这样的索引不仅加重了DML操作的负担,也占用空间;可以用下面的方法找出这样的索引,然后删除; s d XZsQ w Ø 找出某个或某些被怀疑的索引; Kd Lj 1 T Ø ALTER INDEX index_name MONITORING USAGE; 9bu 1A x1M Ø 在数据库经历一定时间的活动后再执行: ALTER INDEX index_name NOMONITORING USAGE; ] u jXPK=t Ø 查询v$object_usage.index_name, v$object_usage.used,可以得知被监控的索引在这段时间内是否被使用到。 S= g E'"LT Mby V_A`r_ }W5 ~ 89" 第三章 SQL 调整 6.分区 f- ~ ] ~ A,(D - -\V;Gw8mD · 分区表是将大表的数据分成称为分区的许多小的子集,9i提供四种分区方法:范围分区,列表分区,哈希分区和混合分区; "rR$2`v" · 范围分区是根椐分区键的不同取值范围来划分子集的,关键字RANGE, VALUES LESS THAN; dW#?{n- H< · 列表分区是根椐分区键的一些离散的取值来划分子集的,关键字LIST, VALUES; 9[Xe|5?c · 哈希分区是应用哈希算法将分区键对应到某个子集中去,关键字HASH, PARTITIONS; 'Ad|* ~ · 混合分区只能有两层,第一层是范围分区,第二层可以是列表分区或者哈希分区; dyu T-. 2 · 范围分区和列表分区中,如果插入记录的分区键没有对应的容纳分区,会产生ORA-14400; GF gh{ '| · update操作如果会使记录从一个分区迁移到另一个分区,且分区表的ROW MOVEMENT属性是DISABLE,会产ORA-14402; M F s W · 分区表上的索引有两大类:普通的二叉树索引,分区索引,下面讲到的都是分区索引: 3Qu Ft~@ @ · 按索引分区和表分区间的对应关系可以分为局部索引和全局索引; UszR. Z Ø 局部索引的索引分区和表分区间是一一对应的,全局索引则相反; BnIZ+ fg= Ø 局部索引的分区方法可以用上面提到四种的任何一种,全局索引的分区方法只有范围分区(而且最高的分区必须用MAXVALUE来定义); psB9~EU&Q Ø ORACLE自动维护局部索引的分区,当表分区被合并,分裂或删除时,关联的索引分区也会被合并,分裂或删除;对分区表执行管理操作时会使其上的全局索引失效; eLyaTOZadu Ø 建在分区表的位图索引必须是局部分区索引; r+;AE N48 Ø ORACLE推荐尽可能地使用局部索引; F+::UWK A · 按索引栏位和分区键间的关系分为前缀索引和非前缀索引; H & =3rkX Ø 前缀索引最前面的栏位是分区键栏位,非前缀索引相反; KfMaV U=4P · 在这两种分类方法的四种组合中,只有三种有效(局部前缀索引,局部非前缀索引,全局前缀索引),不存在全局非前缀索引; j GKasI` · 分区表给CBO带来很多选项,如分区排除,并行分区连接等。 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/119501/viewspace-609376/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/119501/viewspace-609376/