CREATE INDEX
Creates
a new index on a table.
SYNTAX
| index_name |
Name
of the new index you want to create. |
| table_name |
Name
of the table you are creating the index on. |
| column_name |
Name
of the column or columns you are creating the index on. |
| number |
Value
to use for the fillfactor. |
DESCRIPTION
The CREATE
INDEX command creates a new index on an existing table. You can use indexes
to increase the performance of queries by quickly locating specific rows
in a table without examining the entire table. To execute the CREATE INDEX
command on a table, you must be the table owner, have DBA security privilege,
or have the INDEX privilege on that table.
An index
is a mechanism that provides fast access to specific rows in a table based
on the values of one or more columns from the table (known as the key).
Indexes contain the same data as the key columns from the table they are
based on, but the data is structured and sorted to make retrieval much
faster than the table. Once you create an index on a table, its operation
is transparent to users of the database; the DBMS will use the index to
improve query performance whenever possible.
When you
create an index you must specify the index name, the name of the table
you are creating the index on, and the name of the key columns in the
table you want to create the index on. You can create an index on one
or more columns, up to a maximum of 16 columns. Although a table may have
up to 252 columns, you can only create an index on the first 127 columns.
DBMaker also limits indexes to a maximum record size of 1024 bytes.
The UNIQUE
keyword is optional. This keyword specifies whether an index is a unique
index. In a unique index, no more than one row can have the same key value;
a unique index cannot contain duplicate values. However, each NULL value
in an index is treated as a unique value, so it is possible to have multiple
rows with NULL values in a unique index. When you create an index on a
non-empty table, DBMaker checks whether all existing keys are distinct.
If duplicate keys exist, DBMaker will return an error message and will
not create the index. Whenever you insert or update a record in a table
that has a unique index, DBMaker will check to ensure there is no existing
record that already has the same key values as the new or updated record.
DBMaker does not create unique indexes by default; if you want to create
a unique index, you must explicitly specify this using the UNIQUE keyword.
The FILLFACTOR
keyword is optional. This keyword specifies the percentage of an index
page that can be filled before new key values can no longer be inserted.
This allows the database to optimize the use of index pages by reserving
space on an index page for updates to existing records. The number parameter
can have a value from 1 to 100, which represents a fillfactor of 1% to
100%. If you often update a table after creating an index on it, you can
set a low fillfactor value (such as 50) to leave free space for inserting
new key values. If you plan to update the table rarely or infrequently,
you can leave the fillfactor at the default value of 100.
The ASC/DESC
keywords are optional. These keywords specify whether the sort order of
the index is ascending or descending. You can specify the sort order on
a column by column basis, so it is possible to have some index columns
in ascending order while others are in descending order. The sort order
of an index may affect the order of query output in some cases. If an
index is in descending order, it is possible the output will appear in
descending order even though you did not specify this in the query. If
you must have a specific sort order for a query, you should specify it
explicitly using the ORDER BY clause. The default sort order for columns
in an index is ASC (ascending).
When you
load data into a table, DBMaker will update all indexes on that table
each time a new record is inserted. For this reason, you should try to
load all of your data before creating an index on a table if possible.
This is especially true if the table has a large amount of data that will
remain relatively stable after it is loaded. It is much more efficient
to create an index after loading a large amount of data than to create
an index before loading the data.
Index names
must be unique for the table you are creating it on. Index names have
a maximum length of eighteen characters, and may contain numbers, letters,
the underscore character, and the symbols $ and #. The first character
may not be a number.
EXAMPLE
The following
example creates an index named NameIndex on the FirstName and LastName
columns of the Employees table. The index is not unique and may contain
duplicate values.
CREATE INDEX NameIndex ON Employees (FirstName, LastName)
|
The following
example creates an index named NameIndex on the FirstName and LastName
columns of the Employees table. The FirstName and LastName columns are
both sorted in descending order.
CREATE INDEX NameIndex ON Employees (FirstName DESC, LastName DESC)
|
The following
example creates a unique index named ClassIndex on the Course and Section
columns of the Classes table. The index is unique and may not contain
duplicate values.
CREATE UNIQUE INDEX ClassIndex ON Classes (Course, Section)
|
The following
example creates a unique index named ClassIndex on the Course and Section
columns of the Classes table. The index is unique and may not contain
duplicate values, and has a fillfactor of 80.
CREATE UNIQUE INDEX ClassIndex ON Classes (Course, Section) FILLFACTOR 80
|
RELATED COMMANDS
<
CREATE GROUP | Contents
| CREATE LINK >
|
