Rebuild Indexes

异常日志

警告日志

Wed Feb 20 11:00:20 2013
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /db/oracle/diag/rdbms/dbtest/DBTEST/trace/DBTEST_j000_21813.trc:
ORA-20000: index "BARCODE"."BC_BOXES_I"  or partition of such index is in unusable state

在执行GATHER_STATS_JOB任务时，检查到索引 "BARCODE"."BC_BOXES_I"存在异常，状态为unusable。

跟踪日志：

ORA-20000: index "BARCODE"."BC_BOXES_I"  or partition of such index is in unusable state

*** 2013-02-20 11:00:20.158
GATHER_STATS_JOB: GATHER_TABLE_STATS('"BARCODE"','"BC_BOXES"','""', ...)
ORA-20000: index "BARCODE"."BC_BOXES_I"  or partition of such index is in unusable state

在跟踪日志文件中，可以看到同样的错误信息。

异常确认

检查索引状态：

SELECT C.INDEX_NAME,
       I.UNIQUENESS,
       C.COLUMN_NAME,
       C.COLUMN_POSITION,
       I.STATUS
  FROM DBA_IND_COLUMNS C, DBA_INDEXES I
 WHERE I.INDEX_NAME = C.INDEX_NAME
   AND I.OWNER = 'BARCODE'
  9   ORDER BY C.INDEX_NAME, C.COLUMN_POSITION;

INDEX_NAME	     UNIQUENESS  COLUMN_NAME	COLUMN_POSITION STATUS
--------------- 	------------ -------------- --------------- ----------
BC_BOXES_I	     NONUNIQUE	 BOX_NUMBER 				1 UNUSABLE
ITEM_MODEL_I	 NONUNIQUE	 MODEL						1 VALID
ITEM_NUMBER_I	 NONUNIQUE	 ITEM_NUMBER				1 VALID

执行统计信息收集程序，确认异常：

SQL> conn /as sysdba
Connected.
SQL> begin
  2  dbms_stats.gather_table_stats('"BARCODE"','"BC_BOXES"','""');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20000: index "BARCODE"."BC_BOXES_I"  or partition of such index is in
unusable state
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 2

解决异常

重建索引：

SQL> alter index "BARCODE"."BC_BOXES_I" rebuild tablespace barcode_idx nologging;

Index altered.

再次执行搜集程序：

SQL> begin
  2  dbms_stats.gather_table_stats('"BARCODE"','"BC_BOXES"','""');
  3  end;
  4  /

PL/SQL procedure successfully completed.

关于重建索引

When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

即：重建(rebuild)索引使用已有的索引作为数据源，可以调整存储参数，移除数据块之间的碎片。另外，重要的一点是，相比先删除(drop)再创建索引(create index)，重建(rebuild)能够提供更好的性能。

参考

  • Oracle Database Administrator’s Guide#Rebuilding an Existing Index
blog comments powered by Disqus

Published

20 February 2013

Categories

Tags

Github