CODEWITHSUNDEEP
sqloracle-databasedatabase-administrationquery-performancetable-index
Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Clustering Factor and Unique Key

Clustering factor - A Awesome Simple Explanation on how it is calculated:

Basically, the CF is calculated by performing a Full Index Scan and looking at the rowid of each index entry. If the table block being referenced differs from that of the previous index entry, the CF is incremented. If the table block being referenced is the same as the previous index entry, the CF is not incremented. So the CF gives an indication of how well ordered the data in the table is in relation to the index entries (which are always sorted and stored in the order of the index entries). The better (lower) the CF, the more efficient it would be to use the index as less table blocks would need to be accessed to retrieve the necessary data via the index.

My Index statistics:

So, here are my indexes(index over just one column) under analysis.

Index starting PK_ is my Primary Key and UI is a Unique key. (Ofcourse both hold unique values)

Query1:

SELECT index_name,
  UNIQUENESS,
  clustering_factor,
  num_rows,
  CEIL((clustering_factor/num_rows)*100) AS cluster_pct
FROM all_indexes
WHERE table_name='MYTABLE';

Result:

INDEX_NAME           UNIQUENES CLUSTERING_FACTOR   NUM_ROWS CLUSTER_PCT
-------------------- --------- ----------------- ---------- -----------
PK_TEST              UNIQUE             10009871   10453407          96 --> So High
UITEST01             UNIQUE               853733   10113211           9 --> Very Less

We can see the PK having the highest CF and the other unique index is not.

The only logical explanation that strikes me is, the data beneath is stored actually by order of column over the Unique index.

1) Am I right with this understanding?
2) Is there any way to give the PK , the lowest CF number?
3) Seeing the Query cost using both these index, it is very fast for single selects. But still, the CF number is what baffle us.

The table is relatively huge over 10M records, and also receives real time inserts/updates.

My Database version is Oracle 11gR2, over Exadata X2

Upvotes: 4

Views: 795

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20842

You are seeing the evidence of a heap table indexed by an ordered tree structure.

To get extremely low CF numbers you'd need to order the data as per the index. If you want to do this (like SQL Server or Sybase clustered indexes), in Oracle you have a couple of options:

  1. Simply create supplemental indexes with additional columns that can satisfy your common queries. Oracle can return a result set from an index without referring to the base table if all of the required columns are in the index. If possible, consider adding columns to the trailing end of your PK to serve your heaviest query (practical if your query has small number of columns). This is usually advisable over changing all of your tables to IOTs.
  2. Use an IOT (Index Organized Table) - It is a table, stored as an index, so is ordered by the primary key.
  3. Sorted hash cluster - More complicated, but can also yield gains when accessing a list of records for a certain key (like a bunch of text messages for a given phone number)
  4. Reorganize your data and store the records in the table in order of your index. This option is ok if your data isn't changing, and you just want to reorder the heap, though you can't explicitly control the order; all you can do is order the query and let Oracle append it to a new segment.

If most of your access patterns are random (OLTP), single record accesses, then I wouldn't worry about the clustering factor alone. That is just a metric that is neither bad nor good, it just depends on the context, and what you are trying to accomplish.

Always remember, Oracle's issues are not SQL Server's issues, so make sure any design change is justified by performance measurement. Oracle is highly concurrent, and very low on contention. Its multi-version concurrency design is very efficient and differs from other databases. That said, it is still a good tuning practice to order data for sequential access if that is your common use case.

To read some better advice on this subject, read Ask Tom: what are oracle's clustered and nonclustered indexes

Upvotes: 5

Related Questions