Table Indexes

You can add multiple indexes to any column in a table to better support the queries you plan to run.

It is more efficient to create all the indexes needed before you insert any data into your database. You can drop any of the optional indexes later if you decide you do not need them.

Indexes can greatly improve the performance of searches on the indexed columns. However, indexes take up space within the database and slow down insert, update, and delete operations. This section helps you determine when you should create an index and how to achieve maximum performance from your index.

There are many situations in which creating an index improves the performance of a database. An index provides an ordering of a table's rows based on the values in some or all of the columns. An index allows the database server to find rows quickly. It permits greater concurrency by limiting the number of database pages accessed. An index also affords the database server a convenient means of enforcing a uniqueness constraint on the rows in a table.

• Column Index Types
  Each type of column index is designed to speed processing of a certain kind of data.
• Creating an Index on an IQ System Store Table
  Create an index on an IQ system store (catalog store) table.
• Creating an Index on an IQ Store Table
  Create an index on an IQ store (main store) table.
• Deleting a Table Index
  Unused indexes take up space in your database. Delete a table index if you do not require it.
• Rebuilding a Table Index
  As data is modified in the underlying tables that the indexes operate on, the indexes become fragmented. Rebuild fragmented indexes on tables to improve performance.
• Validating a Table Index
  Validate an index on an IQ system store (catalog store) table. You cannot validate indexes on tables in the IQ store (main store).
• Moving a Table Index
  Move an index from the current dbspace to another dbspace. This action is only applicable to tables in the IQ store (main store).
• Generating Table Index DDL Commands
  Display the data description language SQL code for adding an index to a table. The SQL code can be a useful reference and training tool.
• Viewing or Modifying Table Index Properties
  Display and edit the properties for the selected table index.
• Table Index Privilege Summary
  A list of the system privileges and object permissions required to complete the various index tasks on tables.