Optimized database appliance

• US 20030212668A1
• Filed: 05/13/2002
• Published: 11/13/2003
• Est. Priority Date: 05/13/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: Netezza (International Business Machines Corporation)
• Inventors: Zane, Barry M., Hinshaw, Foster D., Metzger, John K.
• Granted Patent: US 7,010,521 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system from processing database queries allows for cost and locale based distribution for execution of database queries. The database queries are executed on execution engines that provide flexible configuration and overlapping functionality. The system reduces various costs, including elapsed time, required to perform database queries. The system provides processing of a database query using a database catalog comprising database table locality information, record locality information and execution engine information. A query optimizer receives the query and accesses the database catalog to create a query execution plan comprising locality-based database operations. A central database operation processor providing a first execution engine executes the query execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan. A second database operation processor providing a second execution engine executes the subplan received from the central database operation processor. At least one of the database operations can be executed on either the first execution engine or the second execution engine. A storage unit stores at least a portion of database tables and records. A data communications network connects the central database processor to the second database processor.

Claims

1. A system for processing a query on a database comprising:

a database catalog comprising database table locality information, record locality information, execution engine information and characteristics of the system components;

a query optimizer receiving the query and accessing the database catalog to create a query execution plan comprising locality-based database operations;

a central database operation processor providing a first execution engine executing the query execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan;

a second database operation processor providing a second execution engine executing the subplan received from the central database operation processor;

a storage unit storing at least a portion of database tables and records connected to at least one of the central database operation processor and the second database operation processor;

a data communications network connecting the central database operation processor to the second database operation processor; and

wherein at least one of the database operations can be executed on either the first execution engine or the second execution engine.

2. The system of claim 1 wherein the central database operation processor is configured as a symmetric multi-processing system and the second database operation processor is configured as a component of a massively parallel processing system.

3. The system of claim 1 wherein the first execution engine or the second execution engine processes data records received from the storage unit by performing one or more operations on the data records without materializing the data record into a memory.

4. The system of claim 1 wherein the first execution engine or the second execution engine processes data records received from the data communications network by performing one or more operations on the data records without materializing the data record into a memory.

5. The system of claim 1 wherein the first execution engine and the second execution engine have at least some different processing capabilities.

6. The system of claim 1 wherein the storage unit is electrically connected to the second database operation processor.

7. The system of claim 1 wherein the storage unit is optically connected to the second database operation processor.

8. The system of claim 1 wherein the storage unit is a disk drive.

9. The system of claim 1 wherein the storage unit is a random access memory.

10. The system of claim 1 wherein a portion of the database is stored on a storage unit connected to the central database operation processor.

11. The system of claim 1 wherein the first execution engine or the second execution engine reconfigures an assigned execution subplan to redirect one or more operations to another execution engine.

12. The system of claim 1 wherein each execution engine comprises a plurality of processing devices.

13. The system of claim 1 wherein each execution engine comprises a combination of processing devices.

14. The system of claim 13 wherein the execution engine comprises a general purpose central processing unit and a field programable gate array.

15. The system of claim 13 wherein the execution engine comprises a general purpose central processing and an application specific integrated circuit.

16. The system of claim 13 wherein the execution engine comprises a field programmable gate array and an application specific integrated circuit.

17. The system of claim 1 wherein a plurality of the storage units are grouped into sections and the locality information comprises:

a section indicator indicating which section stores a database table; and

a distribution indicator indicating whether the database table is striped or replicated to the section.

18. The system of claim 1 wherein a system optimizer tracks performance results from the query and reallocates the database tables and records across the storage units in order to improve query execution.

19. The system of claim 18 wherein the reallocation comprises striping the database tables and records across the storage units.

20. The system of claim 18 wherein the reallocation comprises replicating the database tables and records across the storage units.

21. The system of claim 1 wherein at least one of the first execution engine or the second execution engine dynamically track performance results from the executing queries and modify the query execution plan to improve performance.

22. The system of claim 1 wherein the execution engines dynamically track system configuration and resource utilization from the executing queries and modify the query execution plan to improve system resource utilization.

23. The system of claim 1 wherein at least one of the first execution engine or the second execution engine are dynamically reconfigured to process different database operations.

24. The system of claim 1 wherein a designated second database operation processor also serves as the central database operation processor.

25. A method for generating a distributed locality-based execution plan comprising:

receiving a database query;

parsing and validating the database query to produce a query tree; and

optimizing the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan containing instructions for a central database operation processor providing a first execution engine to execute the locality-based execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan to a second database operation processor providing a second execution engine, wherein at least one of the database operations can be executed on either the first execution engine or the second execution engine.

26. An apparatus for generating a distributed locality-based execution plan comprising:

means for receiving a database query;

means for parsing and validating the database query to produce a query tree; and

means for optimizing the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan containing instructions for a central database operation processor providing a first execution engine to execute the locality-based execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan to a second database operation processor providing a second execution engine, wherein at least one of the database operations can be executed on either the first execution engine or the second execution engine.

27. A computer program product comprising:

a computer usable medium for generating a distributed locality-based execution plan;

a set of computer program instructions embodied on the computer usable medium, including instructions to;

receive a database query;

parse and validating the database query to produce a query tree; and

optimize the query tree to produce a locality-based execution plan by accessing a database catalog comprising database table locality information, record locality information and execution engine information, the locality-based execution plan containing instructions for a central database operation processor providing a first execution engine to execute the locality-based execution plan by performing at least a portion of the locality-based database operations and distributing at least a portion of the locality-based database operations as a subplan to a second database operation processor providing a second execution engine, wherein at least one of the database operations can be executed on either the first execution engine or the second execution engine.