Re: Growth of Index

> I'm interested in the observation that an index will grow if records are
> added on one side of the index. I have one byte indexes on 1 million row
> tables that continue to grow and grow. These indexes are generally yes/no
 

> columns: a number of rows get created with an 'N' and then are updated to
 

> a 'Y' when the record is further processed, hence a huge number of 'Y'
> records are created in the index. Is this lopsided updating the reason
 for
> my index growth?
>

Another way to solve this issue is to leave the value of the large number (in this case 'Y') null so that only the less common value ('N') is included in the index. This will yield a usefull index when you query for 'N' values. On the downside, you have somewhat changed 'real life' by substituting a 'Y' value for null. In addition, your application will be somewhat harder to write because if you're after 'N' values you must use "where value='N'" whereas if you're after 'Y' values you have to specify "where value is null". It's simply a tradeoff of performance vs coding  

Walter Schenk
SoluTech Consulting Services Inc Received on Wed Jul 26 1995 - 00:00:00 CEST