ASE 15.7 SP 131 can't create online index

[former_member273622]

create index index1 on table1(archiveID,file_id) with online in ASE 15.7 SP 131 will lock the whole table.

table has a non clustered unique index.

Any tips much appreciate it

Isabella

[former_member272879]

Isabella,

Please try SP132.  We were able to run an online index creation without breaking the transaction log or have it hold an exclusive table lock.

Databases | SAP Support Portal

2073343 - SYB: Release information for SAP ASE 15.7 SP13x

With “enforce dump tran sequence” db option enabled, in order to create indexes online in pll, you will need the defer_recovery option as like so:

create index x1_ind on x1 (col1)

with online,

consumers = 2,

defer_recovery = <manual/auto/parallel/none/all>

This option means : In the case of LOAD TRAN, with the creation of an index tagged as having its recovery deferred, and the transaction being within the scope of the LOAD TRAN, recovery of the index is deferred until after the load sequence has completed and ONLINE DATABASE has been done.

An empty index will remain at the end of the load sequence, assuming that load sequence did not process a DROP INDEX command for the same index. The index will be marked in a way that indicates that it is unusable by any queries and that it needs re-creation. After the database has been onlined, the index can either be dropped with the DROP INDEX command or re-created with a new CREATE INDEX command.

It is possible to perform a DUMP TRAN after a parallel CREATE INDEX operation, provided deferred recovery of the index was requested.

The values accepted by this option:

PARALLEL - defer only parallel CREATE INDEXes
MANUAL - manually create deferred indexes after ONLINE DATABASE
AUTO - automatically create deferred indexes during ONLINE DATABASE
NONE - automatically drop deferred indexes during ONLINE DATABASE

(Instructions were provided to me by Madhvi P.)

[former_member272879]

Here is another word of warning...

1943890 - SYB: Release information for SAP Sybase ASE 15.7.0.110 with SAP Business Suite

1. Create Index Online
   SAP Sybase ASE 15.7 SP100 introduced a new feature 'CREATE INDEX ...ONLINE'.
   It is not possible to dump the transaction log sequence after creating an index online before having performed a database dump.
   Hence, you cannot use the feature in a production environment where recoverability has to be guaranteed at all times.

So even if it works, it may be something that is too risky to be run in a production environment.  Hopefully a true online index create is released officially in a new patch soon .

[former_member182259]

While that warning is there, one aspect is sometimes you have to be smarter than the documentation.   Often you find '1' in one place in th documentation and another '1' somewhere else, but you have to do the 1+1 on your own to get 2 (our documentation fault - been arguing this problem for decades - sorry).    Sooo...nothing stops you from the following sequence:

dump database

dump tran...

dump tran....

create index ...with online

dump database ....cumulative

dump tran

dump tran

....

Which would be fully recoverable with the sequence

load full database dump

load cumulative dump

load tran dumps after cumulative dump

....yes, there is the risk of data loss BETWEEN the time of the last log dump and the cumulative dump on anything that happens during that period - but that is the SAME risk that would happen say between two log dumps.

One aspect to consider about a create index (or any large DDL operation) is that you are likely inserting a TON...absolute TON of log records - and of necessity, all part of a single system transaction.   Therefore, the logging of the index records would be quite extensive and would need a lot of transaction log space - and of course, require extra time for a subsequent log dump.

[former_member187136]

Isabella,

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

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 thereservepagegap 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. 

Regards

Kiran Kumar A

[former_member273622]

Hi,

this is what you found in Sybase documentation but still there is not mentioning  create index online will have same behavior like in ASE 12.5, because this is what we seen, full table locking.

We upgrade our ASE to 15.7 SP 131 to be able to use this feature for new index non clustered on a large table with 900 mill rows, and found out we can not create the index without blocking the table access for hours. Do you know if there is a way to find out how long will require an exclusive table lock ( the create index online) ?

Thank you

Isabella

[former_member182259]

I am pretty sure that a create index (even in the traditional sense) never grabbed an exclusive table lock - it only used shared locks (think about it - an exclusive lock would make no sense).   There *might* be an exclusive lock on the row in sysindexes, systabstats, sysstatistics being manipulated/created, but....that would be expected.   I suspect your exclusive lock was due to something else - e.g. a concurrent reorg that was not using online (if you check carefully, for example, there are instances - e.g. when a forwarded row is hit - that a reorg escalates to a table lock).

[former_member273622]

Hi Jeff,

Here is the output from monlock table when runing  the index online, first the create index will try get an exclusive table lock for almost 165 sec if this fails the ASE will terminate the process, if  successfull may run  with exclusive table lock for 13-20 min depends on the table size , next step: l move to :exclusive intent for user table and share intend  for sysstatistics table  which can run for hours depends on the table size ( in my case we have round robin partition table  with  aprox  900 mil rows)

sp__monlock 1034

[

SPID        ObjName LockState LockType LockLevel WaitTime

----------- ---------------------------------------- -------------------- -------------------- ------------------------------ -----------

        1034 ad.mfs_files Requested exclusive table      TABLE 312

        1034 ad.mfs_files      Granted exclusive intent TABLE NULL

sp__monlock 1034

go

SPID        ObjName LockState LockType LockLevel WaitTime

----------- ---------------------------------------- -------------------- -------------------- ------------------------------ -----------

        1034 ad.mfs_files Granted exclusive intent TABLE NULL

        1034 ad.sysstatistics Granted shared intent        TABLE NULL

        1034 tempdb_sa.sysstatistics Granted shared intent        TABLE NULL