Uniface 10.4 + Informix: Simple Guide to Index Management 📚

#uniface #informix #indexes #performance

🤖 This blog post was created with the help of an AI assistant and is based on the Uniface 10.4 documentation section “Index Management on Informix”.

When you work with Uniface 10.4 and an Informix database, indexes are important. They help Informix find rows faster and keep your application responsive, even when tables grow large. 🙂

What is an index in Informix?

An index is like a sorted list for your table. Instead of scanning the whole table, Informix can use the index to jump directly to the needed rows. This can make read operations faster, especially on big tables.

An index is stored in a special area in Informix, called a partition.
Depending on the system configuration, each index partition page is 2 KB or 4 KB in size.
The upper limit for the number of indexes per table in Informix is 100.

In Uniface 10.4, non-unique indexes can be created at table creation time. They are created either “on the fly” when Uniface creates the table, or by running SQL scripts generated by the Create Table facility. This means you do not always have to write all index statements by hand. 🚀

Non-unique indexes in Uniface and Informix

A non-unique index does not enforce that all values are different, but it can still speed up queries that search or sort by the indexed fields. The Uniface documentation states that non-unique indexes are created when the table is created.

Non-unique indexes are useful for fields that can have duplicate values, for example a status code or a foreign key that appears many times.
They are defined in the Uniface model and are created in Informix when you create the table using the generated SQL or the on-the-fly mechanism.

Because they are created from the Uniface model, it is important that your Uniface entity definition correctly lists the indexed fields. If you later change the index definition in Uniface, you usually need to regenerate and run the SQL script so the database structure stays in sync. 🔄

How Informix stores index data

Informix uses a storage structure called a partition to store index information. This is where the index pages live and are managed by the database server.

The partition page size for indexes is typically 2 KB or 4 KB.
There is a hard limit of 100 indexes per table in Informix.

If you create many indexes on one table, write operations (insert, update, delete) can become slower, because Informix has to update multiple index structures each time. So it is usually better to create only the indexes that your queries really need. ⚖️

How index names are built

The Uniface 10.4 documentation defines how index names look in Informix. Index names consist of the table name followed by an index number.

Pattern: TableNameN (table name + index number, without a separator).
Example: if your table is called test and the index number is 2, the index name is test2.

This naming rule makes it easy to see which indexes belong to which table. When you analyze performance issues, you can quickly map index names back to your Uniface entities and key definitions. 🔍

Simple example: Customer table with indexes

Let’s look at a small example to make this more concrete. This example is not from the documentation, it is only here to explain the concept.

Table: CUSTOMER

Primary key: CUSTOMER_ID
Non-unique index: CITY (for searches by city)
Non-unique index: LAST_NAME (for searches by last name)

In Uniface, you define the key and index fields in the entity definition. When you let Uniface create the table in Informix, it can create non-unique indexes for CITY and LAST_NAME based on that definition.

In Informix, the indexes could look like this:

CUSTOMER1 → index number 1, for CITY
CUSTOMER2 → index number 2, for LAST_NAME

Now, when your Uniface application runs a query such as “all customers in Berlin” or “all customers with last name Smith”, Informix can use these indexes to find the rows faster. ⚡ You do not need to manually design the index names; Uniface and Informix follow the naming rule for you.

Tips for working with indexes in Uniface 10.4

Keep your Uniface model and the Informix database structure in sync by regenerating and applying scripts after changes to keys or indexes.
Do not create more indexes than you need. Focus on the fields that are most often used in where and order by clauses.
Remember the Informix limit of 100 indexes per table, even if you will rarely hit this limit in real systems.
Use the naming rule (TableNameN) to quickly identify which index belongs to which table when you analyze performance or troubleshoot issues.

If you want to optimize performance later, start by checking which queries are slow and which indexes exist for the fields used in those queries. Often, one well-designed index gives more benefit than many small or rarely used indexes. 💡

✍️ This article is a simplified explanation of the “Index Management on Informix” section from the Uniface 10.4 documentation, written with AI assistance to make the topic easier to understand.