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

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » Querying data » Text indexes

 

Text index refresh types

When you create a text index, you must also choose a refresh type. There are three refresh types supported for text indexes: immediate, automatic, and manual. You define the refresh type for a text index at creation time. With the exception of immediate text indexes, you can change the refresh type after creating the text index.

For information on how to set the refresh type see CREATE TEXT INDEX statement, and ALTER TEXT INDEX statement.

  • Immediate refresh (the default)   Immediate refresh text indexes are refreshed when data in the underlying table changes, and are recommended only when the data must always be up-to-date, or when the indexed columns are relatively short.

    The default refresh type for text indexes is immediate.

    If you have an automatic or manual refresh text index, you cannot alter it to be an immediate refresh text index. Instead, you must drop and recreate it as an immediate refresh text index.

    Immediate refresh text indexes support all isolation levels. They are populated at creation time, and an exclusive lock is held on the table during this initial refresh.

  • Automatic refresh   Automatic refresh text indexes are refreshed automatically at a time interval that you specify, and are recommended when some data staleness is acceptable. A query on a stale index returns matching rows that have not been changed since the last refresh. So, rows that have been inserted, deleted, or updated since the last refresh are not returned by a query.

    Automatic refresh text indexes may also be refreshed more often than the interval specified if either of the following conditions are true: the time since the last refresh is larger than the refresh interval, or the total length of all pending rows (pending_length as returned by the sa_text_index_stats system procedure) exceeds 20% of the total index size (doc_length as returned by sa_text_index_stats).

    Automatic refresh text indexes are refreshed using isolation level 0.

    An automatic refresh text index contains no data at creation time, and is not available for use until after the first refresh, which takes place usually within the first minute after the text index is created. You can also refresh an automatic refresh text index manually using the REFRESH TEXT INDEX statement.

    Automatic refresh text indexes are not refreshed during a reload unless the -g option is specified for dbunload. See Unload utility (dbunload).

  • Manual refresh   Manual refresh text indexes are refreshed only when you refresh them, and are recommended if data in the underlying table is rarely changed, or if a greater degree of data staleness is acceptable, or if you want to refresh after an event or a condition is met. A query on a stale index returns matching rows that have not been changed since the last refresh. So, rows that have been inserted, deleted, or updated since the last refresh are not returned by a query.

    You can define your own strategy for refreshing manual refresh text indexes. For example, you can use a procedure that refreshes all manual refresh text indexes using a refresh interval that is passed as an argument, and rules that are similar to those used for automatic refresh text indexes.

    In the following example, replace text-index-name, table-owner and table-name.

    CREATE PROCEDURE refresh_manual_text_indexes( 
       refresh_interval UNSIGNED INT )
    BEGIN
     FOR lp1 AS c1 CURSOR FOR
       SELECT ts.*
       FROM SYS.SYSTEXTIDX ti JOIN sa_text_index_stats( ) ts
       ON ( ts.index_id = ti.index_id )
       WHERE ti.refresh_type = 1 -- manual refresh indexes only
     DO
       BEGIN
        IF last_refresh IS null 
        OR cast(pending_length as float) / (
           IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2
        OR DATEDIFF( MINUTE, CURRENT TIMESTAMP, last_refresh )
           > refresh_interval THEN
         EXECUTE IMMEDIATE 'REFRESH TEXT INDEX ' || text-index-name || ' ON "'
         || table-owner || '"."' || table-name || '"';
        END IF;
       END;
      END FOR;
    END;

    At any time, you can use the sa_text_index_stats system procedure to decide if a refresh is needed, and whether the refresh should be a complete rebuild or an incremental update. See sa_text_index_stats system procedure.

    An manual refresh text index contains no data at creation time, and is not available for use until you refresh it. To refresh a manual refresh text index, use the REFRESH TEXT INDEX statement.

    Manual refresh text indexes are not refreshed during a reload unless the -g option is specified for dbunload. See Unload utility (dbunload).

See also