An index is a set of pointers to rows in a table based on the order of the values of data in one or more table columns. The index is a database object that is maintained automatically by UltraLite after it has been created. When UltraLite optimizes a query, it scans existing indexes to see if one exists for the table(s) named in the query. If it can help UltraLite return rows more quickly, the index is used. If you are using the UltraLite Table API in your application, you can specify an index that helps determine the order in which rows are traversed.
Indexes can improve the performance of a query—especially for large tables. To see whether a query is using a particular index, you can check the execution plan with Interactive SQL.
Alternatively, your UltraLite applications can include PreparedStatement objects which have a method to return plans.
UltraLite supports the following indexes. These indexes can be single or multi-column (also known as composite indexes). You cannot index LONG VARCHAR or LONG BINARY columns.
|Primary key||Required. An instance of a unique key. You can only have one primary key. Values in the indexed column or columns must be unique and cannot be NULL.|
|Foreign key1||Optional. Values in the indexed column or columns can be duplicated. Nullability depends on whether the column was created to allow NULL. Values in the foreign key columns must exist in the table being referenced|
|Unique key2||Optional. Values in the indexed column or columns must be unique and cannot be NULL.|
|Non-unique index||Optional. Values in the indexed column or columns can be duplicated and can be NULL.|
|Unique index||Optional. Values in the indexed column or columns cannot be duplicated and can be NULL.|
1 A foreign key can reference either a primary key or a unique key.
2 Also known as a unique constraint.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|