Btree Index reduce not return correct information (AREV Specific)
At 24 FEB 1998 02:35:00PM Wayne Curzadd wrote:
We have an application that uses null field search on two btree indexed fields to find records needing update. The select is something like "SELECT FILE WITH FIELD1=' AND WITH FIELD2='". Both fields have btree indexes. The btree reduce process appears not to be working properly. 130 records are returned this example select but only 6 list when listed with rlist after select and they are not correct. Doing a SAVELIST and reviewing the LISTS record determined the return keys are not even real keys just garbage. When one index is removed the select statement works fine.
At first I thought it was a key length problem but increasing length and rebuild indexes did nothing. Currently we must run the system with only one of these fields indexed. Long term this is not acceptable because we are dealing with thousands and something tens of thousands of records.
Is there a problem with reducing two null value btree indexes.
Any help greatly appreciated.
At 24 FEB 1998 04:15PM Aaron Kaplan wrote:
So, the system says 130 rows are selected and when you list you only see 6? How are these incorrect? Are they keys that do not mee the criteria or are they garbage seen in the lists file? How do the keys in the list file match to what you would expect to be returned? Are your results consistant? Is it always the same wrong information returned? If not, can you determine a patern? Do you have similar problems when doing a BTREE.EXTRACT? Is this 3.12 like the other problem?
apk@sprezzatura.com
At 24 FEB 1998 06:09PM Wayne Curzadd wrote:
Aaron,
When the SELECT statement return 130 hits it is consistently the same list and only the six listed are valid record keys. It just so happens that the six do not match the selection criteria. In otherwords both fields on these records are not null.
Btree.extract will return either fields null records fine but when combined with an and statement in a select, the key list appears to be trashed when saved in a savelist command or listed. Most of the keys list in the lists file are not real keys, the keys appear to be truncated. The file has a single part key set at 14 characters, the longest actual key is about 10-12 characters. If I remove either index the select works. The only difference between the two fields is that one is a two part key to another file (not relational index), but if selecting with this field indexed there is no problem.
When the select command is perform with only one field indexed the resulting list is correct, a list command verified the results. However, the interesting thing is known of these records were included in the list when both fields were indexed. A somewhat disturbing situation.
Thanks for the input.
At 24 FEB 1998 06:29PM Aaron Kaplan wrote:
We might have had just the slightest miscommunication. SELECT and BTREE.EXTRACT go through different procedures for a good chunck of the processing. Some if it's the same, but most is different. So, if BTREE.EXTRACT gives you the same results, the problem is in one part of the system. If it gives different, the it's in another part. From your response, I can't tell if you actually tried BTREE.EXTRACT. I say this because you can't make a list from BTREE.EXTRACT. You can do a make.list, but that throws in a whole new set of variables. I suppose you could be explicitly writing the hits, but my instinct tell me otherwise.
Personally, (and again this is instinct, not science) you don't have enought room in the key. For a 10-12 character key, I'd give the key field about 16-18 for length. Remember this key field is NOT @ID but the field with key part 0 that is defined as the Master Field.
What happens if there is data in the field to approximate the same number of records? Like change all these nulls to Kurt is great! and see if you get the same results.
apk@sprezzatura.com