CREATE INDEX

Use the CREATE INDEX statement to create a named index for a specified table.

Syntax

CREATE [ UNIQUE ] [ NOT MODIFIABLE ] INDEX index-name [ IN 
DICTIONARY ] ON table-name [ index-definition ] 
index-definition ::= ( index-segment-definition [ , index-segment-definition ]... 
) 
index-segment-definition ::= column-name [ ASC | DESC ] 
index-name ::= user-defined-name 

Remarks

The maximum column size for VARCHAR and CHAR columns is 255 bytes. VARCHAR columns differ from CHAR columns in that either the length byte (Btrieve lstring) or a zero terminating byte (Btrieve zstring) are reserved, reducing the effective storage by 1 byte. In other words, you can fit 255 useful characters in a CHAR(255) type, but only 254 useful characters in a VARCHAR, after you reserve a byte for the size or null terminator.

An index cannot be created on a VARCHAR or CHAR type column if the column is nullable and at its maximum size. A nullable column is preceded in the data file by a one-byte null indicator. When an index is created on a nullable column, a segmented key is created with a one-byte segment for the null indicator and another segment for the column. The maximum allowed key size is 255 bytes. An index on a nullable VARCHAR or CHAR type column of 255 bytes would require a key of 256 bytes, which exceeds the maximum size allowed for the key.

Whenever you create a relational index definition for an existing data file (through the use of CREATE INDEX or CREATE INDEX IN DICTIONARY), Pervasive PSQL automatically checks the Btrieve indexes defined on the file to determine whether an existing Btrieve index offers the set of parameters requested by the relational index definition. If an existing Btrieve index matches the new definition being created, then an association is created between the relational index definition and the existing Btrieve index. If there is no match, then Pervasive PSQL create a new index definition and, if IN DICTIONARY is not specified, a new index in the data file.

Index Segments

The number of index segments that you may use depends on the file's page size.

Note that nullable columns must also be considered. For example, in a data files with 4096 byte page size you are limited to 119 index segments per file. Because each indexed nullable column with true null support requires an index consisting of 2 segments, you cannot have more than 59 indexed nullable columns in a table (or indexed nullable true null fields in a Btrieve file). This limit is smaller for smaller page sizes.

Any file created with file version set to 7.x or later, and TRUENULLCREATE set to the default value of On, has true null support. Files created using an earlier file format, or with Pervasive.SQL 7, or with TRUENULLCREATE set to Off, do not have true null support and do not have this limitation.

A UNIQUE segment key guarantees that the combination of the segments for a particular row are unique in the file. It does not guarantee or require that each individual segment is unique.

---

Note
All data types can be indexed except for the following:
BIT
LONGVARBINARY
LONGVARCHAR
BLOB
CLOB

---

See also status code 6008: Too Many Segments in Status Codes and Messages.

IN DICTIONARY

The purpose of using this keyword is to notify Pervasive PSQL that you wish to make modifications to the DDFs, while leaving the underlying physical data unchanged.

You cannot use this keyword on a bound database.

IN DICTIONARY is a very powerful and advanced feature. It should only be used by system administrators or when absolutely necessary. Normally, Pervasive PSQL keeps DDFs and data files perfectly synchronized, but this feature allows users the flexibility to force out-of-sync table dictionary definitions to match an existing data file. This can be useful when you want to create a definition in the dictionary to match an existing data file.

---

Caution
Modifying a DDF without performing parallel modifications to the underlying data file can cause serious problems.

---

If you have created a disjointed index, one that exists only in the DDF and not in the data file, and you attempt to drop the index without using IN DICTIONARY, you may encounter Status Code 6 (invalid key number). This error occurs because the database engine attempts to delete the index from the data file, and it is unable to do so because no such index exists in the data file.

You cannot control the number assigned to a Btrieve index (key) if you create the index through SQL. If you use SQL to create indexes on a Btrieve file that has no indexes defined, the first index created is index #0, the second is index #1, and so forth.

If the Btrieve file already has one or more indexes defined, adding an index through SQL causes Pervasive PSQL to check the Btrieve indexes for one that has the required attributes. A description of the new index is inserted into INDEX.DDF. If Pervasive PSQL can match an existing Btrieve index, the inserted description includes the index number of the existing index and no new Btrieve index is generated on the data file.

If Pervasive PSQL cannot match an existing Btrieve index, the smallest unused index number is specified in the description. The description of the new index is inserted into INDEX.DDF. If IN DICTIONARY is not specified, then a Btrieve index is created on the data file using the index number specified in the description.

Since you cannot specify which index number is used to create new indexes in INDEX.DDF, finding out the index number is a manual process. First, issue the command BUTIL -STAT on the underlying Btrieve file to get a listing of the Btrieve indexes. Then you can query X$Index to see which SQL index number(s) will be used next.

	SELECT X$Index.* from X$Index  
        WHERE Xi$file in  
		(SELECT Xf$Id FROM X$File  
			WHERE Xf$Name = 'tablename') 

Substitute the name of your database table for tablename.

Examples

The following example creates an index named Dept based on Dept_name in the Faculty table.

CREATE INDEX Dept on Faculty(Dept_Name) 

The following example creates a non-modifiable segmented index in the Person table.

CREATE NOT MODIFIABLE INDEX X_Person on Person(ID, Last_
Name) 

The following examples create a "detached" table, one with no associated data file, then add and drop an index from the table definition. Again, this index is a disjointed index because there is no underlying Btrieve index associated with it.

CREATE TABLE t1 IN DICTIONARY (c1 int, c2 int) 
CREATE INDEX idx_1 IN DICTIONARY on t1(c1) 
DROP INDEX t1.idx_1 IN DICTIONARY 

See Also

DROP INDEX