Transparent analytical query accelerator

• US 9,009,138 B2
• Filed: 06/17/2011
• Issued: 04/14/2015
• Est. Priority Date: 06/17/2011
• Status: Active Grant

• Current Assignee: Wrp IP Management, LLC
• Original Assignee: International Business Machines Corporation
• Inventors: Draese, Oliver, Hrle, Namik, Ruddy, James A.
• Primary Examiner(s): Wong, Leslie
• Application Number:
  US13/163,309
• Publication Number:
  US 20120323884A1
• Time in Patent Office: 1,397 Days
• Field of Search: 707/607, 707/713, 707/719, 707/600
• US Class Current: 707/713
• CPC Class Codes: G06F 16/245 Query processing

Abstract

A method and apparatus for transparent analytical query acceleration identifies within a database management system (DBMS) if a query is an analytical type. The query is processed with an analytical query processor if the query is the analytical type. In addition, the query is processed within the DBMS if the query is not the analytical type.

Claims

1. A method for transparent analytical query acceleration comprising:

identifying, by use of a processor, within a database management system (DBMS) a query as an analytical type in response to a predefined query element, wherein the predefined query element is one of the query not including an index scan and the query not including a where predicate, and a processing cost of processing the query with an analytical query processor being less than a processing cost of processing the query with a transactional query processor of a general purpose computer within the DBMS;

processing the query with the analytical query processor if the query is the analytical type; and

processing the query with the transactional query processor within the DBMS if the query is not the analytical type.

2. The method of claim 1, wherein the DBMS presents a single interface to an application for all queries.

3. The method of claim 1, wherein the DBMS manages security, integrity, recoverability, availability, and software lifecycle management for the DBMS, the analytical query processor, and an analytical schema.

4. The method of claim 1, wherein the transactional query processor processes the query on a transactional schema and the analytical query processor processes the query on an analytical schema.

5. The method of claim 4, wherein the analytical schema comprises data of the transactional schema organized by analytical schema rules.

6. The method of claim 1, wherein the identification is based on a heuristic comprising a comparison of the processing cost of processing the query with the analytical query processor and the processing cost of processing the query with the transactional query processor.

7. The method of claim 1, wherein the query is identified as the analytical type if k₁(c_a−

c_t)+k₂s+k₃w+k₄g>

T is true, where each k is a constant greater than zero, c_a is a cost of analytical processing, c_t , is a cost of transactional processing, s equals 1 if the query does not comprise an index scan, w is a number of where predicates in the query, g is a number of groups in the query, and T is an analytical threshold.

8. An apparatus comprising:

a computer readable storage medium storing computer readable program code executable by a processor, the computer readable program code comprising;

an optimizer identifying within a transactional database management system (DBMS) a query as an analytical type in response to a predefined query element, wherein the predefined query element is one of the query not including an index scan and the query not including a where predicate, and a processing cost of processing the query with an analytical query processor being less than a processing cost of processing the query with a transactional query processor of a general purpose computer within the DBMS;

the analytical query processor processing the query if the query is the analytical type; and

the transactional query processor processing the query within the DBMS if the query is not the analytical type.

9. The apparatus of claim 8, wherein the DBMS presents a single interface to an application for all queries.

10. The apparatus of claim 8, wherein the DBMS manages security, integrity, recoverability, availability and software lifecycle management for the DBMS, the analytical query processor, and an analytical schema.

11. The apparatus of claim 8, wherein the transactional query processor processes the query on a transactional schema and the analytical query processor processes the query on an analytical schema.

12. The apparatus of claim 11, wherein the analytical schema comprises data of the transactional schema organized by analytical schema rules and wherein the query is identified as the analytical type if k₁(c_a−

c_t)+k₂s+k₃w+k₄g>

T is true, where each k is a constant greater then zero, c_a is a cost of analytical processing, c₁ is a cost of transactional processing, s equals 1 if the query does not comprise an index scan, w is a number of where predicates in the query, g is a number of groups in the query, and T is an analytical threshold.

13. The apparatus of claim 8, wherein the identification is based on a heuristic comprising a comparison of the processing cost of processing the query with the analytical query processor and the processing cost of processing the query with the transactional query processor.

14. A computer program product for transparent analytical query acceleration, the computer program product comprising:

a computer readable storage medium having computer readable program code embodied therein, the computer readable program code configured to;

identify within a transactional database management system (DBMS) a query as an analytical type in response to a predefined query element, wherein the predefined query element is one of the query not including an index scan and the query not including a where predicate, and a processing cost of processing the query with an analytical query processor being less than a processing cost of processing the query with a transactional query processor of a general purpose computer within the DBMS;

process the query with the analytical query processor if the query is the analytical type; and

process the query with the transactional query processor within the DBMS if the query is not the analytical type.

15. The computer program product of claim 14, wherein the DBMS presents a single interface to an application for all queries.

16. The computer program product of claim 14, wherein the DBMS manages security, integrity, recoverability, availability and software lifecycle management for the DBMS, the analytical query processor, and an analytical schema.

17. The computer program product of claim 14, wherein the transactional query processor processes the query on a transactional schema and the analytical query processor processes the query on an analytical schema.

18. The computer program product of claim 17, wherein the analytical schema comprises data of the transactional schema organized by analytical schema rules.

19. The computer program product of claim 14, wherein the query is identified as k₁(c_a−

c_t)+k₂s+k₃w+k₄g>

T is true, where each k is a constant greater than zero, c_a is a cost of analytical processing, c_t is a cost of transactional processing, s equals 1 if the query does not comprise an index scan, w is a number of where predicates in the query, g is a number of groups in the query, and T is an analytical threshold.

20. A method for integrating a computer program product, comprising integrating computer readable program code into a computing system, wherein the code in combination with the computing system performs the following:

identifying within a transactional database management system (DBMS) a query as an analytical type in response to a predefined query element, wherein the predefined query element is one of the query not including an index scan and the query not including a where predicate, and a processing cost of processing the query with an analytical query processor being less than a processing cost of processing the query with a transactional query processor of a general purpose computer within the DBMS;

processing the query with the analytical query processor if the query is the analytical type; and

processing the query with the transactional query processor within the DBMS if the query is not the analytical type.

21. The method of claim 20, wherein the DBMS presents a single interface to an application for all queries.

22. The method of claim 20, wherein the DBMS manages security, integrity, recoverability, availability and software lifecycle management for the DBMS, the analytical query processor, and an analytical schema.

23. The method of claim 20, wherein the transactional query processor processes the query on a transactional schema and the analytical query processor processes the query on an analytical schema.

24. The method of claim 23, wherein the analytical schema comprises data of the transactional schema organized by analytical schema rules and wherein the query is identified as the analytical type if k₁(c_a−

c_t)+k₂s+k₃w+k₄g>

T is true, where each k is a constant greater than zero, c_a is a cost of analytical processing, c_t is a cost of transactional processing, s equals 1 if the query does not comprise an index scan, w is a number of where predicates in the query, g is a number of groups in the query, and T is an analytical threshold.

25. The method of claim 20, wherein the identification is based on a heuristic comprising a comparison of the processing cost of processing the query with the analytical query processor and the processing cost of processing the query with the transactional query processor.