Creating an Index

An index provides quick access to data in a table, based on the values in specified columns.

An index is created on one or more table columns and points to the place where the column data is stored on disk. Indexes speed up data retrieval and are useful for enforcing referential integrity. A table can have more than one index.

  1. In the Perspective Resources view, select the server on which the table resides, then click the drop-down arrow next to the server name and select Administration Console.
  2. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables, then choose one of the following:
    • User Tables
    • Proxy Tables
  3. Select the table for the new index.
  4. Select Properties.
  5. From the Table Properties window, select Indexes > New > Index.
    You see the Add Index wizard.
  6. Enter a name for the index.
  7. Select the columns to include in the index.
  8. (Optional) Click on Add index column expression.
    1. Select ascending or descending as the order of the index expression.
    2. Enter a name for the expression.
  9. Select the database segment in which to place the index.
  10. Select a data cache for the index.
  11. (Optional) In the Key Type window, select:
    • Make this index unique.

      If the index is unique, you can ignore duplicate keys in the Duplicate Keys/Row window.

    • Make this index clustered.

      If the index is clustered, specify how you want the server to handle requests to insert duplicate rows in a table in the Duplicate Keys/Row window.

  12. (Optional) In the Duplicate Key window:
    • Click Ignore duplicate keys to ignore duplicate keys rather than abort the transaction.
    • Choose whether to allow or ignore duplicate rows in a table.
  13. (Optional) In the Space Management window:
    1. Specify the percentage amount to fill a page when the index is created.
    2. Specify the number of rows allowed on pages.
    3. Specifying a ratio of empty pages to filled pages.
  14. (Optional) Specify the cache strategy when creating the index.
    • Most recently used replacement – reads new pages into the LRU end of the chain of buffers in cache. The pages are used and immediately flushed when a new page enters the MRU end. This strategy is advantageous when a page is needed only once for a query. It tends to keep such pages from flushing out other pages that can potentially be reused while still in cache.
    • Large buffer prefetch– if memory pools for large I/O are configured for the cache used by a table or an index, the optimizer can prefetch data or index pages by performing large I/Os of up to eight data pages at a time. This prefetch strategy can be used on the data pages of a table or on the leaf-level pages of a nonclustered index. By default, prefetching is enabled for all tables, indexes, and text or image objects. Setting the prefetch option to off disables prefetch for the specified object.
    • Data already sorted– if data is already sorted, this option saves index creation time.
  15. (Optional) Specify whether to create a local partitioned index.
  16. Click Finish to create the index.
Parent topic: Manage Tables