oracle 索引分析

索引重组 在一个不稳定的表上建索引会影响性能，一个索引block只有完全空时才能进入free list，即使一个索引block里只含有一个条目，它也必须被维护，因此索引需要进行阶段性的重建。 1）检查索引是否需要重组 A、收集一个index的使用统计 ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE; B、查看收集的统计数据 SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats; Column Description LF_ROWS Number of values currently in the index LF_ROWS_LEN Sum in bytes of the length of all values DEL_LF_ROWS Number of values deleted from the index DEL_LF_ROWS_LEN Length of all deleted values C、如果浪费超过20％则索引需要重建 ALTER INDEX acct_no_idx REBUILD; D、或者对索引进行整理 Alter index acct_no_idx coalesce; 2）标记未使用的索引 A、 开始监测索引的使用 Alter index hr.emp_name_ix monitoring usage; B、 停止监测索引的使用 Alter index hr.emp_name_ix nomonitoring usage; C、 查询索引的使用情况 Select index_name,used from v$object_usage; 删除未使用过的索引，可以降低DML操作的成本，从而提升系统性能。 ANALYZE INDEX OPERATOR_ID VALIDATE STRUCTURE SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats; Alter index IDX_S_P_D coalesce; 创建索引: 原则 • 平衡查询和 DML 需求 • 放在一个单独的表空间内 • 使用统一的区大小: 五块的倍数或表空间 MININUM EXTENT 大小的倍数 • 考虑对大索引使用 NOLOGGING • 如果新的关键字值有可能位于当前范围内, 则设置高 PCTFREE 重建索引 索引重建具有以下特点 • 使用现有索引作为数据源建立新索引 • 使用现有索引建立索引时无须排序从而使性能更佳 • 在建立新索引后删除旧索引重建期间各自的表空间内需要有足够的空 间以容纳新旧索引 • 结果索引不包括任何已删除的项因此该索引能更有效地使用空间 • 在建立新索引的过程中查询可继续使用现有索引 可能的重建情况 下列情况应重建索引 • 需将现有索引移到另外的表空间如果索引和表在同一表空间或需要跨磁盘 重新分布对象时可能能需要进行此操作 索引中包含很多已删除的项这是滑动索引如订单表订单号上的索引 存在的典型问题完成的订单已被删除而具有更高订单号的新订单被添加 到表中如果有几个旧订单未完成则可能有若干个索引叶块包含除几个已 删除的项外的全部项 • 需将现有正常索引转换成反向键索引这可能发生在从 Oracle 服务器的早 期发行版移植应用程序时 • 已通过 ALTER TABLE..MOVE TABLESPACE 命令将索引表移至其它表空间 何时应删除索引 下列情况应删除索引 • 应用程序不再需要索引时可将索引删除 • 执行批量装载前删除索引大量装载数据前删除索引装载后再重建索引的 好处有 – 提高装载性能 – 更有效地使用索引空间 • 仅定期使用的索引无须不必要的维护尤其在基于易失表时更是如此在 OLTP 系统中通常发生这种情况通常在年末或季度末生成特殊的查询以 收集在回顾讨论会上使用的信息 • 当在某种类型的操作如装载期间有例程失败时索引可能会标记为 INVALID 在这种情况下需要删除并重建索引 • 索引已损坏 获取索引信息 DBA_INDEXES OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENESS TABLESPACE_NAME LOGGING STATUS DBA_IND_COLUMNS INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH