Skip to Content

ASE 15.7 SP 131 can't create online index

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 09, 2015 at 09:45 PM

    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 .

    Add comment
    10|10000 characters needed characters exceeded

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

  • Dec 24, 2014 at 05:22 AM

    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.  RegardsKiran Kumar A

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Feb 23, 2015 at 10:02 PM

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

    Add comment
    10|10000 characters needed characters exceeded