Create and use functional indexes in Informix

As data volumes grow at an alarming rate, the database management system continues to focus on performance issues. This paper mainly introduces a performance tuning technique named function index (functional index). Creating and using functional indexes based on statistical information about database usage can significantly improve the performance of a select query. Learn how to create and use functional indexes in Ibm®informix®dynamic Server and maximize query performance.

Brief introduction

Performance is a key consideration when choosing a database management System (DBMS). Many factors can have an impact on performance when you perform Select, INSERT, UPDATE, and DELETE operations. These factors include:

The speed and size of persistent data storage

Data storage structure

Data access Methods

As datasets become larger, query performance becomes increasingly important.

In general, using indexes can improve query performance. The index associates the row position in the database with a set of ordered data subsets and/or data-sending organisms. Indexes reduce the number of rows (or tuples) that a DBMS checks when it executes a query, thereby gaining performance gains. Sometimes a query can be completed only by searching the index without retrieving any tuples (tuple) from the table. For example, if you have an index in the column C1, and you issue a query select C1 from T1 where C1 < 10, the index contains all the information that can satisfy the query.

Interestingly, the ANSI SQL standard does not show how to create, implement, or maintain an index. Therefore, database vendors are free to implement indexes in their own way.

This article discusses the function indexing features of Informix Dynamic Server. To understand the concepts covered in this article, you need to be familiar with basic database terminology and concepts such as schemas, tables, rows, columns, indexes, and scalability. You also need to understand the basic configuration of Informix Dynamic Server (IDS) and how to start and stop the server, and how to configure using the ONCONFIG file. In addition, you need to be familiar with basic SQL commands and how to use dbaccess to execute SQL commands on the server.

The purpose of this article is to help you understand the definition of functional indexes and how to use them. In addition, you will learn how to create and use functional indexes, as well as some of the issues to consider before creating functional indexes.

Advantages of functional indexing

Indexes save column values in some order. The function index converts the data in the column and saves the converted values in order.

Suppose a table holds an employee name for an enterprise, and you need to preserve the case of the name. Then, if the query needs to perform a case-insensitive search (as shown below), you must convert the data:

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

If no index is established for the name, the DBMS performs a full table scan and applies the ToUpper function to the name column of each tuple (tuple). To determine whether a tuple (tuple) satisfies a query, you must call the ToUpper function. Performance will decrease when the table is very large or when a large number of sessions emit this type of query.

One way to avoid calling the ToUpper function is to save both case-mixed names and uppercase names in the table. Application query case insensitive columns:

SELECT * FROM t1 WHERE ucname like 'ANTHONY % HOPKINS';

If an index is not created for Ucname, the DBMS still performs a full table scan, but does not further process the data to determine whether it satisfies the query. While this improves performance, it is not an ideal solution because the tables are very large and all applications that need to manipulate or access data must include the logic to handle ucname.

A better way to improve query performance is to create a function index on name:

CREATE FUNCTION toUpper( name VARCHER(100) ) RETURNS VARCHAR(100)
WITH (NOT VARIANT);
RETURN upper( name );
END FUNCTION;
CREATE INDEX ucnameIndex ON t1 ( toUpper(name) );

When this query is executed, the DBMS can use the function index to determine which tuples (tuple) satisfy the query. The DBMS gets and returns only the tuples (tuple) that satisfy the query, as shown in the following list:

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

The DBMS will automatically manage function indexes and applications that do not need to contain logic to manage uppercase data. By updating the index by using the INSERT, update, and DELETE operations, the DBMS is able to ensure that the index is always consistent with the table data.

Next, we'll delve into how to create and use functional indexes, and how to build and validate query plans, and provide concrete examples.