An index change resulting from a change to a value in an index configuration column (UPDATE statement) is achieved by two processes, which are the index entry change processing for the pre-update column value and the index entry change processing for the post-update column value.

Index entry change processing for the pre-update column value means deletion of the index key when a row's data item contains the corresponding index key. The index entry change processing for the post-update column value means addition of an index key when a row's data item contains the corresponding index key. Deletion of an index key and addition of an index key are performed in this order in order to prevent an increase in the size of the index during index change processing.

The following figure shows an example of the internal processing for index change.

Figure 4-1 Example of internal processing for index change

Explanation:

This example executes a transaction that updates from B to K the value in a column that has been indexed.

In step 1, the update transaction deletes index key B that corresponds to the pre-update column's value.

In step 2, index key K that corresponds to the post-update column's value is added.

If you perform an index search while index change processing is underway, the search results might differ. The following are two such cases:

• The row being updated is excluded from the search target.
• The row to be updated appears more than once in the search results.

(a) Case where the row being updated is excluded from the search target

This subsection discusses the case where the row being updated is excluded from the search target.

Updating indexes from the pre-search range to the post-search range

If you use the UPDATE statement to update an index key from the range for which an index search is currently underway to a range for which an index search has been completed, that index key will be excluded from the search target.

The following figure shows an example of a case where the row being updated is excluded from the search target.

Figure 4-2 Example of case where the row being updated is excluded from the search target (1)

Explanation:

This example executes a transaction that updates from K to D the value in a column that has been indexed; it does this at the same time that another transaction is retrieving the index.

Index retrieval steps 1 through 3 (key J) have been completed.

When key K is retrieved, the update transaction deletes it (step 4) and adds key D (step 5); therefore, the corresponding rows are excluded as a search target.

Supplement:

If the index search condition applies to all index keys (for a multicolumn index, all configuration columns), there is no problem because the search target rows no longer satisfy the search condition due to the UPDATE statement. However, if the search condition applies to the configuration columns of a multicolumn index that are not to be changed, a problem might arise, depending on the application. For details about countermeasures, see 4.1.4(4) UAP design guidelines.

Adding index keys to the post-search range

If you execute the INSERT statement before you perform an index search, but add an index key to the range in which the index search has been completed, that index key is excluded from the search target.

The following figure shows an example of a case where the row being updated is excluded from the search target.

Figure 4-3 Example of case where the row being updated is excluded from the search target (2)

Explanation:

This example executes a transaction that adds a row containing D in a column that has been indexed; it does this at the same time that another transaction is retrieving the index.

Index search step 3 (retrieval of key J) has already been completed in HiRDB's internal processing when the update transaction adds key D. As a result, the row for the added key D is excluded from the search target.

Supplement:

Because the row being updated is excluded from the search target, a problem might arise in applications that demand precise sequencing of events. For details about countermeasures, see 4.1.4(4) UAP design guidelines.

Changing index keys with the UPDATE statement

If, during index change processing, an index search is performed after the index key has been deleted, the corresponding row is excluded from the search target.

The following figure shows an example of a case where the row being updated is excluded from the search target.

Figure 4-4 Example of case where the row being updated is excluded from the search target (3)

Explanation:

This example executes a transaction that updates from D to E a column that has been indexed; it does this at the same time that another transaction is retrieving the index.

By the time the update transaction has deleted key D in step 1, the retrieval transaction has already completed retrieval of keys B and C in steps 2 and 3. Therefore, the row corresponding to the added key E in step 4 is excluded from the search target.

Supplement:

• If the pre-update index key is the same as the post-update index key, the index is not changed (there is no index key deletion or addition) unless the index satisfies any of the following conditions:
  The index includes a variable-length string-type configuration column with a defined length of 256 bytes or more.
  The index has a repetition column as a configuration column
  It is a substructure index.
  
