Creating Indexes Without Blocking Access to Data

Adaptive Server 15.7 SP100 includes the create index ... online parameter, which lets you create indexes without blocking access to the data you are indexing.

The syntax is:
create [unique] [clustered | nonclustered] index index_name
	on database.]owner.]table_name
	[with {...
			online,
	...}
For example, to create the index pub_dates_ix on the titles table with the online parameter, use:
create index pub_dates_ix
on titles (pub_id asc, pubdate desc)
with online

Except for the sorted_data parameter, Adaptive Server processes other create index parameters the same way, both with or without the online parameter. For example, if you include the reservepagegap parameter with the online parameter, Adaptive Server reserves the pages while creating the new data layer. However, if you create the index using the sorted_data option, Adaptive Server creates the index on the existing data layer.

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
    • Adaptive Server 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.
Note: Because create index ... online increments the schema count in the sysobjects row that reflects the table's state change, concurrent activity waiting for create index ... online to commit may encounter error 540 after create index ... online commits.