Table Column Description

Database Tables

Integrity Constraints in Tables

Database Table Indexes

An index is an auxiliary data structure that will make the following processes quicker:

• Search for records that fulfill certain conditions in SQL queries.
• Sort records in a SQL query.
• JOIN table operations (relational table connection).
• Work with related tables in interactive database applications.

Each index captures the record order in tables according to a specific key. This key can be a column value, a expression value, or an multiple columns or expressions.

An index may be connected with a rule that prohibits key value duplicity. This index is unique.

Indexes are not a part of SQL standard, however they are used in all SQL implementations. You can have a maximum of 24 indexes per table in the current version of 602SQL.

Using an index is automatic. The application author may specify an index to be used when creating a query. The way a query is evaluated and which index is used can be found by clicking the Optimization button on the Control Panel.

The system query _IV_INDICIES returns a list of indexes.

Indexes Define

An index is defined by the CREATE TABLE or ALTER TABLE statement:

index_description ::= { UNIQUE | PRIMARY KEY | INDEX } (expression [direction] {, expression [direction] }... ) 
	[{ NULL | NOT NULL }] 
direction ::= ASC | DESC

An index that is a single column value can be specified as a column property.

Indexes marked as UNIQUE or a PRIMARY KEY are unique and prevent inserting two records with the same key value to the table. An index marked as INDEX allows key value duplicity. An index marked a PRIMARY KEY may be specified only once in a table definition and serves a special purpose.

NULL Values in Indexes

You can specify if table rows with a NULL value should be involved in the index definition. If the NULL keyword is specified in the index description, the index contains keys with NULL values. If NOT NULL is specified, these keys are omitted.

If neither of these two specifications are set, the index type is important. A NONUNIQUE index may contain NULL values, while UNIQUE and PRIMARY KEY indexes may not contain NULL.

Omitting NULL values from an index makes sense if the index is UNIQUE, but it is desired to allow duplicities for NULL values. If NULL values are omitted from an index, the index will not be used for optimizing the MIN aggregate function, IS NULL predicate and for many conditions in a query. This type of index will not quicken query evaluation (see details).

Example:

CREATE TABLE Tab (a INTEGER, b INTEGER,
  CONSTRAINT Ind_a UNIQUE (a) NULL,
  CONSTRAINT Ind_b INDEX (b DESC) NOT NULL)

CREATE UNIQUE INDEX Ind_a ON Tab (a) NULL;
CREATE INDEX Ind_b ON Tab (b DESC) NOT NULL;

List of topics:

• Information about Query Optimization and Evaluation
• Using Indexes in Prefix-like Queries
• Using Indexes not Containing NULL Values
• CREATE INDEX Statement
• DROP INDEX Statement

Table Column Description

Database Tables

Integrity Constraints in Tables