| Name: | CREATE INDEX | Shortest: | CRE IND |
|---|---|---|---|
| Family: | Command | Category: | Data Definition |
| Description: | Use the CREATE INDEX command to speed up data retrieval by creating pointers that locate rows in a table easily. | ||
,
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.
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.
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.
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.
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):
R:BASE automatically indexes the column(s) that is defined as the table's primary key.
R:BASE automatically indexes the column(s) that is defined as the table's foreign key.
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.
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.
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:
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)