# US 2022/0027335 A1 · Active-active system index management

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2022/0027335 A1
- Document ID: US20220027335A1
- Status: Published application
- Kind: A1
- Application number: 16/934,059
- Filed: 2020-07-21
- Published: 2022-01-27
- Earliest claimed: 2020-07-21
- Assignees: International Business Machines Corporation
- Applicants: International Business Machines Corporation
- Inventors: Xue Bo; Xiaobo Wang; Shuo Li; Sheng Yan Sun; He Fang Zhang; Yue Li
- Web page: https://patentgrep.com/patent/US20220027335A1

## CPC

- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 9/5016: {the resource being the memory}
- G06F 9/544: {Buffers; Shared memory; Pipes}
- G06F 16/1727: {Details of free space management performed by the file system (saving storage space on storage systems G06F3/0608; management of blocks in storage devices G06F3/064)}
- G06F 16/1734: {Details of monitoring file system events, e.g. by the use of hooks, filter drivers, logs}
- G06F 16/2228: {Indexing structures}
- G06F 2209/5011: Pool
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A computer-implemented method for managing an index table associated with a data table includes obtaining an index entry associated with a key in the data table. The computer-implemented method further includes determining whether an index page associated with the index entry is in a buffer pool, wherein the buffer pool includes a plurality of index pages loaded from the index table and at least one temp index. The computer-implemented method further includes adding, in response to determining that the index page associated with the index entry is not in the buffer pool, the index entry into the at least one temp index. The computer-implemented method further includes consolidating the at least one temp index into the index table.

## Claims

### Claim 1

A computer-implemented method for managing an index table associated with a data table, the method comprising:
obtaining an index entry associated with a key in the data table;
determining whether an index page associated with the index entry is in a buffer pool, wherein the buffer pool includes a plurality of index pages loaded from the index table and at least one temp index;
adding, in response to determining that the index page associated with the index entry is not in the buffer pool, the index entry into the at least one temp index; and
consolidating the at least one temp index into the index table.

### Claim 2 (depends on 1)

The computer-implemented method of claim 1, wherein the at least one temp index includes a first temp index, and wherein the first temp index is consolidated into the index table in response to the first temp index having an available storage capacity below a predetermined threshold.

### Claim 3 (depends on 1)

The computer-implemented method of claim 1, wherein the at least one temp index includes a first temp index and a second temp index, and wherein:
the index entry is added into the second temp index and the first temp index is consolidated into the index table in response to the first temp index having an available storage capacity below a predetermined threshold; and
the index entry is added into the first temp index and the second temp index is consolidated into the index table in response to the second temp index having an available storage capacity below a predetermined threshold.

### Claim 4 (depends on 1)

The computer-implemented method of claim 1, wherein the at least one temp index includes a plurality of temp indexes, and wherein consolidating the at least one temp index into the index table comprises, in response to each of a predetermined number of temp indexes in the plurality of temp indexes having an available storage capacity below a predetermined threshold:
merging, in response to key ranges of two or more temp indexes of the predetermined number of temp indexes being at least partially overlapping, the two or more temp indexes into a merged index;
consolidating index pages included in the merged index with the index table;
deleting, in response to consolidating the index pages included in the merged index with the index table, the merged indexes;
consolidating index pages included in the unmerged temp indexes of the predetermined number of temp indexes with the index table; and
deleting, in response to consolidating the index pages included in the unmerged temp indexes, the index pages included in the unmerged temp indexes.

### Claim 5 (depends on 1)

The computer-implemented method of claim 1, wherein consolidating the at least one temp index into the index table further comprises:
identifying the index entries in each of the at least one temp index having a key range across at least two successive index pages;
fetching from the index table one or more index pages associated with the index entries in the at least one temp index, wherein the one or more index pages includes the index pages associated with the identified index entries;
for each of the at least one temp index:
adding the identified index entries in the temp index into their associated index pages of the fetched index pages to update the fetched index pages;
deleting the identified index entries from the temp index;
adding any unidentified index entries in the temp index into their associated index pages of the fetched index pages to update the fetched index pages; and
deleting the added unidentified index entries from the temp index; and
storing the updated index pages in the index table to replace the fetched index pages.

### Claim 6 (depends on 1)

The computer-implemented method of claim 1, wherein the index entry includes the key, a row ID (RID) indicating a row address of the key in the data table, and a flag indicating an availability of the index entry, and wherein the index entry is obtained in response to at least one of: the key being inserted into the data table at the row address; the key at the row address being deleted from the data table, and the key replacing a current key at the row address in the data table.

### Claim 7 (depends on 6)

The computer-implemented method of claim 6, wherein the index entry is obtained in response to the key replacing a current key at the row address in the data table, and wherein adding the index entry into the at least one temp index comprises:
dividing the index entry into a first index entry including the current key, the RID, and a first flag indicating the first index entry being unavailable, and a second index entry including the key, the RID and a second flag indicating the second index entry being available;
adding the first index entry into one of the at least one temp index; and
adding the second index entry into the one of the at least one temp index.

### Claim 8 (depends on 6)

