Introduction To Reverse Key Indexes: Part IV (Cluster One) January 21, 2008
Posted by Richard Foote in Clustering Factor, Index statistics, Oracle Indexes, Oracle Myths, Reverse Key Indexes.trackback
There’s a myth that suggests if the Clustering Factor (CF) of an index is greater than a certain ratio when compared to the number of rows in the table, the CF is poor and an index rebuild would be beneficial.
The slight problem with this advice is that the CF actually measures how well aligned the order of the column values are in the table as compared to the order of the index entries in the index. Generally, a table in which the column values are ordered in a similar manner to the index will have a CF closer to the number of blocks in the table. A table in which the column values are ordered in a random manner when compared to the index will have a CF closer to the number of rows in the table.
An index rebuild doesn’t change the ordering of the index row entries and an index rebuild has no impact on the table so therefore the comparative ordering of both remains unchanged. Therefore the CF of an index will be identical after the rebuild as it was before.
Well actually, there is one slight exception to this rule. Reverse Key Indexes.
Generally, rows with monotonically increasing column values are physically inserted in the order of the monotonically increasing columns. This may not be the case however with tables in ASSM tablespaces or tables with multiple freelists or freelist groups as concurrent inserts will be directed to differing blocks. In these cases we may actually have data that is quite well clustered but may have quite poor CF values due to the manner in which the CF is calculated.
Assuming a Non-ASSM, single freelist/freelist group table, the CF of monotonically increasing indexed values would ordinarily be quite good. As a non-reverse index must also have its values in the monotonically column order, the CF of the index is likely to be nice and low.
However, if you were to rebuild the index as a Reverse Key Index, the index values get reversed and “randomly” redistributed within the index structure, totally changing the order of the index entries within the index. As a result, the index values are no longer aligned with those of the table and the CF is likely to now be quite appalling.
Rebuilding an index generally has no impact on the CF as the index row values retain the same logically order. Rebuilding an index to be reverse (or visa-versa) is the exception to the rule as it will physically (and logically) change the index row order.
A Reverse Key Index is likely therefore to have a much worse CF than it’s non-reverse equivalent.
A Reverse Key Index will be ignored for range predicates (as already discussed in Part I) so a poor CF may not have an impact. However, as also discussed, index range scans are still viable in some scenarios so an increased CF may impact execution plans detrimentally.
See this demo on how a Reverse Index Rebuild turns a “perfect” CF into a shocker.
Richard Foote's Oracle Blog 
Finally I get to comment on a technical post!
I think you allude to this in your carefully worded text.
The CF can’t be modified by only rebuilding the index. The CF can be improved though by removing the data in the table and re-inserting the data in a specific order, where the order depends on the columns that have been indexed and the sequence of these columns in the index.
Of course there are trade offs with this approach. There is the obvious downtime, and there is also the fact that a table may have multiple indexes and so by favouring one you may be adversely impacting another.
I’ve never had to resort to this strategy, I’ve heard talk of it being used in DW environments, where the overnight load can easily order the incoming data from the staging area.
Regards,
Mat.
LikeLike
Hi Mathew
That’s all correct. You could re-order the rows in the table but with multiple indexes, what helps one index will hurt another.
The other possible option is to manually alter the statistics with set_index_stats. Jonathan Lewis in his “Cost-Based Oracle Fundamentals” book discusses the sys_op_countchg function which Oracle uses to calculate the CF.
The problem with the CF is often that it’s not really “correct” as it’s calculated based on the block changing from one row entry to the next, disregarding the fact the current block may already have been recently accessed.
Oracle uses the sys_op_countchg function with a parameter value of 1 for the number of history blocks to keep as recently accessed blocks. By manually applying the function to a table accessed via an index of interest with a greater recent history value set, the CF can in some circumstances be calculated with a more “correct” value.
Perhaps something worth discussing in more detail.
LikeLike
Yes, there are always trade-offs.
Manually altering the stats is a workaround for issues with the calculated CF. I’ve only ever had to resort to this approach once.
I’ve read JPLs book – haven’t yet completed the required second read through though.
Another alternative that should be mentioned, instead of reloading the data into the table to maintain a good CF for an index, is to force the data in the table to live in a specific place by using table clusters or IOTs. This is subject to the same side effect of favouring specific index accesses.
I’ll look forward to a post on circumstances where setting stats is appropriate.
Mat.
LikeLike
Hi Mathew
Yes, I cover IOT a fair bit in my seminar. I also cover secondary indexes on IOT as well which is where one often has problems with IOTs.
I’ll discuss the classic example of when setting stats manually might be worthwhile for sure.
LikeLike
[…] For a nice little demo, see David Alridge’s comment or my previous discussion on Reverse Key Indexes. […]
LikeLike