Re: Shrinking an index??

Z. Martinez wrote:
>

 <snip>
> There is currently an index that keys on the "type" column.
> It certainly looks like a waste of storage and processing, cause the
> remaining 900,000 entries are not really needed.
>
> Is there a way to create an index such that only a subset of the table
> is indexed? I would like to shrink the index to save space and
> hopefully increase the performance of the queries.
>
> Please respond to zlm101_at_psu.edu
>
> Thanks in advance.

Yes, you can do this but I wouldn't generally recommend it unless you really need the space. Performance is probably the same either way. You should create a new column and only populate it when type is 5 or 9. Otherwise leave it null. Create an index on this column and it will only contain entries for types 5 and 9, reducing the size of the index since it doesn't store nulls. The problem with this is that you have to create a new column and index for each set of values for each column, so it can get out of hand. I would recommend buying more disks.

On the other hand, there are cases where you can take advantage of this in your application. e.g. if you have a column with mostly values of 0 but the values you care about are non-zero, then you can use nulls to represent 0 and they won't be stored in the index.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.