Hi everyone

My colleage has been working with ARev since Adam was a boy and when he was younger had some bad experiences with Relational Indexes. I am the "Johnny come lately" who has only been working with OI converting the app from ARev. Historically, our app has steered away from using R. Indexes (we wrote code to update the key list of the related file), but as we have some major redevelopments coming up, we would like to receive some warm fuzzies from the Rev Community that R. Indexes are a good thing to get involved with. What pitfalls need to be avoided? What do we need to know to be on top of them from day one?

Feedback from lots of people would be appreciated.

Robert,

Several developers say relational indexing can't be trusted. I haven't had much experience with them myself.

Another problem is that relational indexes can get full, since the indexed keys are stored in fields in ordinary tables. In my main app I have a very useful place for a relational index - master-detail relationship, but had to leave it out since many of my clients have data that would cause the index to break - often just one single row in the detail table.

The only place I use relational indexes is a couple of static tables.

- Oystein -

Robert ..

I use Relational Indexes for tables that don't get changed too often. Remember that Relational Indexes are stored in a field in a table so that the 64K limitation applies (to the TOTAL record not just the index). There are a couple of static tables that I use Relational Indexing on (ZIP Codes, States, Cities, for one). I also use it for Customer Tables when there are Contacts to be maintained and Shipping Info. Otherwise, B-Tree extract can extract keys from a related table pretty quickly.

Trying to maintain a relational index on a symbolic is very problematical. For that matter, even a B-Tree can be troublesome on symbolics.

Don Miller

C3 Inc.

Don, Oystein

Thanks for your thoughts. Sounds like the BTREE route may be a better way to go for us in this case.

I don't understand why some developers say there are problems with Relational Indexes. Just understand the rules of their creation and their limitations fall from that. Since I come originally from using Rev F I used Relational Indexes (then known as Inverted Lists) as often as I can in ARev and OI.

Rules of their creation (someone add if I miss any): Rows are created or updated using the value of a column (which can be multi-valued) in the source table. The column data of the target table is always multi-valued. Empty values do not create a row with a null key in the target table. ARev and OI will immediately update the target row unless it is locked, then it will write an index transaction.

Limitations: Since they update a row in the target table that row cannot exceed 64k characters including the index, delimiters and other column data. Maximum number of rows in the source table are thus around 6000 rows or less that all contain the same value.

Efficient usage: Say you have a table with a STATUS column. There are several values to STATUS, but most expire as "Done" and a small percentage are "Active". So, if I want immediate access to a list of "Active" rows without maintaining a b-tree I just create a symbolic that returns "1" if the STATUS is "Active" and null for anything else. Then I get the active row key list in one read of the Relational Index row of "1".

I advise you to create a parallel table to house the relational indexes for a main table. Then, you won't have the locked row problem. e.g. main table is named FIRMS with sequential keys. The table used for indexes is FIRMS_EXT with the same sequential keys as FIRMS.

In closing I will admit that updating / rebuilding relational indexes is tedious in tables with a large number of rows. First, the target table must be stripped of all values, then the source table is read and creates values in the target table. Remember to sizelock the target table before you start a rebuild.

Dave

Thank you Dave. There are some very useful tips in there. A couple of further questions…

You say updating / rebuilding indexes is tedious. Can you not just use the Database Manager to rebuild, or are you talking about writing your own routine? Referring to sizelock, I assume you are talking about a routine which rebuilds an index on a network?

I especially appreciated your tip re a status column and parallel tables. They may be very useful for us.

View this thread on the forum...