Index too big error

[Richard Kreiss]

> -----Original Message-----
> From: Filepro-list [mailto:filepro-list-
> bounces+rkreiss=verizon.net at lists.celestial.com] On Behalf Of Jose
Lerebours
> via Filepro-list
> Sent: Tuesday, September 6, 2016 11:38 AM
> To: filepro-list at lists.celestial.com
> Subject: Re: Index too big error
> 
> 
> On 09/06/2016 11:27 AM, Richard Kreiss via Filepro-list wrote:
> > This client has a number of extremely large files The largest, just
> > over 21 million and growing (2 of these) - 6.6GB and 3.3 GB There are
> > at least 2 files of between 1 and 2 million  records - Under a GB in
> > size
> 
> Just what kind of application needs to have over 21M records in a table?
Even
> the IRS allows you to destroy records after 5 years (or is it 3? - of
course, if you
> are a Clinton, you can destroy them at will
> ;-) ... ).
> 
> I am sure that the problem of size is not due to the number of records but
the
> size of each record.  filePro users often fall in the trap of "one single
table with
> as many fixed fields to get it done in less than
> 5 minutes" and rarely employ the practice of "normalized" data structure.
> 
> Perhaps, it is time for you to
> 
> a) look at the map, split your table and normalize your data
> b) purge records and shrink your data segments to minimum possible (hope
you
> are not using @rn as key)

This file is already normalized as much as possible given the requirements
of this file.  The index was being built on date (8,mdy/) and an order
number (7,.0) and item #(5,#).  Even in a normalized file these values would
not change nor would the number of records.  These are order details which
date back over 10 years.  There are reports which run against all of this
data.  These reports run at least twice a week.

I had tried to get my client to allow for posting out old details to a
history file but included in his reports.  He didn't want that and after
arguing this issue a few times, I  gave up as he is paying the bills.

The only way I could get him to archive and not use the old records, would
be to create a file which holds the data for the report for that period and
the import it into the report for calculation purposes.  But that's a future
project.

Ken gave me the environmental variables I needed to set to allow for
building larger demand indexes.