Table management | Tablespace management

Index management

If there is no index defined in a table, a table scan must be performed for each each table referenced in a database query. When the database system is running for a long time, such as table orders in the Commerce database, it will become larger and larger. The result is that the larger the table, the longer a table scan takes since a table scan requires each table row to be accessed sequentially. In most cases, for a SQL query that might return only some rows, an index scan is much faster than a table scan, which will give the DB2 database system more capacity to handle other transactions' requests at a consolidated duration.

Commerce already has many indexes defined. Therefore, users should not need to create them everywhere, but carefully consider the implications that are listed below.

Although indexes can reduce access time significantly, they can also have adverse effects on performance. Before you create indexes, consider the effects of multiple indexes on disk space and processing time:

• Each index requires storage or disk space. The exact amount depends on the size of the table and the size and number of columns in the index.

• Each INSERT or DELETE operation performed on a table requires additional updating of each index on that table. This is also true for each UPDATE operation that changes the value of an index key.

• The LOAD utility rebuilds or appends to any existing indexes.

• The indexfreespace MODIFIED BY parameter can be specified on the LOAD command to override the index PCTFREE used when the index was created.

• Each index potentially adds an alternative access path for a query for the optimizer to consider, which increases the compilation time.

Choose indexes carefully to address the needs of the application program. Some general index planning tips are:

• To avoid some sorts, define primary keys and unique keys.

• To access tables with a small size efficiently, try to use index to optimize frequent queries to create an index on any column that you will use when joining tables is necessary.

• To search efficiently, decide between ascending and descending ordering of keys depending on the order that will be used most often.

• IBM recommends using the SQL Explain facility to determine whether creating an index on specific columns is necessary.

• To plan indexes, you can use the db2adv tool to get advice about indexes that might be used by one or more SQL statements.

xxxx