Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (P-Z)



Refreshes a text index.

REFRESH TEXT INDEX text-index-name ON [ owner.]table-name
[ WITH { 
      ISOLATION LEVEL isolation-level 
      | SHARE MODE } ]
  • WITH clause   Use the WITH clause to specify what kind of locks to obtain on the underlying base tables during the refresh. The types of locks obtained determine how the text index is populated and how concurrency for transactions is affected. The default value is read uncommited.

    You can specify the following WITH clause options:

    • ISOLATION LEVEL isolation-level   Use WITH ISOLATION LEVEL to change the isolation level for the execution of the refresh operation. For information about isolation levels, see Using transactions and isolation levels, and Isolation levels and consistency.

      The original isolation level is restored at the end of the statement execution.

    • EXCLUSIVE MODE   Use WITH EXCLUSIVE MODE if you do not want to change the isolation level, but want to guarantee that the data is updated to be consistent with committed data in the underlying tables. When using WITH EXCLUSIVE MODE, exclusive table locks are placed on all underlying base tables and no other transaction can execute queries, updates, or any other action against the underlying table(s) until the refresh operation is complete. If table locks cannot be obtained, the refresh operation fails and an error is returned. See Table locks.

    • SHARE MODE   Use WITH SHARE MODE to give read access on underlying tables to other transactions while the refresh operation takes place. When this clause is specified, shared table locks are obtained on all underlying base tables before the refresh operation is performed and until the refresh operation completes. See Table locks.

  • FORCE clause   Use this clause to specify the refresh method. If this clause is not specified, the database server decides whether to do an incremental update or a full rebuild based on how much of the table has changed. See Creating text indexes.

    • FORCE BUILD clause   Use this clause to force a complete rebuild of the text index.

    • FORCE INCREMENTAL clause   Use this clause to force an incremental update of the text index.


This statement can only be used on text indexes defined as MANUAL REFRESH or AUTO REFRESH.

When using the FORCE clause, you can examine the results of the sa_text_index_stats system procedure to decide whether a complete rebuild (FORCE BUILD), or incremental update (FORCE INCREMENTAL) is most appropriate. See sa_text_index_stats system procedure.

You cannot execute the REFRESH TEXT INDEX statement on a text index that is defined as IMMEDIATE REFRESH.


Must be the owner of the underlying table, or have either DBA authority or REFERENCES permission.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.


The following statement refreshes a text index called MarketingTextIndex, forcing it to be rebuilt.

REFRESH TEXT INDEX MarketingTextIndex ON MarketingInformation