VARCHAR columns differ from CHAR columns in that either the length byte (Btrieve lstring) or a zero terminating byte (Btrieve zstring) are reserved, increasing the effective storage by 1 byte. In other words, if you create a column that is CHAR (100), it occupies 100 bytes in the records. A VARCHAR (100) occupies 101 bytes. NVARCHAR columns differ from NCHAR columns in that a zero terminating character is reserved, increasing the effective storage by 2 bytes. In other words, if you create a column that is NCHAR(50), it occupies 100 bytes in the records. A NVARCHAR(50) column occupies 102 bytes.
When Zen creates an index, its process varies depending on whether the statement includes IN DICTIONARY, USING, or both. The following table summarizes the results.
Operation
Process and Results
Additional Information
CREATE INDEX
When successful, an index is added to both the data file and X$Index.
•If the data file has no defined indexes, the index created is index 0.
•If the data file has one or more defined indexes, the index created is the smallest unused index number.
In both cases, a new index with the same number is inserted into X$Index also.
When successful, an index is added to X$Index only. Nothing is inserted into the data file.
The data file is examined to determine what index numbers are available.
•If the data file has no defined indexes, the index inserted into X$Index is numbered 0.
•If the data file has one or more defined indexes, the database engine checks to see if there is one that is not already defined in X$Index with column and index attributes that match the index to be added.
If a match is found, this index number is used when the index is added to X$Index.
If no match is found, the index number used is <the largest data file index-number> + 1.
An index in X$Index without a matching key in the data file is referred to as a phantom index and is not used by the database engine.
When successful, an index with the specified index-number is added to X$Index only. Nothing is inserted into the data file.
If the specified index-number exists in the data file and not in X$Index, and the column and index attributes match the index to be added, the index with the specified index-number is added to X$Index. Otherwise, an error is returned.
An index segment corresponds to a column specified in the index definition. A multiple segmented index is one that was created as a combination of multiple columns.
The total number of segments that you may use in all indexes defined on a given file depends on the file page size.
Page Size (bytes)
Maximum Key Segments by File Version
8.x and earlier
9.0
9.5
13.0
512
8
8
Rounded up2
Rounded up2
1,024
23
23
97
Rounded up2
1,536
24
24
Rounded up2
Rounded up2
2,048
54
54
97
Rounded up2
2,560
54
54
Rounded up2
Rounded up2
3,072
54
54
Rounded up2
Rounded up2
3,584
54
54
Rounded up2
Rounded up2
4,096
119
119
2043
1833
8,192
n/a1
119
4203
3783
16,384
n/a1
n/a1
4203
3783
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 1024, 2560 is rounded up to 4096, and so forth.
3While a 9.5 format or later file can have more than 119 segments, the number of indexes is limited to 119.
Note that nullable columns must also be considered. For example, a data file with 4096 byte page size is 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.
Files support true nulls if they are created as file version of 7.x or higher and have TRUENULLCREATE set to on. Files created using an earlier file format, with Pervasive.SQL 7, or with TRUENULLCREATE set to off do not have true null support and do not have this limitation.
UNIQUE
A UNIQUE index key guarantees that the combination of the columns defined in the index for a particular row are unique in the file. It does not guarantee or require that each individual column be unique, in the case of a multisegmented index.
Note All data types can be indexed except for the following: BIT BLOB CLOB LONGVARBINARY LONGVARCHAR NLONGVARCHAR
Use the PARTIAL keyword with a CREATE INDEX statement to create an index on a column, or group of columns, totalling more than 255 bytes.
Partial indexes are created using a prefix of a wide column, or by combining multiple small columns, so that searches using a prefix of the wide column will execute faster. Therefore, queries using WHERE clause restrictions, for example 'WHERE column_name LIKE 'prefix%' would execute faster using the partial index as opposed to not using any index.
If you include the PARTIAL keyword with a CREATE INDEX statement, and the index column(s) width and overhead do not equal or exceed 255 bytes, the PARTIAL keyword is ignored and a normal index is created instead.
Note Width refers to the actual size of the column, and overhead refers to NULL indicators, string lengths, and the like.
Limitations of PARTIAL
The following limitations apply when using PARTIAL:
•Partial indexes may only be added to columns with the data type of CHAR or VARCHAR.
•Partial index columns should always be the last segment in the index definition, or should be the only segment in the index definition.
When the partial index column is the only segment in the index, the column size can be up to 8,000 bytes, but the user-data index segment will be of size 255 bytes.
•Partial indexes are not used by the engine while executing queries with strict equality or collation operations, such as ORDER BY, GROUP BY or JOINs involving the partial column.
•Partial indexes are used only while matching WHERE clause restrictions of the following form:
WHERE col = 'literal'
WHERE col LIKE 'literal%'
WHERE col = ?
WHERE col LIKE ?
where the literal or actual parameter value can be of any length. It could be shorter or wider than the number of bytes indexed in the partial index column. Partial indexes won't be used if a LIKE clause is not of the form 'prefix%'.
If the WHERE clauses match the constraints listed previously, partial indexes will be used while creating the execution plan.
Note If a partially indexed column length is altered using ALTER TABLE such that the new length fits in 255 bytes of the index or when the new length overshoots 255 bytes, it is the responsibility of the user to drop the index and recreate it according to his/her requirements.
Examples
This section provides a number of examples of CREATE PARTIAL INDEX.
The following example creates a table named Part_tbl with columns PartID, PartName, SerialNo and Description, using the specified data types and sizes.
Next, the example creates a partial index named idx_01 using the Description column.
CREATE PARTIAL INDEX idx_01 on part_tbl (description);
Although the Description column used in the index is 300 bytes, using the PARTIAL keyword enables the index to only use the first 255 bytes (including overhead) as the prefix.
============
The following example creates a partial index named idx_02 for the same table in the previous example. Instead, this example uses the PartId, SerialNo, and Description columns collectively for the index.
CREATE PARTIAL INDEX idx_02 on part_tbl (partid, serialno, description);
The following table details the index columns so that you may understand how the wide column is allocated in the index.
Column Name
Data Type
Size
Overhead
Size in Index
PartID
Integer
4
4
SerialNo
Varchar
200
1
201
Description
Char
300
50
Total Index Size
255
NOT MODIFIABLE
This attribute prevents the index from being changed. Note that, for a multisegmented index, this attribute applies to all segments. Status code 10: The key field is not modifiable results if you attempt to edit any of the segments.
The following example creates a nonmodifiable segmented index in the Person table.
CREATE NOT MODIFIABLE INDEX X_Person on Person(ID, Last_Name)
USING
Use this keyword to control the index number when you create an index. Controlling the index number is important in cases where the data is being accessed through the Relational Engine as well as directly from the data files through the MicroKernel Engine.
When you create an index, the specified index number is inserted into both the data file and the X$Index.
CREATE INDEX "citizen-x" USING 3 On Person (citizenship)
IN DICTIONARY
This keyword notifies the database engine that you wish to make modifications to the DDFs while leaving the underlying physical data unchanged. This feature allows you to correct any table dictionary definitions that are not synchronized with their corresponding data files or to create a definition in the dictionary to match an existing data file. This is most often needed when data files are created and used by a Btrieve (transactional) application (which does not use DDFs), but ad-hoc queries or reports need to access the data using the Relational Engine.
Normally, the database engine keeps DDFs and data files perfectly synchronized. When you create an index without the IN DICTIONARY statement, the database engine assigns identical index numbers to the X$Index and the data file. IN DICTIONARY enables you to add an index to the X$Index only.
Caution IN DICTIONARY is a powerful and advanced feature. It should only be used by system administrators or when absolutely necessary. Modifying a DDF without performing parallel modifications to the underlying data file can cause serious problems, such as incorrect results sets, performance problems, or unexpected results.
If you have created a phantom 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 can encounter status code 6: The key number parameter is invalid. This error occurs because the database engine attempts to delete the index from the data file and cannot do so because no such index exists in the data file.
If you use both IN DICTIONARY and USING in the SQL statement when you create an index, a new index using the number specified by the USING keyword is inserted into the DFF only if the segment at the specified index number matches the SQL column. If the number specified by the USING keyword either does not match the SQL column or does not exist in the data file, the SQL engine returns an error message of “Btrieve key definition does not match the index definition”. This ensures that no phantom indexes are created.
Note You cannot use the keyword IN DICTIONARY on a bound database.
Examples
This section provides a number of examples of IN DICTIONARY
The following example creates a detached table, one with no associated data file, then adds and drops an index from the table definition. This index is a detached 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
============
The following example uses a table T1 that already exists. The data file has key1 defined and it is not currently in X$Index.
CREATE INDEX idx_1 USING 1 IN DICTIONARY on T1 (C2)