Generating an index for a table in a database background

• US 10,489,367 B2
• Filed: 10/19/2015
• Issued: 11/26/2019
• Est. Priority Date: 11/25/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Li, Yue, Li, Zhao Wei, Wei, Dong Jie, Xu, Ling
• Primary Examiner(s): Leroux, Etienne P
• Assistant Examiner(s): Agharahimi, Farhad
• Application Number:
  US14/886,235
• Publication Number:
  US 20160147751A1
• Time in Patent Office: 1,499 Days
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2255 Hash tables
• CPC Class Codes: G06F 16/2272 Management thereof

Abstract

Embodiments of the present invention disclose a method, computer program product, and system for generating and manipulating an index for a table in a database. A query statement is received, the table of lines are searched, a result list is generated, and an index for the query statement is generated, wherein the index comprises information associated with the result list and the query condition. The query condition and the identifier of the table is extracted from the query statement, where the index is generated based on the query condition, the identifier of the table, and the result list. A hash value of the identifier of the table and the query condition is obtained, an association between the hash value and the result list to build the index is generated, and in response to a line of the plurality of lines in the table being modified, the index identified as invalid.

Claims

1. A computer implemented method for managing a database to increase response efficiency of a database system, the method comprising:

receiving a query statement to query a table in the database, wherein the query statement includes a table identifier and a query condition;

searching the table identified by the table identifier for a plurality of lines that satisfy the query condition in the query statement;

generating a result list based on line identifiers of one or more lines in the plurality of lines that satisfy the query condition;

generating a hash value based on the table identifier and the query condition, wherein the hash value is associated with the line identifiers of the result list;

generating a flag associated with a line validity state associated with line modification, wherein an initial condition of the validity state indicated by the flag is valid;

mapping the flag to each line identifier of the generated result list;

generating an index for the query statement, wherein the index comprises one or more lines in the plurality of lines associated with the results list and information associated with the result list and the query condition, the hash value, and the mapped flag, wherein the number of lines associated with the result list is compressed using a data storage technique when the number of lines is larger than a pre-configured threshold value, wherein the index is manually generated when a keyword is added at the end of the query statement to indicate the index is to be generated manually or the index is automatically generated in response to determining the query condition in a previous query statement matches at least one part of the second condition in a subsequent second statement; and

using the generated index to respond to a received data query statement querying data in the database.

2. The method of claim 1, wherein the index further comprises information associated with an identifier of the table.

3. The method of claim 2, wherein generating the index for the query statement further comprises:

extracting from the query statement, the query condition and the identifier of the table; and

building the index based on the query condition, the identifier of the table, and the result list.

4. The method of claim 1, further comprising:

in response to a line of the plurality of lines in the table being modified, setting the flag as false and identifying the index previously generated as invalid.

5. The method of claim 4, further comprising:

releasing the result list when it is determined the index is invalid because any line among the one or more lines in the table is modified.

6. The method of claim 1, further comprising:

manipulating the table based on the generated index, the manipulating the table based on the generated index, further includes;

parsing the query condition and generated result list from the index;

in response to receiving a second statement to manipulate the table, determining the query condition that matches at least one part of a second condition in the second statement; and

executing the second statement based on a plurality of lines in the table, which are indicated by identifiers in the result list.

7. The method of claim 6, wherein executing the second statement based on the plurality of lines in the table, which are indicated by identifiers in the result list further comprises:

in response to the query condition matching the second condition, executing an operation in the second statement which indicates identifiers in the results list, within the plurality of lines in the table.

8. The method of claim 6, wherein executing the second statement further comprises:

in response to the query condition matching one part of the second condition, searching for at least one line that satisfies the second condition, among the plurality of lines in the table which are indicated by identifiers in the result list; and

executing an operation in the second statement with where at least one line of the plurality of lines is found.

9. The method of claim 6, further comprising, committing the query statement and the second statement in one transaction of the database.

10. A computer system for managing a database to increase response efficiency of a database system, the computer system comprising:

one or more computer processors;

a computer-readable storage media;

program instructions stored on the computer-readable storage media for execution by at least one of the one or more processors, the program instructions comprising;

