Relational index rebuild

Michael

Client has a number of relational indexes (based on symbolics - not my idea). One is a rel index from the core table to another table; another is a rel index from that 2nd table to a 3rd table.

The indexes appear to be corrupt. Do I need to rebuild each separately? I know rel indexes are supposed to update automagically. Or do I just do the first one and the 2nd and 3rd take care of themselves?

These are also Huge tables; 30 million+ in the first table.

Thanks in advance!

DonBakke

Actually, building a relational index on a symbolic can be a powerful solution. It allows you to make conditional relational indexes. If @ANS = "", then no relation is created. But that is an academic issue, not terribly important to your query.

Is the relational index between the 2nd and 3rd tables dependent upon the relational index between the 1st and 2nd tables? If so, then you might need to consider rebuilding all of them. Otherwise, I would just focus on the first relational index.

Michael

Yep, totally dependent. The 1st table has 4 relational indexes into the 2nd table. The 2nd table is indexes into the 1st.

Table 1 has 30+ million records. Table 2 has almost as many, as does table 3.

I'm guessing start with table 1 -> 2, then 2 -> 3? But I thought relational indexes were supposed to automagically update? Do @dict, @id, @record need to be set since it involves symbolics?

DonBakke

It's hard to give you a definitive game plan without knowing details about these specific symbolics, the relational indexes, and the different tables. Does anything else populate rows in Table 2 and Table 3 other than the relational indexes? Does anything else populate other fields in these tables?

It might help you to remove the "automagical" label and just analyze it from the standpoint of an MFS (SI.MFS in this case) making changes to the target tables as needed. If you are clear to remove all rows from the 2nd and 3rd table , then rebuilding the relational indexes in the 1st table should re-generate the target records in the 2nd and 3rd tables. But if those target rows have non-relational data in them, then this will be require a different strategy.

Something else to keep in mind is that if the source record no longer references a target key, that target key does not get removed from the target table. It will no longer have the source table key within it, but it is very possible for target tables - especially those that only get populated due to relational indexes - to have many empty records. This would be another reason why it makes sense to clear out Table 2 and Table 3. It will remove all unused keys.

Michael

So clearing table 2 and 3 will trigger a rebuild?

Everything in those tables is generate by the relational index.

Michael

Will I need to force a rebuild, or will the indexer just pick it up?

DonBakke

So clearing table 2 and 3 will trigger a rebuild?

No.

Everything in those tables is generate by the relational index.

I would remove the relational indexes off of all tables. I would then clear Table 2 and Table 3. I would then add the indexes between Table 2 and Table 3 first (so they are ready to trigger) and then add the indexes between Table 1 and Table 2. When these latter indexes get rebuilt, this will populate the keys in Table 2 and the relational indexes will then populate the keys in Table 3.

Will I need to force a rebuild, or will the indexer just pick it up?

You will want to close the indexer before removing the indexes. You can then restart the indexer so it keeps doing its job for the other indexes. After all of your relational indexes have been added back and complete, restart the indexer again.