Bloated & Broken Relational Index (AREV Specific)
At 03 AUG 2000 12:19:49PM Roy Drummond wrote:
I am discovering wonderful secrets in my new client's old AREV application. I have three relational indexes that I can not rebuild. The best example has 440,000 records with no SSN and there is a relational index on this field. I figure I am trying to fit 5MB of keys into 64K. No way. The other two files are smaller but have the same problem.
I have several thoughts as to how to fix this:
1) Rewrite all occurances using a Btree index. Have started this.
2) Modify source code to REBUILDINDEX_SUB to return when key is null for relational indexes. Haven't looked yet, but suspect no Source Code.
3) Create a shell for REBUILDINDEX_SUB that will return if value is null. I got that idea from the discussion pages, thanks. Will this work?
4) Any other great ideas?
Any responses will be greatly appreciated.
Roy
At 03 AUG 2000 12:24PM Roy Drummond wrote:
Just for fun…any ideas WHY these indexes worked for so long. They must have been built ages ago when they were small, but why haven't they had more problems with them???
RgD
At 03 AUG 2000 01:40PM Larry Wilson - TARDIS Systems wrote:
First, to the part about the rebuild ignoring records with a null source field; I assume you are wanting the system to skip these records - but it can't because it's got to read the record just to see if there's anything in the source field. If there's nothing there, it does not do any more processing of the record.
Second, are you saying that a target field could end up with more than 64K of keys in it? If so, I would not know why it kept working. Since mine have never exceeded that, I don't know if the system just ignores the update or issues an error message.
Lastly, using BTREE indexes is always, IMHO, the best thing to do. An option would be to write your own MFS and store the information in a 3rd file (does not count any index files for either the source or target). However, make sure you're up to 3.12 as BTREE had a problem before that. It still has a problem, not as bad, but I haven't gotten it to fail consistently, so the error is not repeatable.
Larry Wilson
At 03 AUG 2000 03:52PM Roy Drummond wrote:
Thanks Larry,
You said I was getting a variable exceeds max. length error and thought AREV was trying to index all those null SSNs.
When I tried to rebuild the index it broke, but just now I removed then index and then reapplied it and it worked just right. Thanks for the insight.
Roy
At 04 AUG 2000 11:08AM Larry Wilson wrote:
No problem - glad to have been of some small help.