Assume you have a table with a "date_inser ted" column with an index on
it. Assume also, append mode inserts. All new rows will be inserted at
the end, in date sequence. The index on the column, over a long time
period, will show a very high cluster ratio.
Now, you reorg the table, using an index on "customer_numbe r", another
column of the table. The date index will, after the reorg, have an
extremely low clustering ratio. There's nothing you can go about this,
it's an inherent characteristic of multiple indexes.
If your index with low cardinality is used for retrieving single rows
then the low cardinality doesn't effect the optimizer's use of it.
Clustering indexes significantly decrease data I/O when retrieving
ranges of rows.
I'd be a lot more interested in why, after reorg, you have three indexes
with such high cardinality. This could like a case where your have
multiple indexes with the same first few columns and one or two
different columns making up the low order end of the indexes. If these
are being used to support index only access, it may be possible to
reduce the number of indexes by combining them. This could improve
overall performance. I've also seen this where indexes existed as: index
one - cols A,B,C; index two - cols A,B,C,D; index three - cols A,B,C,E.
If the indexes are not being used for sort avoidance, they can all be
combined into a single index.
Phil Sherman
dunleav1 wrote:
I have a many row and many column table that is in a 16K page size.
I have four indexes on the table.
I am running row compression on the table.
The table does not have a primary key.
The table does not have a clustered index.
I ran a reorg on the table and the indexes.
I ran runstats on the table and the indexes after the reorg.
Three indexes on the table have an index cluster ratios of 99,99,100
respectively.
The fourth index has a low index cluster ratio(3). The fourth index
had a 96 cluster ratio prior to today.
I'm concerned about the fourth index with the low cluster ratio not
being utilzied by the optimizer.
Example:
Table has 10,000,000.
The fourth index is made up id1,id2.
If I do a select test on the data:
select distinct count(id1) from table x; 1
select distinct count(transsync idcode) from table x; 10,000,000
Why did I lose my 96 index cluster ratio after reorg?
Why does the fourth index have a low index cluster ratio as opposed to
the other indexes?
If I reverse the ids in the fourth index will I get a higher index
cluster ratio?
Suggestions?
