CREATE INDEX (Short Name: CRE IND)
Top  Previous  Next

Use the CREATE INDEX command to speed up data retrieval by creating pointers that locate rows in a table easily.

createin

Options

,
Indicates that this part of the command is repeatable.

ASC
DESC
Specifies whether to sort a column in ascending or descending order.

CASE
Specifies that the data values will be case sensitive.

colname
Specifies a 1 to 18 character column name. In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command.

INDEX indexname
Specifies an index, which is displayed with the LIST INDEX command. An indexname is required.

ON tblname
Specifies the table in which to create an index for a column.

SIZE n
Sets the minimum number of characters to preserve to determine uniqueness during hashing. This number can be a maximum of 200 characters. The index is created with the first n characters preserved and the rest of the value stored as a 4-byte hashed representation.

UNIQUE
Requires the values in a column to be unique.

About the CREATE INDEX Command

An index creates pointers to rows in columns, which allows R:BASE to find the rows using pointers much faster than searching the rows of data sequentially. You can index a column of any data type. An indexed column improves the performance of the following commands, clauses, and operations.

Commands, Clauses, and Operations to Use with Indexes

DELETE DUPLICATES
RULES
INTERSECT
SELECT (when it includes a WHERE or an ORDER BY clause)
JOIN
SUBTRACT
Look-up expressions in forms or reports
UNION
ORDER BY
WHERE
PROJECT



Although indexes speed up processing, they might slow down data entry because building an index for each value as it is entered takes time. Creating indexes for columns that contain many duplicate values does not always speed up processing. Indexes also occupy space on a disk.

Null Values

An indexed column can contain null values, but R:BASE uses an index most efficiently if each row in the indexed column contains a value. Primary keys, unique keys, or unique indexes explicitly restrict the insertion of null values. For other indexes, you can define a rule to ensure that a column always contains a value.

UPDATE Permission

When access rights for a table have been assigned using the GRANT command, you must have UPDATE permission for the column you want to index. For information about assigning rights, see the "GRANT" entry.

Indexing Criteria

Some columns are better candidates than others for indexing. To receive the greatest benefit from indexes, use the following criteria to help you decide which type of column is the best choice for indexing your table(s):

Primary Key

R:BASE automatically indexes the column(s) that is defined as the table's primary key.

Foreign Key

R:BASE automatically indexes the column(s) that is defined as the table's foreign key.

Columns Used in Queries

Columns that are not primary or foreign keys but are frequently used in queries should be indexed. Create a unique key constraint for columns that are not primary or foreign keys, but which uniquely identify a row in the table.

Columns Frequently Using ORDER BY or GROUP BY

Include a column in an ascending-order index when the column is not a primary or foreign key but is frequently referenced in an ascending-column ORDER BY or GROUP BY clause. Similarly, include a column in a descending-order index when the column is frequently referenced in a descending-column ORDER BY clause.

Full- and Partial-Text Indexes

Text columns can make effective indexed columns. If the size of the column that has a TEXT data type is 200 bytes or less, R:BASE creates a full-text index. A full-text index is an index that stores the entire contents of a column as an index in .RB3, which is the file that contains indexes to columns. If the size of the column is greater than 200 bytes, R:BASE creates a partial-text index.

If you specify the SIZE option to be less than the defined length of a column, R:BASE creates a partial-text index, and any text column that has a defined length over 200 bytes must be a partial-text index. For columns that have a TEXT data type and exceed 200 bytes, you can specify the SIZE option to be between 0 and 196 to create a partial-text index. Specifying the size allows you to base your index on a specified number of characters at the beginning of the columns and to hash the remaining characters. For example, you can index a 225-character column with a TEXT data type by specifying the SIZE option to be any number less than 197 bytes. R:BASE will create an index with the first n characters and the rest of the value will be stored as a four-byte hashed representation of the text.

Partial-text indexes minimize storage space. However, partial-text indexes might not be as efficient as a full-text index, for example:

CREATE TABLE cities (cityname TEXT(40), state TEXT(2), country + 
TEXT(20))
CREATE INDEX cityindex ON cities (cityname, state)
INSERT INTO cities VALUES('Bellevue','WA','USA')
INSERT INTO cities VALUES('Belltown','PA','USA')
SELECT cityname, state from cities WHERE cityname = 'Bellevue'

In the above example, because the query reads data only from the index named cityindex, there is no need to read the actual data stored in .RB2-which is the data file-so the query is done quickly. The query is an index-only retrieval and produces fast results.

If a partial-text index was used in the same query as above, the partial-text index could also only use the index named cityindex. Because the partial-text index only preserves the first four characters, it is impossible to return the correct answer to the query from the index. The query, as shown below, would slow processing because R:BASE must read data from the R:BASE system .RB2.

CREATE INDEX cityindex ON cities (cityname SIZE 4, state) 
SELECT cityname, state from cities WHERE cityname = 'Bellevue'

When creating text indexes, be aware of the following:

·If you omit the SIZE option and the text field in the column is greater than 200 bytes, R:BASE creates a partial text index by storing the first 32 bytes of each field and hashing the remaining bytes in each field into a four-byte numeric representation of the text. For example, if the text is 280 bytes and you do not specify a size, R:BASE stores the first 32 bytes of each field and hashes the remaining 248 bytes into a four-byte integer.  
·If you specify the SIZE option to be 16 bytes for a 60-byte column with a TEXT data type, R:BASE stores the first 16 bytes of each 60-byte text field and hashes the remaining bytes in each field into a four-byte numeric representation of the text. The total length of each index entry will be 20 bytes (16 + 4).  
·If you specify the SIZE option to be 30-bytes for a 250-byte column with a TEXT data type, R:BASE stores the first 30 bytes of each 250-byte field and hashes the remaining bytes in each field into a four-byte numeric representation of the text. The total length of each index entry will be 34 bytes.  
·If you specify the SIZE option to be 250 bytes for a column with a TEXT data type, you have made an illegal request because the maximum value for the SIZE option is 196 bytes when the length of the text field is greater than 200 bytes. If you specified the SIZE option to be 196 bytes for a 250-byte column, R:BASE would hash the remaining 54 bytes into a four-byte numeric representation of the text.  
·If you omit the SIZE option and the text field in the column is 200 bytes or less, R:BASE creates a full-text index. For example, if the text is 80 bytes and you do not specify a size, R:BASE builds a full-text index of 80 bytes.  

Examples

The following command creates an index for the custid column in the transmaster table.

CREATE INDEX trancust ON transmaster (custid)

The following example creates a multi-column index for the company, custaddress, and custstate columns in the customer table.

CREATE INDEX custaddr ON customer (company ASC, custaddress ASC, custstate ASC)