Create Indexes on Tables

An index provides quick access to data in a table, based on the values in specified columns. A table can have more than one index. Indexes are transparent when accessing data from that table; SAP ASE automatically determines when to use the indexes.

Indexes speed data retrieval by pointing to the location of a table column’s data on disk.

create index stor_id_ind
on stores (stor_id)

The stor_id_ind index goes into effect automatically the next time you query the stor_id column in stores. In other words, indexes are transparent to users. SQL includes no syntax for referring to an index in a query. You can only create or drop indexes from a table; SAP ASE determines whether to use the indexes for each query submitted for that table. As the data in a table changes over time, SAP ASE may change the table’s indexes to reflect those changes. Again, these changes are transparent to users.

For information on how you can design indexes to improve performance, see the Performance and Tuning Series: Locking and Concurrency Control.

• Guidelines for Using Indexes
  Placing an index on a column often makes the difference between a quick response to a query and a long wait. However, building an index takes time and storage space.
• Methods of Creating Indexes
  You can create indexes on tables either by using the create index statement, or by using the unique or primary key integrity constraints of the create table command.
• Create Indexes
  You must be the owner of a table to create or drop an index. The owner of a table can create or drop an index at any time, whether or not there is data in the table. Indexes can be created on tables in another database by qualifying the table name.
• Indexes on Computed Columns
  You can create indexes on computed columns as though they were regular columns, as long as the datatype of the result can be indexed. Computed column indexes provide a way to create indexes on complex datatypes like XML, text, image, and Java classes.
• Clustered or Nonclustered Index Usage
  With a clustered index, SAP ASE sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order. The bottom or leaf level of a clustered index contains the actual data pages of the table. With a nonclustered index, the physical order of the rows is not the same as their indexed order.
• Index Options
  The index options ignore_dup_key, ignore_dup_row, and allow_dup_row control what happens when a duplicate key or duplicate row is created with insert or update.
• Drop Indexes
  The drop index command removes an index from the database, reclaiming their storage space
• Identifying the Indexes on a Table
  Use sp_helpindex to see the indexes that exist on a table.
• Update Statistics for Indexes
  Use update statistics when a large amount of data in an indexed column has been added, changed, or deleted.