Got a strange one here

Arev 3.12

Novell 3

5 User

DOS only - no NLM

I have a table with 160K rows on it which has worked fine for years. One of the rows carries the JOB_NO.

The JOB_NO can be anything A123245 or 9999 or ABCD (it is the number given to the client by their various clients and can therefore be repeated. The JOB_NO is not the key

JOB_NO is a Btree index

If I do a LIST TABLENAME JOB_NO WITH JOB_NO STARTING WITH "V"

and I know that V1234 and v3456 exist, it says no rows found. In fact if I do BTREE.EXTRACT on V1234 it doesn't find it

If I do a LIST TABLENAME JOB_NO WITH JOB_NO STARTING WITH "D"

and I know that D1234 and d3456 exist, it finds the rows

Why?

I removed all indexes and had some remnants in !TABLENAME so I deleted it. I then rebuilt the indexes - No better.

I took a copy of the table and on my standalone PC had the same faults. I set the Dictionary type to VARCHAR (it was blank before)and removed and added the index - It worked. Did the same on their network - No Better.

Is there a quirk I am missing?

Anybody had something similar?

TIA

Claude

Claude,

I have found many interesting things with indexing. Some interesting stuff is right here in the discussions. Search and you will see some of them.

The one most unplesant thing the BTREE indexing does is that it does not consider a string variable to be a string variable. What I mean is that the string variable "0123" sometimes will turn into "123". Rather than being left as a string variable of "0123" it becomes the number 0123 or in reality 123. And several discussions have pointed out that the only way to stop that numeric conversion is to prefix the string variable "0123" with a character. Kinda like "A0123", making the string variable non numeric.

Another is the update process. I just do not like it. There is no option to update the index at the same moment the row is updated in the table. If there is one I have never found it or made it work. I believe it is designed around having a workstation devoted to index updates. UPDATE_INDEX sometimes does not work properly. And UPDATE_INDEX seems to be messy. Somehow there is like "debris" in the "!" index file.

So the problem you are describing about BTREE indexing doesn't amaze me. Maybe others can actually help you fix your problem. I am interested in this problem you are having, so I will be watching the replies you get.

I gave up on it and created my own indexing utility and MFS. Had to handle processing of OPEN, WRITE, DELETE, CLEAR_FILE, DELETE_FILE, CREATE_INDEX, DELETE_INDEX, REBUILD_INDEX, and SELECT_INDEX.

Two things come to mind. 1st, is your field length 5 characters greater than the longest actual value? 2nd, if you have more than one copy of the system, the same volume name with different locations tends to screw up the indexing - at least it does for me. Finds keys that don't exist and dies when it can't read the record(s).

Richard,

As I can see, you are the kind of person like me, you prefere to do most of things yourself.

(With GFE in !file you are probalby right, but I have no experience with INDEX REBUILD)

The problem we are answering to is probably forbidden charecter in some key (between D* and V*). The forbidden characters are @RM,@FM,@VM,@SVM … . Run loop through all the table keys and test such characters first. It was discussed here several times, I do it too.:)

Index rebuild does not correct the problem.

Other symptoms are that BTREE returns records in adressed record neigborhood.

Thanks for your suggestions,

I've done the following (and it still hasn't worked)

Checked the table for @VM, @svm etc. - Found 1

Removed all indexes, yet !TableName still existed

Deleted Table !TableName

Adjusted the Dictionary so the JOB_NO field was smaller in length (the field was 20 chars long, I reduced it to 10 chars as they don't have any data bigger than 10 chars)

Rebuilt just the JOB_NO index

Two subtle things I noticed, which may or may not be of relevance

In the !TableName there is a record with @SVM in it

The Key is an integer but if I list by @id it sorts it as though it was a string.

Any help/suggestions will be gratefully accepted

Claude

You neednot delete the !file.

You must rename record with @id contining @VM/@SVM.

After this!!! you can proceed with reindexing.

Sorry I wasn't clear on the previous submission,

I found an @id with a @vm etc. and deleted the record, then I went through the index building.

So in effect I started with a 160,000 record clean database with no indexes, and built the first index.

Claude

OK,

but sorry, I cannot gues what causes the problem now.

If I remember it well, the @VM,@SVM and so on … are usual in

!tables and their keys.

I took a full backup of their data (today) and put in on my network this time (last time it was on a stand alone). I re-built the index and it worked fine.

Could this fault be in any way Machine specific? Should I look there? If so any areas of interest?

Again, any help gratefully accepted

Claude

View this thread on the forum...