All,

I have specified an indexed field in which no record has information saved in this field. In the same file I have another field in which only 9 records have data in that field. These fields are btree indexed. I am running arev 3.12.

When I dump the !file there is all sorts of records referenced.

What is happening and is this a bug?

Thanks in advance,

Dale

Dale,

I don't believe this is a bug. Even though you have no data in the field, there is still a "null" value there. The system records this in the index. Thus, if you were to run a

SELELCT my_table WITH my_field="

the system would still be able to use the index to locate those records.

I guess, in short, remember that to a database system NULL is still a valid value.

HTH,

[email protected]

Greyhound Lines, Inc.

Thanks Matt,

I forgot to include the following information in my rush to get to work.

In order to fix my corrupted index, I turned off the indexing in each of the fields and for the first 3/4 of the fields (BTREE), the F.DISTRIBUTOR message appeared. For the last 1/4 of the fields I was able to remove the indexed fields (xref) gracefully. I then looked for the !file and it was still there. I then deleted the !file inside AREV.

I then rebuilt the btree indexing and then the xref indexing with no problems except…

When I DUMPed the !file, I saw an entire group (1k) full of references to records for a field, for which no records in the database had information. My question is Why is this occuring. Also, for another field that had only 9 records with information in that field, there was an entire group full of information. Why?

Dale

Dale,

Bottom line. If it works, then it isn't a bug .

Indexes carry a duplication of some of your data. This occurs both for indexed fields, and also for keys throughout the file (sequenced and clustered within ranges based on your indexed fields).

We would suggest that if you write your own indexing routines you will quickly recognize the issues and features of the native indexing.

Also consider that your design must cater for 64KB+ of keylist. Btree indexing is well documented in other languages too, such as C, and also in filing systems like C-ISAM.

The Sprezzatura Group

[i]Celebrate CeBIT with Sprezz Local![/i]

Thanks Sprezz and associates.,

I am still unclear as to why if an indexed field has no records being indexed on that field that it would create 1k worth of information in the !file. I must confess ignorance concerning C+ etc. But logic would seem to dictate that if there are no records accessing this field that there would be no information for this area in !file.

Whenever I get the F.DISTRIBUTOR message in the debugger, indicating a corrupt index, I attempt to remove the indexing on all indexed fields. I start with this particular field and the F.DISTRIBUTOR message appears on all BTREE fields.

In talking with Don Bakke, he asked me if I was doing a lot of writing to a file and this is the case. But not to any indexed fields. So I am wondering just what is going on.

I am thinking of instead of writing to the main file that I should write to a temp file for producing the report that is needed and then copying the records back into the main file with the updated information after the routine is completed.

Thanks for your assistance,

Dale Walker

[email protected]

Dale ..

I had the following:

Had a Dict symbolic named CHECK_XREF which validated that if a source record was deleted, and an empty relational index remained, then it would delete the empty relational index as well. The indexing routine saw this as a b-tree index in the table EMPLOYEE_XREF and dutifully created an entry for it in the !file..even though the field was never accessed that way. The only to fix it was to rename the symbolic CHECKXREF, remove indexing from the table and related table. Put the indexes back and update. No trace remained of CHECK_XREF. The moral is that there are some dictionary names that may get treated as indexes even when they're not (no index flags in the dict element set).

Don Miller

C3 Inc.

I am still unclear as to why if an indexed field has no records being indexed on that field that it would create 1k worth of information in the !file.

If there is a record, there is a value in that field. Perhaps the value is a null, but that is still indexed.

I must confess ignorance concerning C+ etc. But logic would seem to dictate that if there are no records accessing this field that there would be no information for this area in !file.

You need to change the way you think about this field. Don't think of these records as having no information in that field. Think of them as having a value of null.

Whenever I get the F.DISTRIBUTOR message in the debugger, indicating a corrupt index, I attempt to remove the indexing on all indexed fields. I start with this particular field and the F.DISTRIBUTOR message appears on all BTREE fields.

In general, you cannot edit the index information using the system editor. One reason for this is that some indexing records have trailing @FM or @VM and editing these using the system editor clears them out (one of the features of the editor is that it trims off what it considers to be extra marks). This will result in a corrupted index. If you think you have a corrupted index, the safest bet is to remove indexing completely and set it back up. But remember that information on records with nulls in the field are supposed to be in the index. Don't try to edit these out.

In talking with Don Bakke, he asked me if I was doing a lot of writing to a file and this is the case. But not to any indexed fields.

If you are creating a new record on the file you are updating the indexed field whether you have set a value or not. Again, think of it as no value but as a value of null.