• If the index search condition applies to all index keys, there is no problem because the search target rows no longer satisfy the search condition due to the UPDATE statement. However, if the search condition applies to the configuration columns of a multicolumn index that are not to be changed, a problem might arise, depending on the application. The following figure shows an example of a case where rows are no longer searched due to a change made to the index keys by the UPDATE statement.
  

  Figure 4-5 Example of case where rows are no longer searched due to a change made to the index keys by the UPDATE statement

  

Explanation:

This example adds the value 100 to the stock quantity because 100 items whose product code is 104 have been delivered.

The example uses a multicolumn index that consists of PCODE, PNAME, and SQUANTITY for searches. In this case, the row being updated in step 1 is not included in the search results because it is not a search target.

For details about countermeasures, see 4.1.4(4) UAP design guidelines.

(b) Case where the row to be updated appears more than once in the search results

This subsection discusses the case where the row to be updated appears more than once in the search results.

Updating indexes from the post-search range to the pre-search range

If WITHOUT LOCK WAIT is specified as the lock option, the row to be updated might appear more than once in the search results because the search results are not guaranteed until transactions are completed. The same applies to the WITHOUT LOCK NOWAIT lock option. Specifically, if you use the UPDATE statement to update an index key from a range for which the index search has been completed to a range for which the index search has not been completed, that index key will be searched again.

The following figure shows an example of a case where the row to be updated appears more than once in the search results.

Figure 4-6 Example of case where the row to be updated appears more than once in the search results

Explanation:

This example executes a transaction that updates from B to K a column that has been indexed; it does this at the same time that another transaction is retrieving the index.

When key B is retrieved in step 1, the update transaction can update the corresponding rows because lock has been released by the lock option.

Before the retrieval transaction retrieves key J in step 5, the update transaction had already updated the corresponding rows (by deleting key B in step 3 and adding key K in step 4). Therefore, the corresponding updated row appears for the second time in the search results in step 6 (retrieval of key K).

Supplement:

This causes no problem if the search results do not need to be precise (such as for statistical information), but it might be a problem if the search results are to be used by other applications that require highly accurate data. For details about countermeasures, see 4.1.4(4) UAP design guidelines.

The following table shows the possibility of obtaining different search results when an index is searched while the INSERT, UPDATE, and DELETE statements are executing.

Table 4-1 Possibility of obtaining different search results

Subsequent processing (search using index)	Prior processing
	INSERT statement	UPDATE statement	DELETE statement
SELECT statement	MC	MC#	NC
UPDATE statement	MC	MC	NC
DELETE statement	MC	MC	NC

Legend:

NC: Search results do not change.

MC: Search results might change (search target rows might be excluded from the search).

#

If the WITHOUT LOCK WAIT or WITHOUT LOCK NOWAIT lock option is used, a row to be updated might appear more than once in the search results.

If the subsequent processing is the INSERT statement, there is no possibility of getting different search results because the INSERT statement does not use indexes for search processing.

Whether a row to be updated appears more than once in the search results depends on how the index key values before and after update processing are changed. If the direction of the index key value change that takes place when the update transaction updates the index is the same as the direction of the index search by the retrieval transaction, the update target row might appear more than once in the search results, as shown in the following table:

Direction of index search	Direction of index key value change by the UPDATE statement
	Index key value becomes larger	Index key value becomes smaller	Index key value remains the same (same-value update)
Ascending order (from smaller key value to larger key value)	MC	NC	NC
Descending order (from larger key value to smaller key value)	NC	MC	NC

Legend:

NC: Search results do not change.

MC: Search results might change (the same row might appear more than once in the search results).

If you execute multiple transactions concurrently and develop an application that requires a precise sequence of events, you must lock the applicable table for the UAP and serialize index changes and index search processes. If this affects performance, evaluate the following measures:

1. Do not include in the index configuration columns any items to be updated.
2. If an item to be updated must be included in the index configuration columns for search frequency and condition reasons, evaluate whether only the index configuration columns that do not contain the item to be updated can be used for search conditions during index searching without causing any problem in the application. If there would be a problem, do not use this index in those search conditions.
3. If measure 2 above cannot be employed, include search results re-check processing in the UAP.