Index Clustering Factor Deep Dive指标聚类因子深俯冲.pptVIP

03.11.2005 DRAFT Index Clustering Factor Deep Dive Tony HaslerUKOUG Birmingham December 2009  Agenda Actors reconstruction of a real-life performance problem Discussion of the (in)accuracy of Oracles clustering factor estimate and why it is important How to identify and correct clustering factor related performance issues ACKNOWLEDGEMENT: Chapter 5 of Cost Based Oracle Fundamentals by Jonathan Lewis was my source for 99% of the information in this talk The Case Study A large table was loaded with additional data once per month and was read-only the rest of the time At the time of the issue the table held about two years worth of data A column on the table called MONTH_DATE held the date of the first day of the month of the load The data was queried along with other tables using Business Objects The concept of index clustering Why Clustering Factor is important Full table scans avoid access to the index structures Full table scans use multi-block reads to the table data whereas access via an index uses single block reads Each block is visited exactly once BUT Indexed access doesnt necessarily read all the blocks in the table The previous example shows that selectivity is not sufficient to determine whether the index should be used Cost of Indexed Access to a Table The full formula is: cost = blevel + ceiling(leaf_blocks * effective index selectivity) + ceiling(clustering_factor * effective index selectivity) The simplified formula, ignoring cost of traversing index and assuming that all predicates are managed by the index: cost = ceiling(clustering_factor * index selectivity) Clustering Factor For a Strongly Clustered Index Clustering Factor For a Weakly Clustered Index How to Interpret the Clustering Factor of an Index Generally the clustering factor will be between the number of blocks and the number of rows in a table A low number is good and reflects strong clustering A high number is bad and reflects weak clustering The Clustering Factor may be lo