I am thinking of instead of writing to the main file that I should write to a temp file for producing the report that is needed and then copying the records back into the main file with the updated information after the routine is completed.

What would this accomplish? You will have the same problem when copying the records.

Thank you Victor,

Could you please help me with the responses as follows?

I am still unclear as to why if an indexed field has no records being indexed on that field that it would create 1k worth of information in the !file.

If there is a record, there is a value in that field. Perhaps the value is a null, but that is still indexed.

At the F5 tcl prompt I ran the following: list ind_data with Step_mother ] The result was "No Records Selected" At F5 I typed Dump !ind_data The result was upon inspection a complete group of numbers delimited by @vm [/b] I must confess ignorance concerning C+ etc. But logic would seem to dictate that if there are no records accessing this field that there would be no information for this area in !file. You need to change the way you think about this field. Don't think of these records as having no information in that field. Think of them as having a value of null. If the value is null why is the group in dump full of references to records? Whenever I get the F.DISTRIBUTOR message in the debugger, indicating a corrupt index, I attempt to remove the indexing on all indexed fields. I start with this particular field and the F.DISTRIBUTOR message appears on all BTREE fields. In general, you cannot edit the index information using the system editor. One reason for this is that some indexing records have trailing @FM or @VM and editing these using the system editor clears them out (one of the features of the editor is that it trims off what it considers to be extra marks). This will result in a corrupted index. If you think you have a corrupted index, the safest bet is to remove indexing completely and set it back up. But remember that information on records with nulls in the field are supposed to be in the index. Don't try to edit these out. I am not trying to edit these out. All I did was turn off the indexing for the field in DBADMIN | Indexing | Btree | Ind_data | Step_mother | No | F9 The result was a return to the debugger with the F.DISTRIBUTOR message. This occured with all the Btree indexed fields. When I turned off the xref fields in Cross Ref, I had no problems. When I looked for the !file in Locations it was still there. I then deleted the !ind_data file and then reconstructed my indexes. That field then had the same amount of data in Dump. In talking with Don Bakke, he asked me if I was doing a lot of writing to a file and this is the case. But not to any indexed fields. If you are creating a new record on the file you are updating the indexed field whether you have set a value or not. Again, think of it as no value but as a value of null. I am not creating a new record in that file. I am updating/adding the information in each of the records. I am thinking of instead of writing to the main file that I should write to a temp file for producing the report that is needed and then copying the records back into the main file with the updated information after the routine is completed. What would this accomplish? You will have the same problem when copying the records. Are you saying that if I use the overwrite Option in row |copy that this problem will reoccur? Thanks, Dale </QUOTE> —- === At 23 APR 2002 02:45PM dale walker wrote: === <QUOTE>Don, I have noticed this when I turned off the indexing for the fields such as Surname and Given_Name. I was able to gracefully accomplish this. Thanks, Dale </QUOTE> —- === At 23 APR 2002 05:11PM Victor Engel wrote: === <QUOTE> At the F5 tcl prompt I ran the following: list ind_data with Step_mother ] The result was "No Records Selected" At F5 I typed Dump !ind_data The result was upon inspection a complete group of numbers delimited by @vm [/i] What do you get if you type: list ind_data with Step_mother=' This should use the data you saw using DUMP to resolve those records with a null value. If the value is null why is the group in dump full of references to records? To be simplistic, it is a listing of keys whose records contain null values for that field. I am not trying to edit these out. All I did was turn off the indexing for the field in DBADMIN | Indexing | Btree | Ind_data | Step_mother | No | F9 Sorry. I misunderstood. The result was a return to the debugger with the F.DISTRIBUTOR message. This occured with all the Btree indexed fields. At this point, what I would do if I were in that position is the following: [list] [*]Disable indexing using the dictionary window (DICT filename recname then press SF1 to make sure indexing is turned off). Do this for all types of indexes. [*]At this point there should be no !filename file unless the file is the target of a relational index. If it is, remove the relational index from the field that is related to this file. [*]If for some reason, the !filename remains, there would have to be further analysis to determine why, but I'd probably edit the REVMEDIA file directly to remove SI.MFS, reattach the files, then delete the !filename file. [*]Use DICT to establish the Btree indexes again. [*]Rebuild the indexes from the menus. [/list] This should resolve the problem in most cases. There are other possible workflows, but this is the one that works well for me. Other things to ponder: Have you copied dictionary records (potentially with indexing information embedded) from one file to another? Do you attach the same filename on separate volumes? When I turned off the xref fields in Cross Ref, I had no problems. You may be interested to know that the way Xref indexes are implemented is by creating a symbolic field that functions as a multivalued version of the field being indexed. This symbolic field is then given a Btree index. When I looked for the !file in Locations it was still there. That means there is still some indexing set up on the file. Type listindex filename to find out which field the system thinks is still indexed. If necessary, turn and index on on that field and then back off. I then deleted the !ind_data file and then reconstructed my indexes. That field then had the same amount of data in Dump. Indexing control information is stored within the dictionary records as well as in the !filename file. If these are still present, some parts of the indexing routines will still thing there is an index installed. This is why I suggest to remove indexing using the dictionary editor (DICT command). I am not creating a new record in that file. I am updating/adding the information in each of the records. When a record in a file that is indexed is updated, the system checks the old/new values of each indexed field and creates a token on the !filename file that contains the key, field name, old value, new value. This information is used by the background index processor to purge the old information and add the new. Are you saying that if I use the overwrite Option in row |copy that this problem will reoccur? I'm saying that the copy command does writes just like anything else. All the overwrite option affects is whether the write should occur if the record already exists. It has no effect on indexing. </QUOTE> —- === At 24 APR 2002 11:13AM dale walker wrote: === <QUOTE>Victor, I do appreciate the time that you have taken to help me understand what is going on. With respect to typing the following command: list ind_data with step_mother =' I get a listing of every record in ind_data in numeric order. I followed your instructions using the DICT command and it did clean up the dictionary as well as the !ind_data file. I did not have to do anything with the REVMEDIA file. I think that I have theoretically solved the problem. There is another file that is not only being updated but is having records added to it that is not having a problem. The marriage_data file is being indexed immediately upon having a record added. Since this routine will eventually be dealing with about 40,000 records from time of execution to completion, I am thinking of calling the INDEX.FLUSH routine every time an individuals familys records are updated in IND_DATA. That way the indexing routine wont be overwhelmed. Dale </QUOTE> —- === At 24 APR 2002 04:19PM Don Miller - C3 Inc. wrote: === <QUOTE>Dale .. Puzzling, indeed. What happens if you do a Select on the same field? Does it come back pretty clickly and indicates that it's using an index or not? If it is, then my guess is that you have a lot of records that have a null value in that field. Is that what you would expect? Also, FWIW, I assume you have a space before and after the equals ( key. It would be instructive to try a b-tree extract on the field to see what comes up that way, too. Don Miller C3 Inc. </QUOTE> —- === At 24 APR 2002 04:57PM dale walker wrote: === <QUOTE>Don, For what it is worth there are 79,000+ records in this file. I have not performed a select yet. When I do list it does indicate that it is using an index. Quickly - that is relative. I am using a walker special 233 mh with 64mb ram. Windows 98. It takes a couple of minutes to indicate that there are no records present for step_mother. However if I use =' then it jumps right in beginning with 1,10,100,1000… Dale </QUOTE> —- === At 24 APR 2002 07:47PM Victor Engel wrote: === <QUOTE>There is one thing you should also check when setting up indexing. If your environment setting for case sensitivity for query processes does not match the case sensitivity used to create the index, your selects will take as much time as if there were no index. You can check whether this is affecting you without rebuilding your index by doing the following: list ind_data with casesens Step_mother ] list ind_data with caseinsens Step_mother ] '' One of these commands will match your environment setting. The other will be different. If the one that is different from your environment setting matches the case sensitivity of your index, your results will come back much faster. A message will also display indicating that indexes are being used in the search. Note that the index facility within DICT defaults to a setting that is independent of your environment setting (at least in the Arev version I'm currently using). This means that if your environment setting is opposite to this default, you will create an inefficient index every time by accepting the default. </QUOTE> —- === At 25 APR 2002 01:23PM dale walker wrote: === <QUOTE>Victor, I appreciate the time and effort that you have taken to educate me on these issues. Just as a fyi - Step_mother is a numeric btree not alpha field. The default in my xref fields are not case sensitive. Dale </QUOTE> —- === At 25 APR 2002 02:23PM Victor Engel wrote: === <QUOTE>The case sensitivity issue applies no matter what the data type of the field is. </QUOTE> —- === At 01 MAY 2002 02:24PM dale walker wrote: === <QUOTE>Folks, I thank all who contributed to this issue: Victor, Matt and Don. The problem was caused by a different indexed file whose !file was corrupted than what I was looking at. Victor gave a very detailed discussion concerning indexing for which I am very appreciatitve.. Thanks for your assistance and insights, Dale Walker </QUOTE> View this thread on the forum...