Database Indexing

enaio® editor 10.10

A database index of table columns can significantly accelerate queries of large databases.

With database indexing using enaio® editor you can index by default or using your own defined criteria for table column indexing. Once a table column is indexed, the database continues the indexing whenever data is added.

When indexing the database, new index tables are created and existing index tables are deleted and created again. You can also refresh the index tables after extensive editing of the archive or of the object definition file to bring them up-to-date.

You can only index the database if no user is accessing the database. Empty tables will not be indexed.

Using enaio® administrator you can optimize index statistics without having to create new ones.

When you index with standard settings, enaio® editor creates index tables for the following fields:

Character fields with a maximum of 50 characters,

Radio buttons,

Checkboxes,

Date fields,

Number fields.

These criteria for index tables do not always have a positive effect on performance. Therefore, you can create index tables based on your own criteria.

Select Indexing/settings from the Database menu.

The Database indexing window will be opened.

The standard criteria are set by default. These can be recovered via the Restore standard button.

Select the fields for which an index table should be created.

Confirm by pressing OK.

The settings are saved.

Select Indexing/start from the Database menu or click the Database indexing button.

The index tables will be created or deleted according to your criteria.

For character fields you also specify the number of characters. The columns of text fields which are larger than specified will not receive a table index.

The selectivity specifies the relationship between the number of different entries in a field and the number of documents.

It is calculated as follows:

Number of different entries x 100/number of documents

Example: 300 different entries x 100/500 documents = 60%

The higher the selectivity is, the more logical it is to create index tables. For long character fields which usually have a high selectivity, the duration of time to carry out ongoing indexing can exceed the benefits achieved using the index tables for the queries. The criterion of selectivity only makes sense if you have a representative number of documents which you can use to calculate the selectivity.

Field selection and selectivity are combined by a logical AND.

Recommendation: The indexing of character fields which are important to the query – for example, customer name, customer number, etc. – usually makes sense. Generally speaking, the indexing of checkboxes and radio buttons does not make sense.

Indexing can be extremely time-consuming. In the detailed view, the indexed columns are flagged with an 'i'.

Instead of the indexing with enaio® editor you can save an SQL script with the indexing settings and load this script in the database tools where you can then make improvements to the indexing. An SQL script can be created in the Database / indexing menu.

Instead of indexing all tables you can select individual tables and index it via the context menu. In this case, the current settings from the database indexing dialog are used.

In the database view you can select individual columns, and then use the context menu to edit the indexing properties and create and delete indexes.

When indexing using enaio® editor, all indexes which correspond to the naming scheme for the respective object table type are deleted.
For folder tables: 'Sn_' with 'n' being the number of the folder table. Example: The naming scheme for indexes for object table 'stamm11' is 'S11_'.
For register tables: 'Rn_' with 'n' being the number of the register table. Example: The naming scheme for indexes for object table 'register2' is 'R2_'.
For document tables: 'On_' with 'n' being the number of the document table. Example: The naming scheme for indexes for object table 'object22' is 'O22_'.
If you create indexes for the respective table, independently from enaio® editor, the name of the index cannot correspond to the naming scheme of the object table in order to prevent the deletion of these indexes in enaio® editor.