The computer-implemented method of claim 6, wherein the at least one temp index includes a first temp index and a second temp index, wherein the first temp index includes an indicator indicating index entries therein are to be deleted from the index table, and wherein adding the index entry into the at least one temp index comprises:
adding, in response to the index entry being obtained in response to the key being inserted into the data table at the row address, the index entry into the second temp index;
adding, in response to the index entry being obtained in response to the key at the row address being deleted from the data table, the index entry into the first temp index; and
in response to the index entry being obtained in response to the key replacing the current key at the row address in the data table:
dividing the index entry into a first index entry including the current key, the RID, and the first flag indicating the first index entry being unavailable and a second index entry including the key, the RID, and the second flag indicating the second index entry being available;
adding the first index entry into the first temp index; and
adding the second index entry into the second temp index.

### Claim 9 (depends on 6)

The computer-implemented method of claim 6, further comprising, in response to receiving a select statement including a target key:
searching the index entry having the target key in the available index entries in the at least one temp index to find its corresponding RID; and
searching the index entry having the target key in the available index entries in the index table to find its corresponding RID,
wherein a flag of each of the available index entries indicates the index entry is available.

### Claim 10 (depends on 1)

The computer-implemented method of claim 1, further comprising:
obtaining the index entry based, at least in part, on executing one of a plurality of statements in a received log;
reading the plurality of statements from the received log; and
storing the plurality of statements.

### Claim 11 (depends on 1)

The computer-implemented method of claim 1, further comprising:
creating one or more temp indexes.

### Claim 12

A computer system for managing an index table associated with a data table, the computer system comprising:
one or more computer processors,
one or more computer readable storage media, and
program instructions stored on the computer readable storage media for execution by at least one of the one or more processors, the program instructions including instructions to:
obtain an index entry associated with a key in the data table;
determine whether an index page associated with the index entry is in a buffer pool, wherein the buffer pool includes a plurality of index pages loaded from the index table and at least one temp index;
add, in response to determining that the index page associated with the index entry is not in the buffer pool, the index entry into the at least one temp index; and
consolidate the at least one temp index into the index table.

### Claim 13 (depends on 12)

The computer system of claim 12, wherein the at least one temp index includes a first temp index, and wherein the first temp index is consolidated into the index table in response to the first temp index having an available storage capacity below a predetermined threshold.

### Claim 14 (depends on 12)

The computer system of claim 12, wherein the at least one temp index includes a first temp index and a second temp index, and wherein:
the index entry is added into the second temp index and the first temp index is consolidated into the index table in response to the first temp index having an available storage capacity below a predetermined threshold; and
the index entry is added into the first temp index and the second temp index is consolidated into the index table in response to the second temp index having an available storage capacity below a predetermined threshold.

### Claim 15 (depends on 12)

The computer system of claim 12, wherein the at least one temp index includes a plurality of temp indexes, and wherein the program instructions to consolidate the at least one temp index into the index table comprises, in response to each of a predetermined number of temp indexes in the plurality of temp indexes having an available storage capacity below a predetermined threshold, further include instructions to:
merge, in response to key ranges of two or more temp indexes of the predetermined number of temp indexes being at least partially overlapping, the two or more temp indexes into a merged index;
consolidate index pages included in the merged index with the index table;
delete, in response to consolidating the index pages included in the merged index with the index table, the merged indexes;
consolidate index pages included in the unmerged temp indexes of the predetermined number of temp indexes with the index table; and
delete, in response to consolidating the index pages included in the unmerged temp indexes, the index pages included in the unmerged temp indexes.

### Claim 16 (depends on 12)

The computer system of claim 12, wherein the program instructions to consolidate the at least one temp index into the index table further include instructions to:
identify the index entries in each of the at least one temp index having a key range across at least two successive index pages;
fetch from the index table one or more index pages associated with the index entries in the at least one temp index, wherein the one or more index pages includes the index pages associated with the identified index entries;
for each of the at least one temp index:
add the identified index entries in the temp index into their associated index pages of the fetched index pages to update the fetched index pages;
delete the identified index entries from the temp index;
add any unidentified index entries in the temp index into their associated index pages of the fetched index pages to update the fetched index pages; and
delete the added unidentified index entries from the temp index; and
store the updated index pages in the index table to replace the fetched index pages.

### Claim 17 (depends on 12)

The computer system of claim 12, wherein the index entry includes the key, a row ID (RID) indicating a row address of the key in the data table, and a flag indicating an availability of the index entry, and wherein the index entry is obtained in response to at least one of: the key being inserted into the data table at the row address; the key at the row address being deleted from the data table, and the key replacing a current key at the row address in the data table.

### Claim 18 (depends on 17)

The computer system of claim 17, wherein the index entry is obtained in response to the key replacing a current key at the row address in the data table, and wherein the program instructions to add the index entry into the at least one temp index further include instructions to:
divide the index entry into a first index entry including the current key, the RID, and a first flag indicating the first index entry being unavailable, and a second index entry including the key, the RID and a second flag indicating the second index entry being available;
add the first index entry into one of the at least one temp index; and
add the second index entry into the one of the at least one temp index.

### Claim 19 (depends on 12)

The computer system of claim 12, wherein the program instructions further include instructions to:
create one or more temp indexes.

### Claim 20

A computer program product for managing an index table associated with a data table, the computer program product including one or more computer readable storage media and program instructions stored on the one or more computer readable storage media, the program instructions including instructions to:
obtain an index entry associated with a key in the data table;
determine whether an index page associated with the index entry is in a buffer pool, the buffer pool comprising a plurality of index pages loaded from the index table and at least one temp index;
add, in response to the index page associated with the index entry being not in the buffer pool, the index entry into the at least one temp index; and
consolidate the at least one temp index into the index table.