in response to receiving a query statement to query the table in the database, wherein the query statement includes a table identifier and a query condition, executing instructions to search the table identified by the table identifier for a plurality of lines that satisfy the query condition in the query statement;

instructions to generate a result list based on line identifiers of the one or more lines in the plurality of lines that satisfy the query condition;

instructions to generate a hash value based on the table identifier and the query condition, wherein the hash value is associated with the line identifiers of the result list;

instructions to generate a flag associated with a line validity state associated with line modification, wherein an initial condition of the validity state indicated by the flag is valid;

instructions to map the flag to each line identifier of the generated result list;

instructions to generate the index for the query statement, wherein the index comprises one or more lines in the plurality of lines associated with the results list and information associated with the result list and the query condition, the hash value, and the mapped flag, wherein the number of lines associated with the result list is compressed using a data storage technique when the number of lines is larger than a pre-configured threshold value, wherein the index is manually generated when a keyword is added at the end of the query statement to indicate the index is to be generated manually or the index is automatically generated in response to determining the query condition in a previous query statement matches at least one part of the second condition in a subsequent second statement; and

instructions to use the generated index to respond to a received data query statement querying data in the database.

11. The system of claim 10, wherein the index further comprises information associated with an identifier of the table.

12. The system of claim 11, wherein instructions to generate further comprises:

instructions to extract from the query statement, the query condition and the identifier of the table; and

instructions to build the index based on the query condition, the identifier of the table and the result list.

13. The system of claim 10, further comprising:

in response to any line among the one or more lines in the table being modified, instructions to set the flag as false and instructions to identify the index previously generated as invalid.

14. The system of claim 13, further comprising:

instructions to release the result list when it is determined the index is invalid because any line among the one or more lines in the table is modified.

15. The system of claim 10, further comprising:

instructions to manipulate the table based on the generated index, the instructions to manipulate the table based on the generated index, further includes;

instructions to parse the query condition and generated result list from the index;

in response to receiving a second statement to manipulate the table, instructions to determine the query condition that matches at least one part of a second condition in the second statement; and

instructions to execute the second statement based on a plurality of lines in the table, which are indicated by identifiers in the result list.

16. The system of claim 15, wherein instructions to execute the second statement based on the plurality of lines in the table, which are indicated by identifiers in the result list further comprises:

in response to the query condition matching the second condition, instructions to execute an operation in the second statement which indicates identifiers in the results list, within the plurality of lines in the table.

17. The system of claim 15, wherein the instructions to execute the second statement further comprises:

in response to the query condition matching one part of the second condition, instructions to search for at least one line that satisfies the second condition, among the plurality of lines in the table which are indicated by identifiers in the result list; and

instructions to execute an operation in the second statement with where at least one line of the plurality of lines is found.

18. A computer program product for managing a database to increase response efficiency of a database system, the computer program product comprising:

one or more non-transitory computer-readable storage media and program instructions stored on the one or more computer-readable storage media, the program instructions comprising;

in response to receiving a query statement to query the table in the database, wherein the query statement includes a table identifier and a query condition, executing instructions to search the table identified by the table identifier for a plurality of lines that satisfy the query condition in the query statement;

instructions to generate a result list based on line identifiers of the one or more lines in the plurality of lines that satisfy the query condition;

instructions to generate a hash value based on the table identifier and the query condition, wherein the hash value is associated with the line identifiers of the result list;

instructions to generate a flag associated with a line validity state associated with line modification, wherein an initial condition of the validity state indicated by the flag is valid;

instructions to map the flag to each line identifier of the generated result list;

instructions to generate the index for the query statement, wherein the index comprises one or more lines in the plurality of lines associated with the results list and information associated with the query condition, the hash value, and the mapped flag, wherein the number of lines associated with the result list is compressed using a data storage technique when the number of lines is larger than a pre-configured threshold value, wherein the index is manually generated when a keyword is added at the end of the query statement to indicate the index is to be generated manually or the index is automatically generated in response to determining the query condition in a previous query statement matches at least one part of the second condition in a subsequent second statement; and

instructions to use the generated index to respond to a received data query statement querying data in the database.