Create Index anomoly

Opto_Will

Has anyone had the experience of a CREATE_INDEX turning the field into a SYN (as viewed in Table Builder).
e.g. Field "2 " no longer exists. Instead Field "SYN2" exists. Type , etc remains the same so I am not sure why it created a Synonym.

I am using the Command:
INDEX_TYPE$ = 1 CASE$ = 0 BUILD_NOW$ = 1 Create_Index(INDEX_TYPE$, IndexTable, IndexColumn, CASE$,BUILD_NOW$)

Background:
I am adding several indexes programatically but the above happens.
Not on every index I created but consistenly in the same subset (1-4 of the 8 I create) in my testing. I have even removed and recreated the affected fields in Table builder as a just-in-case.

If I then Delete_Index (or manually untick the Btree box in Table builder) the SYN remains.
However, If I wipe the whole Table Index (via instructions found at Removing Indexes Manually)
The table 'reverts' to normal. Field "2" is back and "SYN2" is gone.

Ticking BTree on in Table Builder or adding Indexes via DB Manager have never displayed that behavior (as far as I know) so I didn't expect the Create_Index function to.

DonBakke

Are you saying the original field name is literally "2"? While that does not appear to be an illegal field name (at least the Table Builder doesn't complain), I cannot ever recall seeing a field name that is just a number. Typically we see names like F2, F3, etc.

That said, I have never seen the behavior you described. I wonder if there is logic within Create_Index that does not like field names that are strictly numbers and thus it creates the synonym to be safe. Just a guess at this point.

Opto_Will

Sorry @DonBakke, yet again I have not explained myself clearly.
Its not the field name but the position.

Maybe this example will help:
Before: Position ColumnName [Indexed] 1 CLIENT_CODE 2 ENTER_DATE Create_Index("1"", "CLIENTS", "ENTER_DATE", "0","1") After: Position ColumnName [Indexed] 1 CLIENT_CODE SYN 2 ENTER_DATE Y

All the details of the original non-synonym entry are the same in the new entry (Name, DataType, S/M, Length, etc...). Only the position pointer of 2 has now been replaced by 'SYN 2"

DonBakke

@Opto_Will - Thanks for the clarification.

I agree that this is still a bit odd. I wonder if there is a mismatch between the dictionary field itself and %FIELDS%. Synonyms are identified if position 28 (DICT_MASTER_FLAG$) has the value of 0. However, IIRC, the Table Builder displays its data from the %FIELDS% summary record. The master flag is in the AMV field 14 (FIELDS_MASTER_FLAG$). When actions are taken to update the dictionary fields then %FIELDS% gets updated since the dictionary field is the source of truth.

The only way to prove out this theory is to pull a backup and do an inspection.

Opto_Will

I have done some testing comparing a Baseline (unindexed Table) with indexes created both with TableBuilder and Indexes created programatically with the Create_Index function. I will present my findings for those interesting in reading.

I used two fields, one a varchar and one a date, to index. I compared a full parse of its %FIELDS% values as well as the Dictionary field's <28> entry as noted by Don above, which, as you would expect, is mirrored in the SYSCOLUMNS entry.

My distilled results were:
1. The %FIELDS% records contained 3 differences between indexed and not. The Indexed row<6> toggled to 1 for the indexed fields in both tests. A new row NULL_TRANSACTION<20> now contained data 0 for the indexed columns when compared to the baseline (which is empty). The final change, DESCRIPTION<14>, was a value of 1 for the baseline and the TableBuilder Index but was 0 for the Varchar Create_Index field and "" for the DATE Create_Index fields.
2. The DICT<28>record mirrored the DESCRIPTION<14> record noted above in the tests.
3. In both fields, Create_Index fields showed as a Position of "SYN x" where the baseline and Tablebuilder testes stayed as just 'x'

Here is a table with my results:
Baseline TABLE BUILDER CREATE_INDEX Example 1: CLEAR_DISP <%FIELDS%-25> CLASS <1> - - - ORDINAL - - - HEADING CLEAR_DESP CLEAR_DESP CLEAR_DESP REPEAT F F F KEYPART 17 17 17 INDEXED<6> 0 1 1 *Expected CONVERSION 0 0 0 FORMULA "" "" "" JUSTIFICATION 0 0 0 DISPLAY_LENGTH L L L VALIDATION 20 20 20 GENERIC_TYPE "" "" "" SOURCE "" "" "" DESCRIPTION<14> 1 1 0 # ? FOREIGN_TYPE - - - FOREIGN_NAME - - - FOREIGN_MAP - - - FOREIGN_ATTRIBUTES 0 0 0 PHYSICAL_MAP_DOMAIN "" "" "" NULL_TRUNCATION<20> X 0 0 *Consistent DICT.CLEAR_DISP<28> 1 1 0 SYN? No No Yes Example 2: Date <%FIELDS%-64> CLASS <1> - - - ORDINAL - - - HEADING DATE DATE DATE REPEAT F F F KEYPART 2 2 2 INDEXED<6> 0 1 1 *Expected CONVERSION 0 0 0 FORMULA "" "" "" JUSTIFICATION 0 0 0 DISPLAY_LENGTH R R R VALIDATION 11 11 11 GENERIC_TYPE D D D SOURCE "" "" "" DESCRIPTION<14> 1 1 "" # ? FOREIGN_TYPE - - - FOREIGN_NAME - - - FOREIGN_MAP - - - FOREIGN_ATTRIBUTES 0 0 0 PHYSICAL_MAP_DOMAIN "" "" "" NULL_TRUNCATION <20> X 0 0 *Consistent DICT.DATE<28> 1 1 "" SYN? No No Yes

I then tried to programatically detach/reattach the tables. The DICT<28> and %FIELDS%.DESCRIPTION<14> values were now consistenet with a Table Builder index whene using Create_Index but the fields were still showing as SYN.

Then I deleted the '%PROTECT.SPEC%' and '%FIELDS%' records before a delete/reattach and teh SYN was gone. There was still a difference, however, because the %FIELDS%.NULL_TRUNCATION<20> record no longer had any values. It quickly reappears if I toggle an Index field in Tabel Builder.

So, I can get rid of my SYNs but its not perfect.
1. I feel i should retain the NULL_TRUNCTATION entries. While non-existance might be treated like a 0, my ultimate goal is always to have a programatic solution mirror exactly what would have happened if human processed.

2. Considering various use cases , its not ideal to have to delete DICT values and reattah tables on the fly to rebuild them when other users are likely active. It seems that is why Create_Index and Delete_Index likely exist.
As an example, If I have a process that gets run irregularly (say once a year) but would REALLY benefit from an index but nothing else would use that index, it seems inefficient for the system to actively maintain an index for that field, no matter the minimal overhead in processing time and disk space.
A simple index on the fly is a perfect solution:
Create_Index Select Data and process based on Index return Delete_Index (because it wont be needed for another year)
Mind you in this particular scenario I probably wouldnt care because, based on my results, I think the Delete_Index would undo the Create_Index correclty and the end result would be as it was originally, despite being a Synonym for the duration.

This is maybe only an issue if you wanted to create an index permanently/semi permanently (like in one of my use cases). Once again, probably just cosmetic because the SYN points to the right position and maintains the corret field name and definitions so should behave correctly regardless.

I admit I dont fully understand the system processing differences of of 'real' field definition vs 'SYN' but normally we would have both 'x' and 'SYN x' with different names or maybe even DataType. Therefore, I would expect it to be not much more than a redirection under the hood so no big deal.

Anyway, thats all for now.

TL;DR Create_Index does not behave exactly like Table Bulder or DB Manager in creating indexes but the differences will likely not affect you.

Edit: I should point out again that this was not for every field I programatically indexed in that table. I still dont know why this is the case.

DonBakke

TL;DR Create_Index does not behave exactly like Table Bulder or DB Manager in creating indexes but the differences will likely not affect you.

This is where I would use hooking to figure out what is different. Obviously the Database Manager UI uses code, and I'm 99.9% certain it uses the same routines you are using, but perhaps with slightly different arguments or perhaps it is calling other code in advance. This would also be a good use of profiling to see all of the calls being made.