An index for a base table is used to accelerate access to a table. An index is a tree-type access structure assigned to a column or combination of columns in a particular table and which contains cross-references the rows in this table. An index uses a so-called inverted list to assign to every value in a column those rows which contain the value in this column. Combinations of columns are dealt with in the same way.
An index is referred to as simple or compound, depending on whether it refers to one or more columns.
SESAM/SQL uses indexes
to provide rapid access to rows containing specific values in the index columns
to return the rows of a table in sorted sequence according to the values in the index columns
to evaluate integrity constraints for one or more columns of the index without the need to access the base table.
Administration of an index increases the overhead involved in insert and update statements and in recovery operations. The following guidelines should therefore be observed:
Indexes should not be used for tables which only contain a few rows. The time advantages of an index are lost in the case of small tables as a result of the time taken to open and search the index file.
No index should not be used for a column which contains only a small number of different values.
Indexes are more suitable for tables used primarily for retrieving data than for tables which are updated regularly.
An index is created with the SQL statements CREATE INDEX or ALTER TABLE and deleted with DROP INDEX.
Storage structure of indexes
When an index is created using CREATE INDEX, storage space which is known as the contiguous area of the table is reserved. The values which are to be inserted are stored in this area. If a value is to be inserted and there is no longer enough space in this area, relocation takes place, i.e. a free block is created. Logically this block belongs to the index, but it is no longer physically contained in the contiguous area of the index. The next time the user space is reorganized using the REORG SPACE statement all the existing tables and indexes are recovered in the user space. The relocations then also disappear.
Index for a UNIQUE constraint
SESAM/SQL requires an index for every column or combination of columns for which a UNIQUE constraint is defined. If an index has already been created for the relevant column or combination of columns using CREATE INDEX, then this index is also used for the UNIQUE constraint.
If this is not the case, SESAM/SQL generates the index automatically. The name of an index generated in this way begins with UI, followed by a 16-digit number.
REORG STATISTICS statement
REORG STATISTICS rebuilds the global statistics for an index. It makes sense to use REORG STATISTICS after large volumes of changes or insertions have been made. Updated statistics allow more accurate internal estimation of costs and supports the decision regarding the most efficient access plan (see "High performance").