Nelson,

The way the index is currently created in UniVerse, it will only ever index the first attribute as CUST.NUM just points to attribute one, so only attribute one will be indexed.

If you just want to be able to get a list of customer numbers for a matchcode you can use the FORM.LIST command to use what you have already, so based on your example

>FORM.LIST MATCHCODE  4537100110WSTLM00000BSHT

2 record(s) selected to SELECT list #0.

Or if you want them put into a traditional UniVerse Index, you try this.

Create a new dictionary

CUST.NUM.MV

001: I
002: LOWER(@RECORD)
003:
004: Customer
005: 10L
006: M

>CD MATCHCODE CUST.NUM.MV
Compiling "CUST.NUM.MV".
LOWER ( @RECORD )

>

>CREATE.INDEX MATCHCODE CUST.NUM.MV
>BUILD.INDEX MATCHCODE CUST.NUM.MV

>LIST.INDEX MATCHCODE CUST.NUM.MV DETAIL
Alternate Key Index Details for file MATCHCODE
File........... MATCHCODE
Indices........ 1 (0 A-type, 0 C-type, 0 D-type, 1 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name      Type  Build    Nulls  In DICT  S/M  Just Unique Field num/I-type
CUST.NUM.MV      I    Not Reqd  Yes    Yes      M    L     N    LOWER(@RECORD)

Details of index CUST.NUM.MV in file MATCHCODE

                              # of Records    Bytes Used
Alternate Key Value           for Key         for Key
58926904                      1               33
59938613                      1               33

Statistics:
                    Number       Records per Alternate Key Index      Size
Index name          of Keys   Average   Minimum   Maximum   StdDev   (in Bytes)
CUST.NUM.MV         2         1         1         1         0         66
>

If you change your select to use CUST.NUM.MV instead of CUST.NUM you can use the index in selects etc.

So two ways for you.

Thanks,

Hi Nelson,

Based on the information provided, the CUST.NUM field only defines attribute 1. Therefore, that is the only attribute that will be part of the index. One option would be make field 1 multi-valued and have attribute 1 contain all customer numbers delimited by a value mark rathre then separate attributes. Alternatively, you could create an I-descriptor which concatenates the multiple attributes into a value marked separated string. That might require a subroutine to build the string based on the number of attributes in the record. Would have to think on how that could be done directly in the I-descriptor expression.

Thanks,

Neil

Hi Nelson,

1/ you must turn the MATCHCODE @RECORD value to mv'ed. LOWER( @RECORD)  is the method. 

CUST.NUM 

001 I 

002 LOWER(@RECORD)

anyway, 

2/ why not indexing your customer file directly on MATCHCODE value ? 

CREATE.INDEX CUST MARCHCODE NO.NULLS 

I hope this help

Nelson,

You obviously already have the logic for calculating MATCHCODE. If the value is stored in the customer record, you can simply follow Manu's suggestion of an index on that field. You could incorporate a simple match code into an I-Descriptor and build an index on that dictionary. You can access a complex match code calculation using an @SUBR call to extant BASIC code in an I-Descriptor.

An advantage of the index on the customer file is that it is not subject to omission for a MATCHCODE update if whatever it is based upon may change (program logic issues likely resolved after 38M entries). It also pushes the calculation closer to the database engine for better efficiency. The downside is that the index will evaluate twice for each customer record update to compare before and after values for a possible index change. If match code is an "assign once at creation and remove upon deletion" calculation, an index on the customer adds overhead for each update.

Following the suggestions of Neil and Jonathan would likely minimize the perturbations in the application code.

Thanks to all the responses.  Very helpful.

I have tested the indexing with all values in attribute 1 but multivalued.  I have tested the indexing with values attributed, but using an I-descriptor LOWER(@RECORD).

Both work as expected.

Considering that our Matchcode file has 37M entries, and many of them have multiple cust# attributes, I am concerned that building the index by either of these two methods "could" create an index with well over 100M entries.  What limits might exist on such an index and what performance impacts might such a large index make on performance.  We need to understand this before determining if an index is worth having.

Nelson

Hi Nelson, 

If you index your CUST on MATCHCODE, the indices file will have at maximum the number of CUST with one value. 

000 MATCHCODE value0

01 CUST id

Then as you explain there is many CUST sharing the same MATCHCODE, you have indice with less Keys than CUST file. 

Do'nt worry about perf it's robust, so be carefull with the time to build the indices.

Then enjoy SELECT, SELECTINDEX, BSCAN etc.

Give it a try, it's surprisingly efficient.

Regards 

If a match code can have multiple customers, the customer probably has, at most, one match code. That would suggest the number of match codes is limited to the number of customers.

Thanks for all the valuable responses.

I built an index using the LOWER(@RECORD) dict itme.  it took anly about 1 hour for 37M matchcodes even using the concurrent option.  However, the disk hit was enormous as shown below.  Selects come back immediately, so it works nicely.  Now we have to decide if this index has appropriate usage in our ERP :-)

Hi Nelson

Nice to read you have good perf. 

Anyway, I do not understand you big file MCODE !

If I understand correctly, you build it from CUST file via a itype which concat some fields and put CUSTid as record values.

Why not build the index directly like this

CREATE.INDEX CUST MCODE NO. NULLS

BUILD. INDEX CUST MCODE

You get the same result and nothing to do for maintenance of index, it's automatic! And one 64bit file less. 

With kind regards. 

Let me explain better.

• The MATCHCODE is built via a subroutine based upon the Name, Address, etc. of the customer.
• the MATCHCODE file has 1-n matching customer numbers listed in it's attribute list
• the INDEX was built on the MATCHCODE file via an I-Descriptor LOWER(@RECORD) 
• There are over 37M MATCHCODE records, many of which have multiple customer numbers associated with it (for example, different family members at same address)

The information I provided was on this index.

Nelson

Hi Nelson,

I understand it. 

My suggest is to create a itype on CUST file calle  'MATCHC' which call your subr building the match code value

The you index CUST file on MATCHC field.

CREATE.INDEX CUST MATCHC NO. NULLS

The content of the index ll be exactly the content of your MATCHCODE file.

Let me know if you need help to build the itype/subr. 

Regards