Tham khảo tài liệu 'oracle 8 database administration volume 2 instruction guide phần 2', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Reorganizing Indexes Checking Index Validity 13-15 Copyright Oracle Corporation 1998. All rights reserved. ORACLE Analyze the index to perform the following Check all the index blocks for block corruption. Note that this command does not verify whether index entries correspond to data in the table. Populate the INDEX_STATS view with information about the index. Syntax ANALYZE INDEX schema. index VALIDATE STRUCTURE After running this command query INDEX_STATS to obtain information about the index as shown in the following example SVRMGR SELECT blocks pct_used distinct_keys 2 lf_rows del_lf_rows 3 FROM index_stats BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS 25 11 14 0 1 row selected. Reorganize the index if it has a high proportion of deleted rows for example when the ratio of DEL_LF_ROWS to LF_ROWS exceeds 30 . Oracle8 Database Administration 13-25 Lesson 13 Managing Indexes Dropping Indexes Dropping Indexes Drop and re-create an index before bulk loads. Drop indexes that are infrequently needed and build them when necessary. Drop and recreate invalid indexes. DROP INDEX dname idx I 13-16 Copyright Oracle Corporation 1998. All rights reserved. ORACLE Indexes may need to be dropped in the following scenarios An index that is no longer needed by applications can be removed. An index may be dropped prior to performing bulk loads. Dropping an index prior to large data loads and recreating them after the load - Improves performance of the load - Uses index space more efficiently Indexes that are used only periodically do not need to be maintained unnecessarily especially if they are based on volatile tables. This is generally the case in an OLTP system where ad hoc queries are generated at year-end or quarter-end to gather information for review meetings. An index may be marked INVALID when there is an instance failure during certain types of operations such as loading. In this case the index needs to be dropped and recreated. The index is corrupt. 13-26 Oracle8 .
