Indexes and index performance considerations
Informix® provides several types of indexes. Some performance issues are associated with indexes.
- Types of indexes
Informix uses B-tree indexes, R-tree indexes, functional indexes, and indexes that DataBlade modules provide for user-defined data. The server also uses forest of trees (FOT) indexes, which are alternatives to B-tree indexes. - Estimating index pages
The index pages associated with a table can add significantly to the size of a dbspace. - Managing indexes
An index on the appropriate column can save thousands, tens of thousands, or in extreme cases, even millions of disk operations during a query. However, indexes entail costs. - Improve query performance with a forest of trees index
A forest of trees index is an alternate indexing method that alleviates the performance bottlenecks and root node contention that can occur when many concurrent users access a traditional B-tree index. - Creating and dropping an index in an online environment
You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment, when the database and its associated tables are continuously available. - Improving performance for index builds
You can improve performance for index builds by adjusting the PDQ priority and by allocating enough memory and temporary space for the entire index. - Storing multiple index fragments in a single dbspace
You can store multiple fragments of the same index in a single dbspace, reducing the total number of dbspaces needed for a fragmented table. You must specify a name for each fragment that you want to store in the same dbspace. Storing multiple index fragments in a single dbspace simplifies the management of dbspaces. - Improving performance for index checks
The oncheck utility provides better concurrency for tables that use row locking. When a table uses page locking, oncheck places a shared lock on the table when it performs index checks. Shared locks do not allow other users to perform updates, inserts, or deletes on the table while oncheck checks or prints the index information. - Indexes on user-defined data types
You can define your own data types and the functions that operate on these data types. You can define indexes on some kinds of user-defined data types.
Parent topic: Performance Guide