Query handling in databases with replicated data

• US 8,782,075 B2
• Filed: 05/08/2007
• Issued: 07/15/2014
• Est. Priority Date: 05/08/2007
• Status: Active Grant

• Current Assignee: Actian Corporation (HCL Technologies Limited), Paraccel Incorporated (HCL Technologies Limited)
• Original Assignee: Paraccel Incorporated (HCL Technologies Limited)
• Inventors: Zane, Barry M., Steinhoff, David E., Guthrie, Mario A.
• Primary Examiner(s): Young, Kevin L
• Application Number:
  US11/746,001
• Publication Number:
  US 20080281784A1
• Time in Patent Office: 2,625 Days
• US Class Current: 707/769
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

The same data is stored in at least two different database management systems (DBMS'"'"'s), including a columnar DBMS (320.2) and a non-columnar (e.g. row-oriented) DBMS (320.1). The columnar DBMS may provide fast responses to queries involving aggregation, and such queries can be routed to the columnar DBMS. A query may be routed to the non-columnar DBMS if for example the query execution involves only small DBMS tables on the non-columnar DBMS. In some situations, an execution time estimate can be obtained from each DBMS, and the query is routed to the DBMS providing the shortest estimate.

Claims

1. A database access method comprising:

(1) receiving a database query relating to data which is stored in each of a first database and a second database, wherein the first database and the second database are two separate and discrete databases, wherein the first database is managed by a first database management system that includes a first query processor configured to execute queries directed to the first database, wherein the second database is managed by a second database management system that is distinct from the first database management system, the second database management system including a second query processor configured to execute queries directed to the second database, wherein at least one attribute of the data stored in the first database is stored in columnar form, and wherein none of the attributes of the data stored in the second database are stored in columnar form;

(2) determining, externally from each of the first database management system and the second database management system, if the database query is to be executed on the first database or the second database based on the processing required by the database query and the form in which data is stored by the first and second database management systems, the determining comprising, checking if the database query involves computation of an aggregation and determining that the database query is to be executed on the first database responsive to the database query involving computation of an aggregation; and

(3) providing the database query to the first query processor or the second query processor based on the determination made in operation (2).

2. The method of claim 1 wherein the determination made at operation (2) is based at least in part on an estimation of a query execution time for executing the database query with the first database management system and/or on an estimation of a query execution time for executing the database query with the second database management system.

3. The method of claim 2 wherein operation (2) comprises generating, externally from each of the first database management system and the second database management system, a request for an execution plan for the database query for the first database management system and/or a request for an execution plan for the database query for the second database management system.

4. The method of claim 2 wherein operation (2) comprises:

generating, externally from the first database management system, a request for the first database management system to generate an execution plan for executing the database query; and

generating, externally from the second database management system, a request for the second database management system to generate an execution plan for executing the database query.

5. The method of claim 1 wherein operation (2) comprises obtaining a size or sizes of at least one table associated with the second database that includes data referenced by the database query.

6. The method of claim 1 wherein the first database is a columnar database.

7. The method of claim 1 wherein the first database management system is implemented in a first set of computer hardware, and the second database management system is implemented in a second set of computer hardware that is discrete and separate from the first set of computer hardware.

8. The method of claim 1 wherein the first database management system queries and obtains data from the second database management system, and the second database management system is unable to query the first database management system.

9. A system configured to execute database queries, the system comprising:

one or more processors, configured to execute one or more computer program modules, the computer program modules comprising;

a first database management system that manages a first database, wherein at least one attribute of the data within the first database is stored by the first database management system in columnar form, the first database management system including a first query processor configured to execute queries directed to the first database;

a second database management system that is distinct from the first database management system, the second database management system managing a second database, wherein the first database and the second database are two separate and discrete databases, and wherein no attribute of the data within the second database is stored by the second database management system in columnar form, the second database management system including a second query processor configured to execute queries directed to the second database; and

a query router that is external to each of the first database management system and the second database management system, the query router being configured to receive a database query for data stored in each of the first database and the second database, to determine whether the database query should be executed by the first query processor on the first database or by the second query processor on the second database based on the processing required by the database query and the form in which data is stored by the first and second database management systems, and to route the database query to the appropriate one of the first query processor or the second query processor, wherein the query router determines if the database query involves computation of an aggregation and routes the database query to the first query processor responsive to the database query involving computation of an aggregation.

10. The system of claim 9 wherein the first database management system includes a columnar database management system.

11. The database management system of claim 9 wherein each said first and second database management systems comprise an execution planner for providing an estimated execution time for executing a database query, and wherein the query router is further configured to request estimated execution times for execution of the database query from the execution planners associated within each of the first and second database management systems.

12. A system configured to route database queries, the system comprising:

one or more processors, configured to execute one or more computer program modules, the computer program modules comprising;

a query router configured to communicate with (i) a first database management system that manages a first database, the first database management system including a first query processor configured to execute queries directed to the first database, and (ii) a second database management system that manages a second database, the second database management system including a second query processor configured to execute queries directed to the second database,

wherein the first database and the second database are two separate and discrete databases,

wherein the first database management system is distinct from the second database management system, and the query router is external to both of the first and second database management systems,

wherein the first database management system stores at least one attribute of the data in the first database in columnar form, and the second database management system does not store any attributes of the data in the second database in columnar form, and

wherein the query router is further configured to receive a database query for data stored in each of the first database and the second database, to determine whether the database query should be executed by the first query processor on the first database or by the second query processor on the second database based on the processing required by the database query and the form in which data is stored by the first and second database management systems, and to route the database query to the appropriate one of the first database management system or the second database management system, wherein the query router determines if the database query involves computation of an aggregation and routes the database query to the first database management system responsive to the database query involving computation of an aggregation.

13. The system of claim 12 wherein the first database management system includes a columnar database management system.