Refreshing a full-text search index in a partitioned database

• US 8,620,924 B2
• Filed: 03/06/2012
• Issued: 12/31/2013
• Est. Priority Date: 03/06/2012
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Behnen, Marion, Mundkur, Shantanu S.
• Primary Examiner(s): Vital, Pierre
• Assistant Examiner(s): Brooks, David T
• Application Number:
  US13/413,399
• Publication Number:
  US 20130238628A1
• Time in Patent Office: 665 Days
• Field of Search: None
• US Class Current: 707/741
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning
• CPC Class Codes: G06F 16/31 Indexing; Data structures therefor; Storage structures

Abstract

A full-text search index for a table stored in a set of partitions of a database is refreshed. At a first point in time, table row distribution information and a table partitioning list are captured. At a second point in time, it is determined that an index refresh operation is to be performed. The captured table row distribution information is compared with current table row distribution information to determine whether there is at least one difference. In response to the determining that there is at least one difference, a text index distribution is re-aligned for any table rows that are affected by the change in table data row distribution by: generating a new table partitioning list, and indexing documents associated with table data rows that moved from one partition in the captured table partitioning list to another partition in the new table partitioning list.

Claims

1. A method for refreshing a full-text search index for a table stored in a set of partitions of a database, wherein a portion of the table is stored in each of the partitions, comprising:

at a first point in time, with a processor of a computer, capturing table row distribution information and a table partitioning list identifying the set of partitions, wherein the table row distribution information has a slot for each table row and indicates a partition in which the table row is stored; and

at a second point in time,

in response to at least one of a partition being added and a partition being dropped from the set of partitions,

moving table rows among the set of partitions to change table data row distribution; and

updating the table row distribution information by updating the slot for each table row to indicate the partition in which the table row is stored;

determining that an index refresh operation is to be performed due to the change in the table data row distribution;

comparing the captured table row distribution information with current table row distribution information to determine whether there is at least one difference; and

in response to the determining that there is at least one difference, re-aligning a text index distribution for any table rows that are affected by the change in the table data row distribution by;

generating a new table partitioning list that the full-text search index is to be distributed over; and

indexing documents associated with the table rows that moved from one partition in the captured table partitioning list to another partition in the new table partitioning list, wherein the documents associated with the table rows that did not move from one partition in the captured table partitioning list to another partition in the new table partitioning list are not re-indexed.

2. The method of claim 1, wherein the set of partitions comprises a first set of partitions and further comprising:

identifying a change in partitions by comparing the captured table partitioning list with the new table partitioning list.

3. The method of claim 1, further comprising:

consolidating index refresh operations due to incremental updates of a base table with index refresh operations due to data redistribution.

4. The method of claim 1, further comprising:

creating a new full-text search index partition for each added partition.

5. The method of claim 1, further comprising:

dropping a full-text search index partition for each dropped partition.

6. A computer program product for refreshing a full-text search index for a table stored in a set of partitions of a database, wherein a portion of the table is stored in each of the partitions, the computer program product comprising:

a non-transitory computer readable storage medium having computer readable program code embodied therein, wherein the computer readable program code, when executed by a processor of a computer, is configured to perform operations of;

at a first point in time, with a processor of a computer, capturing table row distribution information and a table partitioning list identifying the set of partitions, wherein the table row distribution information has a slot for each table row and indicates a partition in which the table row is stored; and

at a second point in time, in response to at least one of a partition being added and a partition being dropped from the set of partitions, moving table rows among the set of partitions to change table data row distribution; and

updating the table row distribution information by updating the slot for each table row to indicate the partition in which the table row is stored;

determining that an index refresh operation is to be performed due to the change in the table data row distribution;

comparing the captured table row distribution information with current table row distribution information to determine whether there is at least one difference; and

in response to the determining that there is at least one difference, re-aligning a text index distribution for any table rows that are affected by the change in the table data row distribution by;

generating a new table partitioning list that the full-text search index is to be distributed over; and

indexing documents associated with the table rows that moved from one partition in the captured table partitioning list to another partition in the new table partitioning list, wherein the documents associated with the table rows that did not move from one partition in the captured table partitioning list to another partition in the new table partitioning list are not re-indexed.

7. The computer program product of claim 6, wherein the set of partitions comprises a first set of partitions and further comprising:

identifying a change in partitions by comparing the captured table partitioning list with the new table partitioning list.

8. The computer program product of claim 6, further comprising:

consolidating index refresh operations due to incremental updates of a base table with index refresh operations due to data redistribution.

9. The computer program product of claim 6, further comprising:

creating a new full-text search index partition for each added partition.

10. The computer program product of claim 6, further comprising:

dropping a full-text search index partition for each dropped partition.

11. A computer system for refreshing a full-text search index for a table stored in a set of partitions of a database, wherein a portion of the table is stored in each of the partitions, comprising:

a processor; and

a storage device coupled to the processor, wherein the storage device has stored thereon a program, and wherein the processor is configured to execute instructions of the program to perform operations, wherein the operations comprise;

at a first point in time, with a processor of a computer, capturing table row distribution information and a table partitioning list identifying the set of partitions, wherein the table row distribution information has a slot for each table row and indicates a partition in which the table row is stored; and

at a second point in time,

in response to at least one of a partition being added and a partition being dropped from the set of partitions,

moving table rows among the set of partitions to change table data row distribution; and

updating the table row distribution information by updating the slot for each table row to indicate the partition in which the table row is stored;

determining that an index refresh operation is to be performed due to the change in the table data row distribution;

comparing the captured table row distribution information with current table row distribution information to determine whether there is at least one difference; and

in response to the determining that there is at least one difference, re-aligning a text index distribution for any table rows that are affected by the change in the table data row distribution by;

generating a new table partitioning list that the full-text search index is to be distributed over; and

indexing documents associated with the table rows that moved from one partition in the captured table partitioning list to another partition in the new table partitioning list, wherein the documents associated with the table rows that did not move from one partition in the captured table partitioning list to another partition in the new table partitioning list are not re-indexed.

12. The computer system of claim 11, wherein the set of partitions comprises a first set of partitions and further comprising:

identifying a change in partitions by comparing the captured table partitioning list with the new table partitioning list.

13. The computer system of claim 11, further comprising:

consolidating index refresh operations due to incremental updates of a base table with index refresh operations due to data redistribution.

14. The computer system of claim 11, further comprising:

creating a new full-text search index partition for each added partition.

15. The computer system of claim 11, further comprising:

dropping a full-text search index partition for each dropped partition.