F Index Creation Options

In prior releases, Oracle Lite enforced uniqueness on a set of columns by creating a unique index on all table columns. This method required a large volume of disk space for long keys or for keys containing many columns.

F.1 Uniqueness Constraint in Oracle Lite

With Oracle Lite, applications can now enforce a uniqueness constraint on a large number of columns without using a large volume of disk space. This benefits applications that require a uniqueness constraint on a large number of columns, but have table rows with the same values in a smaller subset of these columns.

F.1.1 The Address Table Example

The explanations and examples in this section all refer to the following table:

ADDRESS (STREET VARCHAR(40), CITY VARCHAR(40), STATE VARCHAR(20), ZIP VARCHAR(12));

F.1.2 Using Uniqueness Constraints

If you want to enforce a uniqueness constraint to prevent any two rows in the ADDRESS from containing identical values for all columns, you can create a unique index on all the table's columns. However, this method requires a large volume of disk space.

If you know that very few rows have the same values in the STREET and CITY columns, you can create a unique index on STREET and CITY only. If two rows have the same values for the STREET and CITY columns, then Oracle Lite locks them and tests the rows' remaining column values for uniqueness.

Although this method requires less disk space, it also has some disadvantages. Since the database must search all records to ensure that no unique or primary key columns are identical, the following actions decrease database performance:

• inserting and updating rows

• querying rows based on primary keys

When Oracle Lite locks indexed columns that have the same values, those columns cannot be accessed by concurrent database users.

F.1.3 Specifying the Number of Columns in an Index

You can specify the number of columns in an index in the POLITE.INI file, or in one of the following SQL statements:

• CREATE INDEX

• CREATE TABLE

• ALTER TABLE

F.1.3.1 The POLITE.INI File

The MAXINDEXCOLUMNS value in the POLITE.INI file specifies the maximum number of columns in an index. When a user creates a new index, the index only contains the number of columns specified in the MAXINDEXCOLUMNS variable. For example, the following line in the POLITE.INI file specifies that any newly created index must contain the first two columns of the table it refers to:

MAXINDEXCOLUMNS=2

When you apply the preceding example to the ADDRESS table, the following statement creates an index that contains the columns STREET and CITY.

CREATE INDEX IDX1 ON ADDRESS(STREET, CITY, STATE, ZIP);

The following statement also creates a unique index that contains the columns, STREET and CITY:

CREATE UNIQUE INDEX IDX1 ON ADDRESS(STREET, CITY, STATE, ZIP);

Since the statement contains the UNIQUE clause, Oracle Lite designates all of the specified columns as a unique key.

F.1.3.2 The CREATE UNIQUE INDEX Statement

In Oracle Lite, the CREATE UNIQUE INDEX statement contains the following optional clause for specifying the number of indexed columns:

KEY COLUMNS = <number_of_columns>

Oracle Lite creates an index that contains the number of columns you specify in the KEY COLUMNS clause. For example, the following statement creates an index that contains two columns, STREET and CITY:

CREATE UNIQUE INDEX IDX1 ON ADDRESS (STREET, CITY, STATE, ZIP) KEY COLUMNS = 2;

Since the statement contains the UNIQUE clause, Oracle Lite designates all of the specified columns as a unique key

F.1.3.3 The CREATE TABLE and ALTER TABLE Statements

The PRIMARY KEY clause in the statements, CREATE TABLE and ALTER TABLE supports the following clause for specifying the number of indexed columns:

KEY COLUMNS = <number_of_columns>

The following example creates a table and designates four of its columns as primary keys. However the index that enforces the primary key only contains the first two columns:

CREATE TABLE ADDRESS (STREET VARCHAR(40), CITY VARCHAR(40), STATE VARCHAR(20), ZIP VARCHAR(12), PRIMARY KEY(STREET, CITY, STATE, ZIP)  KEY COLUMNS = 2);

F.1.3.4 Usage Notes

If the POLITE.INI file does not include a value for the MAXINDEXCOLUMNS variable and the SQL statements do not use the KEY COLUMNS option, then Oracle Lite uses all of the specified columns to create an index.

If the POLITE.INI file specifies a MAXINDEXCOLUMNS value, then Oracle Lite uses this value to create all indexes and primary keys unless the KEY COLUMNS clause in a SQL statement overrides it.