管理索引.ppt

管理索引 目标 学完这个课程，你应该可以掌握你下内容: 列出不同的索引类型和它们的使用 创建各种类型的索引 重组索引 维护索引 监控索引的使用情况 索引的种类 逻辑 Single column or concatenated Unique or nonunique Function-based Domain 物理 Partitioned or nonpartitioned B-tree Normal or reverse key Bitmap B-Tree Index Bitmap Indexes Comparing B-Tree and Bitmap Indexes Creating Normal B-Tree Indexes Creating Indexes: Guidelines Balance query and DML needs Place in separate tablespace Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for tablespace Consider NOLOGGING for large indexes INITRANS should generally be higher on indexes than on the corresponding tables. Creating Bitmap Indexes Changing Storage Parameters for Indexes Allocating and Deallocating Index Space Rebuilding Indexes Use the ALTER INDEX command to: Move an index to a different tablespace Improve space utilization by removing deleted entries Rebuilding Indexes Online Indexes can be rebuilt with minimal table locking. Some restrictions still apply. Coalescing Indexes Checking Indexes and Their Validity Dropping Indexes Drop and recreate an index before bulk loads. Drop indexes that are infrequently needed and build them when necessary. Drop and recreate invalid indexes. Identifying Unused Indexes To start monitoring the usage of an index: To stop monitoring the usage of an index: Obtaining Index Information Information about indexes can be obtained by querying the following views: DBA_INDEXES: Provides information on the indexes DBA_IND_COLUMNS: Provides information on the columns indexed V$OBJECT_USAGE: Provides information on the usage of an index Summary In this lesson, you should have learned how to: Create different types of indexes Reorganize indexes Drop indexes Get index information from the data dictionary Begin and end monitoring usage of indexes Practice 12 Overview This practice covers the following topics: Creating an index on columns of a table Moving the index to another tablespace Dropping an index Obtain index information Class