Using index scans

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. An index is a database object. The index is maintained automatically by UltraLite after it has been created. You can create one or more indexes to improve the performance of your queries, or, depending on the type of index you create, to ensure that row values remain unique.

An index provides an ordering of a table's rows based on the values in some or all of the columns. When creating indexes, the order in which you select columns to be indexed becomes the order in which the columns actually appear in the index. So, when you use them strategically, indexes can greatly improve the performance of searches on the indexed column(s).

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.

Index Characteristics
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.

Performance tips

  • Create an index on any column:

    • for values that you search for on a regular basis
    • that the query uses to join tables
    • that are commonly used in ORDER BY, GROUP BY, or WHERE clauses

  • When creating a composite index, the first column of the index should be the one that is used most often by the predicate in your query.

  • Ensure the update maintenance overhead an index introduces is not too high for the memory of your device.

  • Do not create or maintain unnecessary indexes: indexes must be updated when the data in a column is modified, so all insert, update, and delete operations are performed on the indexes as well.

  • Create an index on large tables.

  • Do not create redundant indexes. For example, if you create an index on table T with columns (x, y), you can create a redundancy if there is another existing index on T with columns (x, y, z).

See also
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1