Managing Indexes参考.ppt

Managing Indexes Objectives After completing this lesson, you should be able to do the following: List the different types of indexes and their uses Create various types of indexes Reorganize indexes Drop indexes Get index information from the data dictionary Monitor the usage of an index Classification of Indexes Logical Single column or concatenated Unique or nonunique Function-based Domain Physical Partitioned or nonpartitioned B-tree Normal or reverse key Bitmap B-Tree Index Bitmap Index 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 Use the parameter CREATE_BITMAP_AREA_SIZE to specify the amount of memory allocated for bitmap creation. 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 Change a reverse key index to a normal B-tree index and vice versa Online Rebuild of Indexes Rebuilding indexes can be done with minimal table locking Some restrictions still apply Coalescing Indexes Checking Index Validity Dropping Indexes Drop and re-create an index before bulk loads. Drop indexes that are infrequently needed and build them when necessary. Drop and re-create 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 data dictionary. DBA_INDEXES: Provides information on the indexes DBA_IND_COLUMNS: Provides information on the columns indexed DBA_IND_EXPRESSIONS: Provides information on function ba