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 data retrieval and are useful for enforcing referential integrity. A table can have more than one index.

  1. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables, then choose one of the following:
    • User Tables
    • Proxy Tables
  2. Click the Name field of the table, then click the drop-down arrow and select Properties.
  3. In the left pane, click Indexes.
  4. Select New > Index.
  5. On the Name screen, enter a name for the index.
  6. On the Columns screen, select the columns to include in the index.
  7. (Optional) Click Add index column expression.
    1. Select Asc. or Desc. as the order of the index expression.
    2. (Optional) Enter a name for the expression.
  8. On the Database Segment screen, select the database segment on which to place the index.
  9. On the Database Cache screen, select a data cache for the index.
  10. (Optional) On the Key Type screen, select either or both of:
    • 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.
  11. (Optional) On the Duplicate Key screen:
    • Click Ignore duplicate keys to ignore duplicate keys rather than abort the transaction.
    • Choose whether to allow or ignore duplicate rows in a table.
  12. (Optional) On the Space Management screen:
    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.
  13. (Optional) On the Index Compression screen, specify whether or not to apply index compression.
  14. (Optional) On the Cache Strategy screen, 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.
    • Online– create indexes without blocking access to the data you are indexing.
      Restrictions:
      • User tables must include a unique index to use the create clustered index ... online command (creating nonclustered indexes does not have this restriction).
      • You can run create index ... online with a pll sort only on round robin partitioned tables
      • If you issue an insert, delete, update, or select command while create index … online or reorg … online are in the logical synchronization blocking phase:
        • The insert, delete, update, or select commands may wait and execute after create index … online or reorg … online are finished
        • SAP ASE may issue error message 8233.
      • You cannot:
        • Run dbcc commands and utility commands, such as reog rebuild, on the same table while you are simultaneously running create index ... online.
        • Run more than one iteration of create index ... online simultaneously.
        • Perform a dump transaction after running create index ... online. Instead, you can:
          • Run create index ... online, then dump the database, or
          • Run a blocking create index, then issue dump transaction.
        • Run create index ... online within a multistatement transaction.
        • Create a functional index using the online parameter
  15. (Optional) On the Local Partition screen, specify whether to create a local partitioned index.
  16. (Optional) Click Summary to review your selected options.
  17. Click Finish to create the index.
Parent topic: Tables