案例軟件課件dba

1、Managing IndexesObjectivesAfter completing this lesson, you should be able to do the following:List the different types of indexes and their usesCreate various types of indexesReorganize indexesDrop indexesGet index information from the data dictionaryMonitor the usage of an indexClassification of I

2、ndexesLogicalSingle column or concatenatedUnique or nonuniqueFunction-basedDomainPhysicalPartitioned or nonpartitioned B-treeNormal or reverse keyBitmapB-Tree IndexIndex entry headerKey column lengthKey column valueROWIDRootBranchLeafIndex entryBitmap IndexkeystartROWIDendROWID bitmapTableIndexBlock

3、 10Block 11Block 12File 3Comparing B-Tree and Bitmap IndexesB-treeSuitable for high-cardinality columnsUpdates on keys relativelyinexpensiveInefficient for queries using OR predicatesUseful for OLTPBitmapSuitable for low-cardinality columns Updates to key columns veryexpensiveEfficient for queries u

4、sing OR predicatesUseful for data warehousingCreating Normal B-Tree IndexesCREATE INDEX hr.employees_last_name_idxON hr.employees(last_name)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;Creating Indexes: GuidelinesBalance query and DML needsPlace in separate tab

5、lespaceUse uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for tablespaceConsider NOLOGGING for large indexesINITRANS should generally be higher on indexes than on the corresponding tables.Creating Bitmap IndexesUse the parameter CREATE_BITMAP_AREA_SIZE to specify the amount of

6、 memory allocated for bitmap creation.CREATE BITMAP INDEX orders_region_id_idxON orders(region_id)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;Changing Storage Parameters for IndexesALTER INDEX employees_last_name_idxSTORAGE(NEXT 400KMAXEXTENTS 100);Allocating

7、and Deallocating Index SpaceALTER INDEX orders_region_id_idxALLOCATE EXTENT (SIZE 200KDATAFILE /DISK6/indx01.dbf);ALTER INDEX orders_id_idxDEALLOCATE UNUSED;Rebuilding Indexes Use the ALTER INDEX command to:Move an index to a different tablespaceImprove space utilization by removing deleted entriesC

8、hange a reverse key index to a normal B-tree index and vice versaALTER INDEX orders_region_id_idx REBUILDTABLESPACE indx02;Online Rebuild of IndexesRebuilding indexes can be done with minimal table lockingSome restrictions still applyALTER INDEX orders_id_idx REBUILD ONLINE;Coalescing IndexesBefore

9、coalescingAfter coalescingALTER INDEX orders_id_idx COALESCE;Checking Index Validity ANALYZE INDEX orders_region_id_idxVALIDATE STRUCTURE;INDEX_STATSDropping IndexesDrop and re-create an index before bulk loads. Drop indexes that are infrequently needed and build them when necessary.Drop and re-crea

10、te invalid indexes.DROP INDEX hr.deptartments_name_idx;Identifying Unused IndexesTo start monitoring the usage of an indexTo stop monitoring the usage of an indexALTER INDEX summit.orders_id_idx MONITORING USAGEALTER INDEX summit.orders_id_idx NOMONITORING USAGEObtaining Index InformationInformation

11、 about indexes can be obtained by querying the data dictionary.DBA_INDEXES: Provides information on the indexesDBA_IND_COLUMNS: Provides information on the columns indexedDBA_IND_EXPRESSIONS: Provides information on function based indexesV$OBJECT_USAGE: Provides information on the usage of an indexSummaryIn this lesson, you should have learned how to:Create different types of indexesReorganize indexesDrop indexesGet index information from the data dictionaryBegin and end monitoring usage of ind