Trying to understand index compression a bit better...

I have a table with a non-Unique key on a single String field 159 characters long. Example
values in this field would be:

Grain
Grain:Corn
Grain:Corn:2007
Grain:Corn:2008
Grain:Wheat
Grain:Wheat:2007
Grain:Wheat:2008

Would full compression be best here? I suppose trailing-byte compression should help,
because of the large number of blank spaces at the end of each field. But will
duplicate-byte compression also be a benefit?

My real question:  Does duplicate-byte compression compare bytes at the same position in
adjacent keys, or does it compress duplicate bytes within a key?

Thanks,
Mark Wilsdorf
Flagship Technologies, Inc.
QuickBooks™ Add-Ons and Solutions You Can Use
http://www.goflagship.com

Mark,

<< Would full compression be best here? I suppose trailing-byte compression
should help, because of the large number of blank spaces at the end of each
field. But will duplicate-byte compression also be a benefit? >>

Yes, absolutely.  You've got a lot of duplicate data, so duplicate-byte
compression will be able to extract a lot of space savings.

<< My real question:  Does duplicate-byte compression compare bytes at the
same position in adjacent keys, or does it compress duplicate bytes within a
key? >>

It compares the bytes at the same position starting from the absolute left
position in the key.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Another question:

After an Alter Table in DBSys to add compression, the .IDX file is the same size on disk,
about 60 K on a small table.

I suppose this has no bearing on the .IDX file size over the life of the database? (i.e.,
that compression should keep the file from growing as large).

Thanks,

Mark Wilsdorf
Flagship Technologies, Inc.
QuickBooks™ Add-Ons and Solutions You Can Use
http://www.goflagship.com

Mark


Try an optimise.

Roy Lambert

Roy,

> Try an optimise.

Ah...didn't think of that.

Thanks!

Mark Wilsdorf

Mark,

<< After an Alter Table in DBSys to add compression, the .IDX file is the
same size on disk, about 60 K on a small table. >>

DBISAM does an "in-place" alter when you only modify the indexes, thus it
re-uses the same space and does not get rid of any "extra space", rather
putting it on the free list for later re-use.

--
Tim Young
Elevate Software
www.elevatesoft.com