The primary key requirement is not standard SQL (even though it ought to be). Eventually omission of a primary key will be allowed, and will result in generation of a hidden system-owned surrogate key.
Since unique constraints result in the creation of system-owned unique indexes for enforcement, three indexes result from this DDL statement. The constraint index will be referred to by its constraint name (SERIALNO_PK).
create table ENLISTMENT( NAME varchar(128) not null, RANK char(3), SERIALNO integer not null constraint SERIALNO_PK primary key, PLATOONID integer not null) create clustered index ENLISTMENT_CX on ENLISTMENT(NAME) create index ENLISTMENT_PLATOONID on ENLISTMENT(PLATOONID)
Each index has a defined key which is the column list specified by the DDL statement which created the index. For this example:
Note that there is a duplicate in the defined key NAME of the clustered index. This is allowed since the clustered index was not specified as unique. However, we need a unique locator for every tuple stored, so we introduce the concept of an index's distinct key. For a unique index, this is identical to the defined key. For a non-unique clustered index, we append the columns of the primary key (leaving out any that were already referenced by the defined key). In this example, the distinct key for ENLISTMENT_CX is (NAME, SERIALNO).
Note that in this case, adding on the clustered index key does not make the unclustered index key "more unique" since it is already unique; but it does give us an indirect access path from SERIALNO to tuple: first search SERIALNO_PK to convert the SERIALNO into a NAME, and then use the (NAME,SERIALNO) combination to search ENLISTMENT_CX, which stores the values for all other columns.
For non-unique index ENLISTMENT_PLATOONID, adding on the distinct key of the clustered index serves both purposes:
In this case of a non-unique unclustered index, the full tuple stored in the BTree forms the distinct key for the unclustered index. To summarize distinct keys:
A filter on an unclustered index normally requires an indirect search as described previously. However, if the unclustered index "covers" all columns referenced by the query, then a direct search can be used and the clustered index can be ignored. For example,
select NAME,RANK,SERIALNO from ENLISTMENT where NAME='Pyle'
In this case, SERIALNO_PK can be used for a direct lookup.
select NAME from ENLISTMENT where SERIALNO=1000
So, the coverage for a clustered index is the entire table, while the coverage for an unclustered index is the combination of its defined key with the distinct key of the clustered index. Examples:
|End $Id: //open/dev/farrago/doc/design/TableIndexing.html#4 $|