OI 7.21

Added a new relational index, deselected the immediate build. Used update index in database manager, selected "update. Process was too quick and did not do anything. Selected "rebuild" and index did rebuild. The problem is the index did not build when selecting "Update" on a newly created index.

Not sure if this has been fixed in 8

John

John, if you want to build the new index you must select immediate build when adding the index. Update index only adds indexes created since the index was added. This is how it worked in 7.x and 4.x.

but if you ran UPDATE_INDEX it would

Perhaps I am missing something here. If you run update index on any index without the rebuild flag the index will look at the ! file for any indexes waiting to be updated and update them. If you run the update index with the rebuild flag on it will read all the records in the table and rebuild them. What is it that leads you to believe that the update index without the flag will rebuild a newly created index.

Ok… perhaps I wasn't clear.

If you add an index in OI and uncheck the Immediate build doesn't it flag the index to be built the next time a flush is encountered?

I expected the index to be built when I did an Update from the Database Manager -] Utilities -] Indexes -] Update -] Select the index -] Click the "Update" button. I thought that would be the same as issuing an UPDATE_INDEX "table", "index", 0

John

To further reinforce what Barry mentioned. The Update_Index routine does rebuild a newly added index. The "Update" button on the Index Update form does not.

John

Well it appears to only be a problem with Relationals. I decided to try it in 8.01 and found additional problems.

After adding the index I tried to issue the "RUN UPDATE_INDEX 'table','index',0. Then tried to call up the records in the ! table by using Open Record in the System Editor. The Editor breaks to the debugger with error ENG0010: RTP9 Line 308 Variable has not been assigned a value!!!

John

Is this the old editor or the new editor? RPT9 is open, which seems to imply some form of file handle caching.

Also, while there is a !FILE for relationals, the system does not store data information there except for pending transactions when the destination record is locked. All that's stored is index definition information.

The Sprezzatura Group

World leaders in all things RevSoft

The RTP9 error did not occur in 7.21 doing the same thing. It was in the old System Editor as this has the EXEC line.

BTW, I went back and rechecked and the Relational issue is a little more complicated. If I did RUN UPDATE_INDEX "table","index",0 the index did not build. Same goes for using UPDATE in the Database Manager. However, if I use RUN UPDATE_INDEX "table","",0 it did build the index. Verry strange!

Perhaps the index specific update does not process Relationals? I seem to remember this problem but I thought it was only an issue if the field also had a Btree! Rechecked 8.01 and same thing happens!

Is it just me or does this indexing really really need to be looked at! I mean, here we are at 8.01 and we are still having indexing issues! Just my two cents.

Thanks,

John

]

Did you just put in the name of the field, or the fully qualified name of the relational index (something like "related_file*related_field*order")?

Relational indexes require the full name to index/rebuild unless you do "all" in the table. You can find the full name in field 23 of the dictionary record.

Thanks Karen,

News to me… the documentation for Update_Index shows you should use column_name. Either the documentation is incorrect or there is a bug in Update_Index. I am leaning towards the latter since using the Update button within the Database Manager has the same effect.

John

It's been this way since arev days – docs are correct, they just don't make it clear how to find the name of a relational index.

If you put a btree, crossref and relational index all on the same field - - you get three DIFFERENT names for the indexes that have to be used if updating/rebuilding explicitly.

Thanks Karen,

That brings back memories… Yes I remember doing something similar in AREV but I thought that was changed in OI? I did a search and found an old thread discussing exactly how to get around another issue:

http://www.revelation.com/__85256DB80017688B.nsf/0/6757925BB112CA3185256AEA0071F03E?Open&Highlight=0,UPDATE_INDEX,RELATIONAL

If I understand this correctly, you call UPDATE_INDEX "table","target table*source field*sort",0

I tried this exact syntax and the index will not update. It also will not update using the database manager.

Thanks again,

John

]

Yes, so long as 'target table*source field*sort' is what is in field 23 of the dict record. Case is important as well.

For example, I just cleared a backup table, EMPLOYEE_ACCT_SUMMARY_ARCHIVE (to make it easier to watch when indexing occured).

I created a relational index in Check_Archive that points to the cleared table. As you say, the update feature in the DB doesn't work (it is hard coded to see btrees only - which is why I have my own routines for update/rebuild). I confirmed this by checking to see that the table was blank after running the update. I next ran this command (I cut and pasted the index name from the dictionary – the pgm I use does essentially the same, but for this test I only used the OI command):

Update_Index "CHECK_ARCHIVE", "EMPLOYEE_ACCT_SUMMARY_ARCHIVE*CHECKS*DR", 0

When I checked the EMPLOYEE_ACCT_SUMMARY_ARCHIVE table after running this (which took a couple of seconds), there were numerous records, all with a nice little relational index into them.

Now, off to remove the index and clear the table again …

Karen,

You are spot on! I just double checked and I was not using the exact syntax that is in the *INDEXES row for the Relational. It now builds using Update_index.

It still does not resolve the bug in the Database Manager where it can not update Relationals.

Thanks again,

John

View this thread on the Works forum...