Abstract
Indexes can be created on the result of a function applied to columns in a table. That can be useful in a variety of ways, such as fast case-insensitive searching on character data. There is also a particular solution explored using built-in function “ifx_checksum” for very compact indexes on columns wider than normally allowed.
Content
Examples in this article were produced using the provided “stores_demo” database in a Docker container from informix-dockerhub with Informix Dynamic Server (IDS) updated to 14.10.FC11.
Functional indexes are documented here. For example, you might want to search rapidly through a character column beginning with supplied text in either lower or upper case (without installing the Basic Text Search extension). The following table was created to test this, with alternate rows upshifted:
This SQL uses built-in function UPPER to get the required results:
The results are:
However, the query plan is:
That would be a problem on a large table as it would scan all rows.
You might then try and fail to create an index on the built-in function:
You can only create an index on a user-defined function. This can be a stored procedure language (SPL) function, so we can create a simple wrapper function:
- You must declare the function as invariant, meaning it will always return the same output for given input.
- Index keys have a limit on the total number of bytes, so set a sensible length on the input and output.
- Use TRIM in case the intput data is CHAR to discard trailing spaces.
We can now create a functional index:
Retesting as follows returns the same results:
This time, the query plan contains:
Having seen the basics of functional indexes for fast case-insensitive queries, we can now look at the second purpose of this article. What if we need to index a wide column set which might require excessive disk space and/or exceed the allowed limits? The following example on the “stores_demo” database is based on a real case where a unique index was reduced from 14GB to 1GB.
The “stores_demo” database contains a table with the following schema:
Note that LVARCHAR is equivalent to LVARCHAR(1024).
Firstly, we might want to make “warehouse_id” a unique SERIAL number as probably intended:
Let’s say we then want to make “warehouse_name” unique. The obvious solution fails:
More details on any error number can be obtained with “finderr” as in this case:
Fortunately, there is a built-in function “ifx_checksum” we can use that is described here:
https://docs.deistercloud.com/content/Databases.30/IBM Informix.2/Tips/Checksum.xml?embedded=true
IBM and HCL Informix documentation refer to it in the context of Enterprise Replication, but does not otherwise cover it.
We can create the index with:
- The index cannot be unique as there could be checksum collisions, so uniqueness must be enforced by triggers.
- IDS 11.70+ supports the ONLINE keyword to create an index while the table is in use.
If you are creating more than one checksum index, you might need different variations of “sp_checksum” with varying data types and numbers of parameters. Those with more than one would have a combined checksum expression as in this example:
Once you have functional indexes, you will be prevented from dropping the function:
Experience shows that a separate function to check rapidly whether a row already exists is more useful in a separate function, so it can be used both in triggers and applications:
- There should be a parameter for each column in the index (one in our example).
- An optional last parameter is needed for triggers to ignore the row being checked.
- Optimizer directive +INDEX ensures the checksum index will be used.
- The warehouse name must match as well as the checksum in case of collision.
- A third condition is present to ignore the specified row if provided for triggers.
We can now create the trigger procedure:
The triggers are then as follows:
TIP: set undocumented parameter NONEXCLTRIG so you can drop or create triggers while the table is in use.
You should now find that you cannot create duplicates in “warehouse_name”:
Conclusion
The techniques in this article can provide fast fuzzier searches, and massively reduce the space required to index wide column sets.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
Contact Us
If you have any questions or would like to find out more about this topic, please contact us.
