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.
IMMEDIATE REFRESH IMMEDIATE REFRESH text indexes are refreshed when data in the underlying table or materialized view changes, and are recommended for base tables only when the data must always be up-to-date, when the indexed columns are relatively short, or when the data changes are infrequent.
The default refresh type for text indexes is IMMEDIATE REFRESH. Materialized view text indexes only support IMMEDIATE REFRESH.
If you have an AUTO REFRESH 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 or materialized view during this initial refresh.
AUTO REFRESH AUTO 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.
AUTO REFRESH text indexes may also be refreshed more often than the interval specified when one or more of the following conditions are true:
the time since the last refresh is larger than the refresh interval.
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).
the deleted length exceeds 50% of the total index size (doc_length). In this case, a full rebuild is always performed instead of an incremental update.
AUTO REFRESH text indexes are refreshed using isolation level 0.
An AUTO 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 AUTO REFRESH text index manually using the REFRESH TEXT INDEX statement.
AUTO REFRESH text indexes are not refreshed during a reload unless the -g option is specified for 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 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. In the following example, all MANUAL REFRESH text indexes are refreshed using a refresh interval that is passed as an argument, and rules that are similar to those used for AUTO REFRESH text indexes.
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_utc IS null OR cast(pending_length as float) / ( IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2 OR DATEDIFF( MINUTE, CURRENT UTC TIMESTAMP, last_refresh_utc ) > 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.
A 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.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|