Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
|
|
View PDF |
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.
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.
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));
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.
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
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.
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
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);
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.