资源描述
Index UsageObjectivesAfter completing this lesson,you should be able to do the following:Identify index typesIdentify basic access methodsMonitor index usageIndexing GuidelinesYou should create indexes only as needed.Creating an index to tune a specific statement could affect other statements.It is best to drop unused indexes.EXPLAIN PLAN can be used to determine if an index is being used by the optimizer.Types of IndexesUnique and nonunique indexesComposite indexesIndex storage techniques:B*-tree Normal Reverse key Descending Function based Bitmap Domain indexes Key compressionFull Notes OnlyWhen to IndexIndexDo Not IndexKeys frequently used in search or query expressionsKeys and expressions with few distinct values except bitmap indexes in data warehousingKeys used to join tablesFrequently updated columnsHigh-selectivity keysColumns used only withfunctions or expressions unless creating function-based indexesForeign keysColumns based only on queryperformance Full Notes OnlyEffect of DML Operations on IndexesInserts result in the insertion of an index entry in the appropriate block.(Block splits might occur.)Delete rows result in a deletion of the index entry.(Empty blocks become available.)Updates to the key columns result in a logical delete and an insert to the index.Indexes and ConstraintsThe Oracle Server implicitly creates or uses B*-tree indexes when you define the following:Primary key constraintsUnique key constraintsCREATE TABLE new_channels(channel_id CHAR(1)CONSTRAINT channels_channel_id_pk PRIMARY KEY ,channel_desc VARCHAR2(20)CONSTRAINT channels_channel_desc_nn NOT NULL ,channel_class VARCHAR2(20),channel_total VARCHAR2(13);Indexes and Foreign KeysIndexes are not created automatically.There are locking implications to DML activity on parent-child tables.CUSTOMERS#cust_idSALESPRODUCTS#prod_idCHANNELS#channel_idBasic Access MethodsFull table scans:Can use multiblock I/O Can be parallelizedIndex scans:Allow index access only Are followed by access by ROWIDFast-full index scans:Can use multiblock I/O Can be parallelizedIdentifying Unused IndexesThe Oracle Database provides the capability to gather statistics about the usage of an index.Benefits include:Space conservation Improved performance by eliminating unnecessary overhead during DML operationsEnabling and Disabling the Monitoring of Index UsageTo start monitoring the usage of an index:To stop monitoring the usage of an index:V$OBJECT_USAGE contains information about the usage of an index.ALTER INDEX customers_pk MONITORING USAGE;ALTER INDEX customers_pk NOMONITORING USAGE;Index Tuning Using the SQL Access AdvisorThe SQL Access Advisor:Determines which indexes are requiredRecommends a set of indexes Is invoked from Advisor Central in Oracle Enterprise Manager Run through the DBMS_ADVISOR package APIsUses a workload such as:Current contents of the SQL cache A user-defined set of SQL statements A SQL Tuning Set Hypothetical workloadGenerates a set of recommendations Provides an implementation script SummaryIn this lesson,you should have learned about the following:Indexes Index types DML operations and indexes Indexes and constraintsMonitoring indexes Index usage monitoring
展开阅读全文