Just in case you're still toying with the idea ...

A variation to the technique mentioned below by Gilles Schipper, would be
to specify a key length equal to the existing primary key length. Then,
when you want to add a record which doesn't require the search item (i.e.
value 0) you would replace the 0 with the value of the existing primary
key. At least you easily do away with the longest chain.

However, I suspect that you are faced with application changes that ought
to really reflect a different design. Perhaps you should adopt the
seemingly silliest of solutions and actually have 5 different detail sets
in which none would have this value as a key and each would hold records in
this common state or status (major change).

I don't think quick access to 4-5 thousand records is intended for on-line
viewing. So you're probably adding the key to improve the performance of a
module that performs some calculation on all records with a  specific
value. Therefore perhaps you should consider adding a detail set containing
the summary info per key value (also a major change).

True, these are wild guesses and true it's late Friday afternoon. But I'm
faced with something similar so I might not be too far off :) although the
file in question is a 500,000 record KSAMXL that chokes when it's time to
update the single character status key ...

Costas Anastassiades,
INTRACOM SA
Athens - Greece



----------
From:   Gilles Schipper[SMTP:[log in to unmask]]
Sent:   Πέμπτη, 6 Νοεμβρίου 1997 3:48 μμ
To:     [log in to unmask]
Subject:        Re: Single character Image key

One technique I am aware of is to create 2 separate detail data sets - one
with single-character search item (actually, it must be at least 2
characters to satisfy an image requirement for minimum item length) - and
the other without this item as a search item.

The advatages of this technique is that you do not pay the appropriate
overhead for entries that do not require this search item.

Disadvantages include perhaps a slightly more complex design and violations
of third normal form theories, etc.


At 10:23 PM 97/11/05 -0700, you wrote:
>I need to add a new key to an Image detail dataset.  The key is a single
>character with five values (0-4).  About 80% of the 85,000 records will
>have the same key value (0) and thus a long, long chain.  The remaining
>17,000 records should be spread about equally through the other four
values
>and still long chains.
>
>I recall someone posted a technique to overcome the long chains situation.
>Unfortunately, it appears that was one message I didn't archive. :(  If
>someone has the original message or knows the technique, could you please
>send it to me via e-mail?  Thanks!
>
>
>------------------------------------------------------------------
>John Pearce  <[log in to unmask]>       | Bethesda Management Company
>Speaking for only myself             | Colorado Springs, CO  USA
>
>
---------------------------------------------------------------------------
Gilles Schipper
GSA Inc.
HP3000 & HP9000 System Administration Specialists
300 John Street, Box 87651   Thornhill, ON Canada L3T 7R4
Voice: 905.889.3000     Fax: 905.889.3001
Internet:  [log in to unmask]  Compuserve: 71203,474
---------------------------------------------------------------------------