Query processing management in a database management system

• US 10,565,201 B2
• Filed: 11/04/2016
• Issued: 02/18/2020
• Est. Priority Date: 11/04/2016
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Beavin, Thomas A., Hao, Zhang, Li, Shuo, Wang, Xiaobo, Wei, Ke Wei
• Primary Examiner(s): Meng, Jau Shya
• Application Number:
  US15/344,431
• Publication Number:
  US 20180129708A1
• Time in Patent Office: 1,201 Days
• Field of Search: None
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

Disclosed aspects relate to query processing management in a database management system (DBMS). The DBMS may detect that a query has a set of subqueries. The DBMS may detect that the set of subqueries indicates a set of data structures. A lead data structure may be selected from the set of data structures using a selection criterion. A set of input columns may be identified from the lead data structure of the set of data structures. A set of output columns may be identified from the set of data structures other than the lead data structure. A hash data structure may be constructed using the set of input columns and the set of output columns. A cache may be built based on the hash data structure.

Claims

1. A computer-implemented method for query processing management in a database management system (DBMS), the method comprising:

detecting, by the DBMS, that a query has a set of subqueries;

detecting, by the DBMS, that the set of subqueries relate to a set of data structures;

selecting, from the set of data structures, a lead data structure of the set of data structures using a selection criterion;

identifying, from the lead data structure of the set of data structures, a set of input columns;

identifying, from the set of data structures other than the lead data structure, a set of output columns;

constructing, using the set of input columns and the set of output columns, a hash data structure, wherein the hash data structure comprises a plurality of hash values correlated to a plurality of data input columns; and

performing a join operation with the lead data structure and a second data structure of the set of data structure comprising;

searching the plurality of hash values for a single hash value matching a second hash value for a second data column of the second data structure; and

based on not finding a match between the second hash value and the plurality of hash values, performing a join operation between the lead data structure and the second data structure and building a dynamic join result cache comprising a plurality of hash values that using input columns resulting from the join operation of the lead data structure and the second data structure.

2. The method of claim 1, wherein:

the set of data structures includes a set of tables;

a subquery of the set of subqueries includes a FROM clause;

the FROM clause indicates the set of tables;

the query has a set of predicates;

the set of predicates indicates the set of input columns;

the lead data structure includes the set of input columns;

the query has a SELECT statement;

the SELECT statement indicates the set of output columns;

the set of data structures includes the set of output columns; and

the set of output columns is external with respect to the lead data structure.

3. The method of claim 1, further comprising:

preserving the query to keep the query unchanged with respect to semantic features of the query.

4. The method of claim 1, further comprising:

sustaining a set of indexes of the set of data structures to maintain the set of indexes without creation of a new index to process the query.

5. The method of claim 1, further comprising:

configuring the selection criterion based on a primary key criterion.

6. The method of claim 1, further comprising:

configuring the selection criterion based on a unique index criterion.

7. The method of claim 1, further comprising:

configuring the selection criterion based on a sequence criterion.

8. The method of claim 1, further comprising:

configuring the selection criterion based on a cost estimation criterion.

9. The method of claim 8, further comprising:

calculating, with respect to using a first candidate lead data structure of the set of data structures, a first estimated size of a cache;

calculating, with respect to using a second candidate lead data structure of the set of data structures, a second estimated size of the cache;

comparing the first and second estimated sizes of the cache;

determining that the second estimated size of the cache exceeds the first estimated size of the cache; and

selecting, as the lead data structure, the first candidate lead data structure.

10. The method of claim 8, further comprising:

configuring, based on the cost estimation criterion, the lead data structure to include a plurality of data structures of the set of data structures.

11. The method of claim 1, further comprising:

ordering, to manage cache efficiency, a set of records of the lead data structure.

12. The method of claim 1, further comprising:

identifying, from a set of predicates of the query, the set of input columns of the lead data structure; and

identifying, from a SELECT statement of the query, the set of output columns of the set of data structures.

13. The method of claim 1, further comprising:

establishing, to construct the hash data structure, a hash key group that is derived from a set of values of the set of input columns; and

establishing, to construct the hash data structure, a hash value group that is derived from a set of values of the set of output columns.

14. The method of claim 1, further comprising:

processing, by the DBMS using the cache, the query.

15. The method of claim 1, further comprising:

determining, by comparing a set of new inputs with the cache, a match;

bypassing a join operation; and

returning, based on the match with respect to the cache, a set of output values.

16. The method of claim 1, wherein the set of operational steps each occur in both:

a dynamic fashion to streamline query processing management, and

an automated fashion without user intervention.

17. A system for query processing management in a database management system (DBMS), the system comprising:

a memory having a set of computer readable computer instructions, and

a processor for executing the set of computer readable instructions, the set of computer readable instructions including;

detecting, by the DBMS, that a query has a set of subqueries;

detecting, by the DBMS, that the set of subqueries relate to a set of data structures;

selecting, from the set of data structures, a lead data structure of the set of data structures using a selection criterion;

identifying, from the lead data structure of the set of data structures, a set of input columns;

identifying, from the set of data structures other than the lead data structure, a set of output columns;

constructing, using the set of input columns and the set of output columns, a hash data structure, wherein the hash data structure comprises a plurality of hash values correlated to a plurality of data input columns; and

performing a join operation with the lead data structure and a second data structure of the set of data structure comprising;

searching the plurality of hash values for a single hash value matching a second hash value for a second data column of the second data structure; and

based on not finding a match between the second hash value and the plurality of hash values, performing a join operation between the lead data structure and the second data structure and building a dynamic join result cache comprising a plurality of hash values that using input columns resulting from the join operation of the lead data structure and the second data structure.

18. A computer program product for query processing management in a database management system (DBMS), the computer program product comprising a computer readable storage medium having program instructions embodied therewith, wherein the computer readable storage medium is not a transitory signal per se, the program instructions executable by a processor to cause the processor to perform a method comprising:

detecting, by the DBMS, that a query has a set of subqueries;

detecting, by the DBMS, that the set of subqueries relate to a set of data structures;

selecting, from the set of data structures, a lead data structure of the set of data structures using a selection criterion;

identifying, from the lead data structure of the set of data structures, a set of input columns;

identifying, from the set of data structures other than the lead data structure, a set of output columns;

constructing, using the set of input columns and the set of output columns, a hash data structure, wherein the hash data structure comprises a plurality of hash values correlated to a plurality of data input columns; and

performing a join operation with the lead data structure and a second data structure of the set of data structure comprising;

searching the plurality of hash values for a single hash value matching a second hash value for a second data column of the second data structure; and

based on not finding a match between the second hash value and the plurality of hash values, performing a join operation between the lead data structure and the second data structure and building a dynamic join result cache comprising a plurality of hash values that using input columns resulting from the join operation of the lead data structure and the second data structure.