Setup > Usage > Data > Database optimisation 

The softwares in technology Adonix provide in the standard table dictionary indexes designed to manage integrity constraints (by unique keys) and to give a in most cases a good response time (standard indexes used for the left lists, the large batch tasks, standard queries). These indexes are carried by 1 to 16 fields. In the indexes of this type, it is not possible to use an indexed field with an index greater than 1. Thus, for example, it is impossible to create an index on the second line of an address in the dictionary .

In certain specific cases, it is useful to use the setup to create the indexes in order to achieve a specific optimization (for example in a report or to optimize a complex inquiry). A typical example of this is the addition of an index to a large table to accelerate the creation of data in an inquiry created by the requestor, or the use of a dedicated index in the object setup, to use a specific order in a left list.

This function is used to create indexes with the following properties by means of setup :

  • They must not be used explicitly in a program, but serve to accelerate the requests when the index normally used is not selective.
  • It can be used in the setup of left lists, in order to respond for example to the acceleration of the requests when filters are frequently made on certain fields. These filters can be for example, filters generated by role management or filters used by the Selection function.
  • They may correspond to indexed fields (while the standard indexes described in the table dictionary cannot). A example of the usage would be the addition of an index on certain dimension types (the dimensions are the indexed fields, something which is prohibited in a standard index; in addition taking into account the number of existing dimension types, it would be impossible to anticipate which would be the most useful).
  • Homonyms are authorized
  • They can be activated or de-activated on demand

Because it is part of the setup, the function is the means to create perpetual indexes: existing indexes are not updated by new software versions. On the other hand a new software version is likely to add new indexes to the list (but these indexed will systematically be proposed as de-activated).

It is important to understand that these indexes should only serve to optimize standard processes, left lists or inquiries. If an index is required within the framework of a specific/custom development, it is necessary to consider that this index must be defined in the data dictionary and saved by an activity code.

    Prerequisite

    SEEREFERTTO Refer to documentation Implementation

    Screen management

    Entry screen

    Presentation

    The entry of additional indexes is made in a grid, where are defined, table by table the indexes to create:

    Close

     

    Fields

    The following fields are present on this tab :

    Grid

    Specify the table in which the index will be added.

    • Index code (field CODIND)

    The code for a supplementary index should commence with SPE_.

    The index code must be unique .

    • Index descriptor (field DESCRIPT)

    Specify the fields from the table from which the index is composed.

    The fields must be separated by the sign - or +.

    • Active (field FLACT)

    This indicator makes it possible to remove a table index.

    • Comment (field COMDES)

     

    Close

     

    Specific Buttons

    This is used to start the update of the tables by creating the indexes that have been added or activated, but also to delete those indexes that have been removed or de-activated.

    Warning, this phase can be long and costly in machine time, insofar as an indexation on a large table necessitates the reading and construction of the index. In addition, it can also require disk space and pose problems if there is not sufficient space in the database. In this case, explicit error messages will be returned by the database.

    The following fields are included on the window opened through this button :

    Block number 1

    • field OBJET

     

    • field CLES

     

    Block number 2

    • From folder (field DOSORG)

    Use this field to define the folder from which the record will be copied. The possible syntaxes are described in the Dedicated appendix.

    • All folders (field TOUDOS)

    Use this option to copy the record to all the folders defined in the dictionary (ADOSSIER table of the current solution).

    • To folder (field DOSDES)

    Use this field to define the folder to which the record will be copied. The possible syntaxes are described in the Dedicated appendix.

    Close

    This button makes it possible to transfer the contents of the complete table to another folder.

    Error messages

    In addition to the generic error messages, the following messages can appear during the entry :

    Index XXX+YYY+ZZZ+... YYY field does not exist

    The field with the name specified does not exist in the index that is currently being defined.

    Key components already entered for index XXX

    This index already exists as a standard index for the table.

    The code must start with SPE_

    The name proposed for the index is invalid.

    Code already exists in line i

    An index has already been created with the same name.

    Tables used

    SEEREFERTTO Refer to documentation Implementation