1、Oracle DBA 成长日记6.索引索引时 oracle 数据库不可缺少的一部分，它提供了快速访问数据的特殊能力，从而不再需要对整个表进行扫描。6.1 索引概述6.1.1oracle 索引类型 B*树索引反向键索引（reverse key）位图索引（bitmap）位图索引（bitmap join）基于函数的索引（function _based）域索引（domain ）仅当 SQL 查询语言中包含 where 子句的时候，才会使用索引。Oracle 对 where 子句进行评估，然后决定是否需要使用给定的索引提供对感兴趣的数据进行快速访问。1. B*树索引B*树是默认的索引类型，它是数值的有序
2、列表。索引的顶层是根块 root block，这个根块指向下一层的块，称之为支块 branch block，这些分支块可以是多级深度的，并且最终指向叶块 leaf block。叶块包含了实际的数据值以及与这些数据值相关联的行号 rowid。使用 B*树索引的好处： B*树特别适合于范围查询和包含精确条件的查询 B*树索引可以自动地平衡 过多的 B*树索引可能影响 DML 操作，但单个的 B*树索引对 DML 操作的影响是很小的 B*树索引操作可以随数据量的增长而很好地进行扩展。访问路径 描述唯一值扫描 返回值不超过一行。扫描的类型仅可能是主键或唯一键范围扫描 分为有界扫描和无界扫描两种。有界扫
3、描开始和结束于索引中的特定位置；无界扫描开始于索引中的特定位置，而且对索引中的叶节点进行读操作。可能返回多个相同的行。此种扫描方式不能使用反向键降序范围扫描除了返回的行必须按照降序排列之外，其他都与范围扫描相同快速全扫描 当查询的所有列出现在索引中，并且至少有一个列的值为 NOT NULL时，可以使用快速全扫描。它不能消除排序过程，因为索引键不能给返回的行定序。它使用多个 I/O 模块，并且可以并行化地进行扫描。快速全扫描比索引全扫描更快索引全扫描 对索引进行一次全面扫描。可以消除排序操作，但不能消除表的访问操作。它使用单独的 I/O 模块，可能比快速全扫描要慢索引跳扫 索引跳扫是跳过一个或多
4、个索引列的合成索引。当列是索引扫描的一部分时，则需要使用该列索引连接 是两个或多个索引的 hash 连接，这些索引包含在 SQL 查询语句中所引用的所有表列。因此可以消除表的访问操作，但不能消除排序操作2.反向键索引除了 rowid 之外，反向键索引将颠倒索引中所有列的顺序。反向键索引通过使用含有相邻排列的列值索引，可以解决特定的性能问题（如重复序号）。这些排列的数字将分步在叶块的一个小集合中，当执行并访问操作的时候，将会导致块拥堵。另外频繁地执行插入/删除操作也可能会使索引在水平方向上造成“不平衡” 。反向键索引通过颠倒索引列的值来解决这个问题，从而将这些值遍布到更多的块中。反向键的访问路径
5、仅限于一下两种： 唯一值扫描 索引全扫描3.位图索引位图所用用来改善基于有很少不同列值的行的访问速度。位图索引包含索引值和表中相关的 rowid 之间的位图关系，位图中的每一位都指出这个索引值是否在给定的rowid 中出现。在相同的列中创建位图的索引比创建 B*树的索引要小得多。位图索引可以提供比B*树索引更强的性能，特别是在数据仓库环境中（写操作较少，读操作较多的情况下）。位图索引在 OLTP 应用程序中执行性能较差，因为典型的 OLTP 有大量的 DML 操作。这是因为 oracle 对位图索引中行的加锁方式：在一个行的行为可能导致对位图索引中的多个行加锁。位图索引对相同类型的查询最有效。
6、含有范围比较的查询不使用与使用位图索引。与 B*树索引不同的是，位图索引可以含有 NULL 值的行，这对某些类型的 SQL 语句将是非常有帮助的。位图索引的局限性： 仅能用于 oracle 的企业版中 分区表的位图索引必须使本地索引 不能将位图索引创建为索引组织表的辅助索引，除非该索引组织表有映射表 不能将位图索引定义为唯一索引4.位图连接索引位图连接索引时连接一个表的主键列和其他表的一个或多个外部键列的位图索引。当使用位图连接索引时，可以改善这些表之间的连接性能。位图连接索引存在着某些约束： 每次只能修改位图连接索引表中的一列，这意味着当执行并行 DML 行为时，将会阻塞其他的会话。 不能在
7、索引组织表中或临时表中创建位图连接索引 索引中的每一列必须是其中一个围表中的某一列。因此该索引将表现为雪花或星星模式 索引中围表的每一列必须是主键或该列必须有唯一约束 位图连接索引中不能有将表与自身想连接 应用于标准位图索引的所有约束都同样可以应用于位图连接索引5.基于函数的索引基于函数的索引可以在包含一个或多个表列的函数上创建索引。当创建基于函数的索引时，计算函数的值并且将其结果存储在索引中。当 where 子句包含函数时，基于这个函数的索引可以给索引访问带来好处。基于函数的索引存在着一些约束： 必须使用一个确定的函数定义基于函数的索引，也就是说函数仅返回一个值。 西部使用返回可重复值的函数
8、定义基于函数的索引。若函数返回的可变值是无效的。 可以对基于函数的索引进行分区，但是对于基于函数的全局分区索引来说，分区键不能是索引所基于的函数。 函数必须使用括号来定义，而无论该函数实际上是否含有参数，如果不使用圆括号，可可能会理解为列名。 索引基于的函数不能含有聚集函数。为了使用基于函数的索引，必须将 query_rewrite_enabled 参数设置为 true。如果删除了基于函数的索引所以来的函数，则将会把这个基于函数的索引标记为 disabled，并且任何试图使用该索引的查询都会失败。6.域索引域索引指的是使用 oracle 扩展框架所创建的索引。使用 create index t
9、ype 定义用于存储索引值和用于返回匹配行的机制。6.1.2 杂项索引特性1.索引属性属性 描述 默认值Pctfree 在已存在的行中指出一个块需要保留多少空间用来更新。当一个块中所使用的空间达到或超过 pctfree 值，则从空闲表中删除该块并不再使用这个块。10Pctused 如果块的使用已经降低到 pctused 之下，则因为达到或超过pctfree 阈值而从空闲表中删除的块将重新添加到表中40Initrans 定义在每个数据块中分配的并发事务项的初始数目。其中最大值取决于块大小。如果有大量的并发操作，则可以改变这个值，而数据库也可以按照需要动态地添加事务项2Maxtrans 指出允许更
10、新给定数据块的并发事务的最大数目 基于块大小的变量Storage clause为索引确定实际物理存储特性Storage 子句的一些常用值的参考：参数 定义Initial 索引的第一个扩展区的大小Next 索引的下一个扩展区的大小Minextents 应该分配给索引的扩展区的最小数目Maxextents 应该分配给索引的扩展区的最大数目Pctincrease 在每个扩展区操作之后，next 参数因该增加的比例Freelists 分配给索引的空闲表数目Freelist group 分配给索引的空闲表组的数目Buff _pool 应该给索引分配的 oracle 数据库缓冲池2.建立联机索引标准的索引
11、创建操作会使得所有的 DML 操作等待直到索引创建完成之后才去执行。使用 create index 命令的 online 子句来解决这个问题，并且允许使用对基表产生最少中断的 DML 来创建索引。2. 并行处理在 oracle 中，通过使用 parallel 子句或通过发出 alter session force parallel ddl 命令来是的索引的创建操作可以并行地执行。当使用 parallel 时，可以选择直接指定并行度，也可以让数据库确定并行度。若是让数据库确定并行度，则数据库将会使用可用于实例中的 CPU 数目诚意数据库参数 parallel-threads_per_cpu 的值
12、来作为计算出的并行度。Alter index rebuild 命令只能并行地执行分分区索引的重新创建。可以使用 alter index rebuild partition 命令来并行地重新创建索引的独立分区。并行处理的一些约束： 不支持联机重建索引的并行处理 不能并行扫描无分区索引4.分区索引分区索引有两种基本类型：本地分区索引和全局分区索引。本地分区索引本地分区索引总是将底层的表分成相等的分区。索引分区键、分区数目以及存储在这些分区中的值与底层基表的分区几乎相同。可以给本地分区索引的分区赋予名字，也可以有系统默认取名。全局分区索引全局分区索引允许以跟底层表分区策略不同的方式对索引进行分区操作
13、或子分区操作。当创建全局分区索引时，必须把创建的索引所在列的名称作为分区键的前缀。例如,在给定表中的列 column_01 和 column_02 上创建索引，饿索引的分区名称必须以column_01 和 column_02 作为前缀。在 oracle10g 中，全局分区索引可以是范围分区索引，也可以是 hash 分区索引。当创建全局索引的时候，需要定义所有的单独的分区和分区范围。5.重建索引重建索引的原因： 需要将索引移动到新的表空间中去 索引中的块存储变为无效 需要修改索引中的一条属性重建索引的方式有两种。第一种：先删除索引，然后再次创建索引。这会产生一些问题，因为需要完整的create
14、index 语句，同时在索引创建过程中，将会对索引上的 DML 加锁。Oracle 还提供了另外一种方法：使用 alter index rebuild 命令。在创建索引之前，alter index rebuild 命令不会首先删除这个索引。在正常执行过程中，会在创建索引所在的表中放置互斥锁，这将会阻塞与该索引相关联的表中的所有 DML.通过使用 create index rebuild online 命令重新创建联机索引来可避免加锁情况的发生。6.合并索引7.分析索引分析索引的方法： 通过 analyze 命令来分析索引Analyze index ix_my_tb_01 compute sta
15、tistics; 通过 analyze 命令来分析相关联的表，以确保对所有的索引进行分析。Analyze table my_tb compute statistics for all indexed columns; 通过 dbms_stats 命令来分析索引Exex dbms_stats.gather_index_stats(y_schema,ix_my_tb_01,degree=4); 通过 dbms_stats 命令来对表进行分析，将 cascade 属性设置为 true 以确保对索引进行分析。Exec dbms_stats.gather_table_stats(y_schema,ix_
16、my_tb_01,degree=4,cascade=true);8.索引键压缩当压缩索引时，数据库消除了重复出现的键列值，这样可以减少索引的大小。如果是唯一索引，则可以压缩索引中除一列以外的其他所有列。如果不是唯一索引，则可以压缩所有的索引列。不能压缩紧含有一列的位图索引、分区唯一性索引和唯一性索引。9.反向键索引Oracle 允许创建以颠倒字节顺序存储的索引。使用 create index 或 alter index rebuild命令的 reverse 子句创建反向键索引。不能将位图索引或者索引组织表创建为反向键索引。10.无序索引的创建Create index 通过消除与索引创建相关联的
17、排序操作来加速索引的创建。如果数据已经按顺序加载到表中，则使用 create index 命令的 nosort 子句来加速索引的创建。 Nosort 和 reverse 不能同时使用 Nosort 子句不支持簇操作、分区索引或者位图索引 不能再索引组织表的辅助索引中使用 nosort 子句11.Nologging 子句Nologging 可以减少在直接加载操作时重做日志的产生。12.检测索引的使用方法如果确定是否正在使用某个索引，可以通过使用 monitoring usage 子句实现。当启用检测时，可以通过检查 V$OBJECT_USAGE 视图中的 USED 列确定是否正在使用该索引。6.
18、2 创建索引6.2.1 安全性需求在自己的模式中创建索引并创建以自己模式存在的对象，仅仅需要在分配给索引的表空间中有足够的空间限额。如果不在自己的模式中，则必须对要索引的对象拥有index 的权限或 create index 的权限，同时需要有足够的空间限额，或授予 unlimited tablespace 的权限。为了创建基于函数的索引，需要对将用于基于函数索引的函数拥有 execute 的权限。若希望在自己的模式中创建基于函数的索引，则需要拥有 query rewrite 的系统权限。若在另一个模式中创建索引，则需要拥有 global query rewrite 的权限。为了全局地使用基于
19、函数的索引，需要将 query_rewrite_enabled 设置为 true，并且将 query_rewrite_integrity 设置为 trusted。6.2.2 使用 create index 的例子1.五分区索引的例子create index ix_mytab_01 on mytab(column_01);create index ix_mytab_02 on mytab(column_01,column_02);create index ix_mytab_03 on mytab(column_01,column_02)tablespace my_index storage(in
20、itial 10k next 20k pctfree 10);create index ix_mytab_03 on mytab(column_01,column_02)tablespace my_index storage(initial 10k next 20k pctfree 10)compute statistics;/使用 compute statistic 子句来计算统计值create index ix_mytab_03 on mytab(column_01,column_02)tablespace my_index compress storage(initial 10k nex
21、t 20k pctfree 10);/还可以使用 compress 键来对索引进行压缩（可以对唯一性索引进行压缩）2.创建位图索引create bitmap index bit_mytab_01 on my_tab(col_two)tablespace my_tbs;3.使用 oracle 并行查询来创建索引并行查询可以给表上的操作带来好处，则可以在创建表的时候，定义数据库应该考虑使用的并行度。使用 parallel 时，数据库可以并行地执行索引建立操作。create index ix_parts on parts(id) tablespace parts_tablespace paralle
22、l 4;4.分区索引的例子oracle 允许创建两种分区索引：本地分区索引和全局分区索引创建本地分区索引本地索引将底层的表分为相等的分区。因此不需要为本地索引定义分区范围信息。需要定义分区名称、表空间名称和分区属性。create index ix_part_my_tab_01 my_tab(col_1,col_2,col_3) local (partition tbs_part_01 tablespace part_tbs_01,partition tbs_part_02 tablespace part_tbs_02,partition tbs_part_03 tablespace part_
23、tbs_03);如果使用的是 hash 分区表，则可用 store in 参数来创建索引：create index ix_part_my_tab_01 on on my_tab (col_01,col_02,col_03) local store in(part_tbs_01,part_tbs_02,part_tbs_03);创建全局分区索引create table store_sales(store_id number(6),invoice_number number,time_id date,invoice_sale_amt number(10,2)partition by range(t
24、ime_id)(partition sales_q1_2003values less than (to_date(01-apr-2003,DD-MON-YYYY),partition sales_q2_2003values less than (to_date(01-jul-2003,DD-MON-YYYY),partition sales_q3_2003values less than (to_date(01-oct-2003,DD-MON-YYYY),partition sales_q4_2003values less than (to_date(01-jan-2003,DD-MON-YY
25、YY),partition sales_overflow values less than (maxvalue);5.基于函数索引的例子在 SQL 查询中，需要使用特定的函数来定位数据。create table emp_info(last_name varchar2(40),first_name varchar2(40),salary number);select last_name,first_name,salary from emp_info where upper(last_name) like THOMPSON;就可以创建如下索引：create index fb_upper_last_
26、name_emp on emp_info (upper(last_name);当完成了对索引的创建后，要确保对该索引进行分析。6.3 修改索引6.3.1 安全性模式在自己的模式中，需要足够的表空间限额和数据库连接能力。在另外一个模式中，需要有 alter any index 的系统权限。若要使用 monitoring usage 子句，则必须在自己的模式中。6.3.2 修改无分区索引的例子1.重建五分区索引alter index pk_mytable_01 rebuild online tablespace new_pk_tbs;alter index pk_mytable_01 rebuil
27、d online tablespace new_pk_tbs pctfree 20 storage(initial 1m next 1m) parallel;/先移动索引，然后重新设置一些存储参数，并且利用 oracle 并行处理加以加速索引的重建。2.修改索引属性alter index id_test_01 storage(freelist 5);若使用的是本地管理表空间，则可以改变的属性会很少。3. 启动并行查询alter index id_test_01 parallel;4.重命名索引alter index id_test_01 rename to ix_test_01;5.改变日志记
28、录子句alter index ix_test_01 nologging;alter index ix_test_01 logging;6.改变检测属性alter index ix_test_01 monitoring usage;/将索引的检测属性设置为 monitoringalter index ix_test_01 nomonitoring usage;/将索引检测属性设置为 nomonitoring7.合并索引alter index ix_test_01 coalesce;8.从索引中释放为使用的空间deallocate unusage space 子句可以用来从索引中删除不需要的空间。
29、alter index ix_test_01 deallocate unused;也可以给 oracle 执行需要保留的空间大小：alter index ix_test_01 deallocate unused keep 10m;6.3.3 使用修改分区索引命令的例子重新创建已存在索引的分区：alter index ix_part_my_tab_01 rebuild partition tbs_part_01;重新创建分区索引的子分区的例子：alter index sales_ix rebuild subpartition pq42;2.合并分区或子分区将 tbs_part_01 分区合并到
30、ix_part_my_tab_01 分区索引中去：alter index ix_part_my_tab_01 coalesce partition tbs_part_01;3.修改分区或子分区的存储属性alter index ix_part_my_tab_02 modify partition part_001 storage(freelists 5 buffer_pool default);/将空闲表的数目修改为 5，并且给索引分配默认的缓冲池4.修改分区索引的默认属性alter index ix_part_my_tab_02 modify default attribues pctfree
31、 10 storage(initial 200k next 200k);5.重命名分区alter index ix_part_my_tab_02 rename partition part_001 to partition_001;6.分割分区或子分区若使用全局索引，可能要分割索引中的分区alter indexix_part_my_tab_01 split partition part_002 at(5000) into(partition partition_002_a,partition_partition_002_b);7.删除索引分区或子分区alter index ix_part_my
32、_tab_01 drop partition partition_002_b;6.4 删除索引6.4.1 安全性需求 需在自己的模式中 或拥有 drop any index 的权限6.4.2 删除索引的例子drop index ix_part_my_tab_01；6.5 与索引相关的数据字典信息视图名称 描述 All UserDBA_INDEXES 列出每一个单独的索引 可用 可用DBA_IND_PARTITIONS 列出分区索引中的每一个单独的分区或子分区可用 可用DBA_IND_COLUMNS 列出给定索引中所有列的详细信息 可用 可用DBA_IND_EXPRESSIONS 提供所有域基于函数索引相关联的表达式可用 可用DBA_IND_SUBPARTITION 为每个分区索引提供分区信息 可用 可用DBA_JOIN_IND_COLUMNS 描述数据库中将位图加入到索引中的加入条件可用 可用DBA_PART_INDEXES 描述数据库中的分区索引 可用 可用
