Rocket U2 | UniVerse & UniData

 View Only
Expand all | Collapse all

UniVerse Indexes

  • 1.  UniVerse Indexes

    Posted 10 days ago

    We have a file that is used to find customers via a name & address formatted "matchcode".  This matchcode file has an index for customer number, as it has 38M entries.  For example:

    MATCHCODE:     4537100110WSTLM00000BSHT
    0001 59938613
    0002 58926904

    Often, there are more than one customer number (attributed) associated with a matchcode as shown in this example.  It seems that only <1> is indexed and not the others (.  Is this a function of how UniVerse indexes, or the dictionary value used or is it a function of how the index was created?

    >LIST-INDEX MATCHCODE ALL
    Alternate Key Index Summary for file MATCHCODE
    File........... MATCHCODE
    Indices........ 1 (0 A-type, 0 C-type, 1 D-type, 0 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         D       Not Reqd  Yes    Yes            S        L       N            1

    DICT MATCHCODE    09:50:32am  10 Jul 2024  Page    1
    Field.........    Type &    Field........    Conversion.. Column......... Output   Depth &
    Name.......... Field.      Definition... Code........     Heading........ Format   Assoc..
                               Number
    CUST.NUM       D    1                                                             CUST.NUM        9L          S

    Interested in suggestions to handle this.

    Nelson



    ------------------------------
    Nelson Schroth
    president
    C3CompleteShop LLC
    Harrison OH US
    ------------------------------


  • 2.  RE: UniVerse Indexes

    ROCKETEER
    Posted 10 days ago

    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,



    ------------------------------
    Jonathan Smith
    UniData ATS
    Rocket Support
    ------------------------------



  • 3.  RE: UniVerse Indexes

    ROCKETEER
    Posted 10 days ago

    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



    ------------------------------
    Neil Morris
    Universe Advanced Technical Support
    Rocket Software
    ------------------------------



  • 4.  RE: UniVerse Indexes

    PARTNER
    Posted 10 days ago

    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



    ------------------------------
    Manu Fernandes
    ------------------------------



  • 5.  RE: UniVerse Indexes

    Posted 9 days ago

    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.



    ------------------------------
    Mark A Baldridge
    Principal Consultant
    Thought Mirror
    Nacogdoches, Texas United States
    ------------------------------



  • 6.  RE: UniVerse Indexes

    Posted 8 days ago

    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



    ------------------------------
    Nelson Schroth
    president
    C3CompleteShop LLC
    Harrison OH US
    ------------------------------



  • 7.  RE: UniVerse Indexes

    PARTNER
    Posted 8 days ago

    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 



    ------------------------------
    Manu Fernandes
    ------------------------------



  • 8.  RE: UniVerse Indexes

    Posted 8 days ago

    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.



    ------------------------------
    Mark A Baldridge
    Principal Consultant
    Thought Mirror
    Nacogdoches, Texas United States
    ------------------------------



  • 9.  RE: UniVerse Indexes

    Posted 5 days ago

    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 :-)

    # ls -la *MCODE*
    -rw-rw-rw-    1 slice    staff    3466161152 Jul 15 15:33 MCODE
     
    I_MCODE:
    total 45408
    drwxrwxrwx    2 slice    staff                    256 Jun 21 2004  .
    drwxrwxrwx    9 slice    sys                    4096 May 21 10:15 ..
    -rw-rw-rw-      1 slice     staff      23240704 Jul 10 10:04  INDEX.000
    -rw-rw-rw-      1 slice     staff                      29 Jun 21 2004  INDEX.MAP


    ------------------------------
    Nelson Schroth
    president
    C3CompleteShop LLC
    Harrison OH US
    ------------------------------



  • 10.  RE: UniVerse Indexes

    PARTNER
    Posted 4 days ago

    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. 



    ------------------------------
    Manu Fernandes
    ------------------------------



  • 11.  RE: UniVerse Indexes

    Posted 21 hours ago

    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



    ------------------------------
    Nelson Schroth
    president
    C3CompleteShop LLC
    Harrison OH US
    ------------------------------



  • 12.  RE: UniVerse Indexes

    PARTNER
    Posted 20 hours ago

    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 



    ------------------------------
    Manu Fernandes
    ------------------------------