Creating Indexes

Indexes optimize operations that either search for or order by specific values. Define indexes for any columns on which you frequently perform either of these operations. Indexes provide a fast retrieval method for a specific row or group of rows in query optimization. Pervasive PSQL also uses indexes with referential integrity (RI). Indexes improve performance on joins and help to optimize queries. For more information about RI, see Pervasive PSQL User's Guide.

In Pervasive PSQL databases, the transactional database engine creates and maintains indexes as part of the physical file for which they are defined. The transactional database engine performs all maintenance for Insert, Update, or Delete operations. These activities are transparent to any Pervasive PSQL application.

To create an index, use a CREATE INDEX statement. This method creates a named index. You can delete named indexes after you create them. For more information about dropping indexes, refer to Chapter 14, Inserting and Deleting Data.

While indexes allow you to sort rows and retrieve individual rows quickly, they increase the disk storage requirements for a database and decrease performance somewhat on Insert, Update, and Delete operations. You should consider these trade-offs when defining indexes.

The next example uses a CREATE INDEX statement to add an index to a table that already exists:

CREATE INDEX DeptHours ON Course
(Dept_Name, Credit_Hours)# 
 


 Note


Be aware that if you use the CREATE INDEX statement on files that contain a lot of data, execution could take some time to complete, and other users may not be able to access data in that file in the meantime.

For detailed information about the CREATE TABLE and CREATE INDEX statements, refer to the SQL Engine Reference.

Index Segments

You can create an index on any single column or group of columns in the same table. An index that includes more than one column is called a segmented index, in which each column is called an index segment.

For example, the Person table in the sample database has the following three indexes:

A segmented index consisting of the Last Name column and the First Name column.

The Perm_State + Perm_City column.

The ID column.

The number of index segments is affected by the page size of the data file. See the API Programmer's Reference for more information on how to use the PAGESIZE keyword. The maximum number of indexes you can create for a table depends on the page size of its data file and the number of segments in each index. As Table 12-2 shows, data files with page sizes smaller than 4096 bytes cannot contain as many index segments as a data file with a page size of 4096. The number of index segments that you may use depends on the file's page size.

Table 12-2 Maximum Number of Index Segments per Data File
Page Size (bytes)	Maximum Key Segments byFile Version
	8.x and prior	9.0	9.5
512	8	8	rounded up2
1,024	23	23	97
1,536	24	24	rounded up2
2,048	54	54	97
2,560	54	54	rounded up2
3,072	54	54	rounded up2
3,584	54	54	rounded up2
4,096	119	119	204
8,192	n/a1	119	420
16,384	n/a1	n/a1	420
1"n/a" stands for "not applicable" 2"rounded up" means that the page size is rounded up to the next size supported by the file version. For example, 512 is rounded up to 1,024, 2,560 is rounded up to 4,096, and so forth.

In Status Codes and Messages book, see status codes "26: The number of keys specified is invalid" and "29: The key length is invalid" for related information about index segments and the transactional interface.

Using the page size and fixed record length, you can calculate the efficiency with which data is being stored (such as the number of wasted bytes per page). By having fewer records per page, you can improve concurrency where page-level locking is concerned.

By default, Pervasive PSQL creates all tables with a page size of 4096 bytes. However, you can specify a smaller page size using the PAGESIZE keyword in a CREATE TABLE statement, or you can create a table using the MicroKernel Database Engine and specify a smaller page size for that table.

When calculating the total number of index segments defined for a table, a nonsegmented index counts as one index segment. For example, if your table has three indexes defined, one of which has two segments, the total number of index segments is four.

You can use the Pervasive PSQL Control Center to display the number of defined index segments and the page size of a data file. For information about this utility, see the Pervasive PSQL User's Guide.

Index Attributes

When you create an index, you can assign to it a set of qualities, or attributes. Index attributes determine the modifiability of the index and how Pervasive PSQL sorts the indexes you define for a table. You can include parameters specifying index attributes anytime you create or alter an index definition.

Indexes can have the following attributes:


Case-sensitivity	Determines how Pervasive PSQL evaluates uppercase and lowercase letters during sorting. By default, Pervasive PSQL creates case-sensitive indexes. To create a case-insensitive index, specify the CASE keyword when you create the index.
Sort order	Determines how Pervasive PSQL sorts index column values. By default, Pervasive PSQL sorts index column values in ascending order (from smallest to largest). To create an index that sorts in descending order, specify the DESC keyword when you create the index.
Uniqueness	Determines whether Pervasive PSQL allows multiple rows to have the same index column value. By default, Pervasive PSQL creates non-unique indexes. To create an index that requires unique values, specify the UNIQUE keyword when you create the index.
Modifiability	Determines whether you can modify index column values after Pervasive PSQL stores the corresponding row. By default, Pervasive PSQL does not allow changes to index column values once Pervasive PSQL stores the row. To create a modifiable index, specify the MOD keyword when you create the index.
Segmentation	Indicates whether the index is segmented (whether it consists of a group of columns combined into a single index). By default, Pervasive PSQL creates indexes that are not segmented. To create a segmented index using the CREATE TABLE statement, specify the SEG keyword for each index segment you create, except the last segment in the index. (The SEG keyword indicates that the next column specified is a segment of the index you are creating.) Because you can create only one index at a time with the CREATE INDEX command, you do not need to use the SEG keyword to specify a segmented index. If you specify more than one column, Pervasive PSQL creates a segmented index using the columns in the order in which you specify them.
Partial	Indicates whether Pervasive PSQL uses a portion of a CHAR or VARCHAR column, designated as t he only or last index column, when the total size of the column(s) plus overhead is equal to or greater than 255 bytes. By default, Pervasive PSQL does not create partial indexes. To create a partial index using the CREATE INDEX statement, specify the PARTIAL keyword.

Uniqueness and modifiability apply only to entire indexes. You cannot apply uniqueness or modifiability to a single index segment without applying it to the entire index. For example, if you create a segmented index and specify the MOD keyword for one of the index segments, you must specify the MOD keyword for every segment.

In contrast, you can apply case-sensitivity, sort order, and segmentation to individual index segments without affecting the entire index. For example, you can create a case-insensitive index segment in an otherwise case-sensitive index.

Partial Indexes apply only to the last column defined in the index, as long as that column meets the following criteria:

the column is the only column defined in the index, or it is the last column defined in the index

the last index column is of data type CHAR or VARCHAR

the total size of the index, including column overhead, is equal to or greater than 255 bytes.

For more information on creating indexes and the attributes available, see CREATE INDEX in the SQL Engine Reference