QUERY HANDLING IN DATABASES WITH REPLICATED DATA

• US 20080281784A1
• Filed: 05/08/2007
• Published: 11/13/2008
• Est. Priority Date: 05/08/2007
• Status: Active Grant

• Current Assignee: Actian Corporation (HCL Technologies Limited)
• Original Assignee: Paraccel Incorporated (HCL Technologies Limited)
• Inventors: Zane, Barry M., Guthrie, Mario A., Steinhoff, David E.
• Granted Patent: US 8,782,075 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• 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) obtaining a database query referring to data which is stored in each of a plurality of databases, the plurality of databases comprising one or more first databases and one or more second databases, wherein at least one attribute of the data is stored in columnar form in each second database but not in any first database;

(2) determining if the query is to be executed on one or more of the first databases or one or more of the second databases;

(3) providing the query to the one or more of the first databases or the one or more of the second databases as determined in operation (2).

2. The method of claim 1 wherein operation (2) comprises estimating whether the query execution time is shorter on the one or more of the first databases or the one or more of the second databases.

3. The method of claim 2 wherein operation (2) comprises generating an execution plan for the query for at least one of the first and second databases to estimate the execution time.

4. The method of claim 2 wherein each first database is managed by a respective first DBMS, each second database is managed by a respective second DBMS, and operation (2) comprises:

requesting at least one first DBMS to generate an execution plan for executing the query on the respective first database and to provide an estimated execution time based on the at least one first DBMS'"'"'s execution plan; and

requesting at least one second DBMS to generate an execution plan for executing the query on the respective second database and to provide an estimated execution time based on the at least one second DBMS'"'"'s execution plan.

5. The method of claim 1 wherein operation (2) comprises performing a test on a size or sizes of at least one first database'"'"'s table or tables containing data referenced by the query.

6. The method of claim 1 wherein each second database is a columnar database.

7. The method of claim 1 wherein each first database is managed by a respective first DBMS on a first computer system, and each second database is managed by a respective second DBMS on a second computer system.

8. The method of claim 1 wherein:

each first database is managed by a respective first DBMS, and each second database is managed by a respective second DBMS; and

each the second DBMS queries and obtains data from one or more of the first DBMS'"'"'s, but each first DBMS is unable to query any second DBMS.

9. The method of claim 1 wherein operation (2) comprises performing one or more tests on the query, the one or more tests comprising a test for checking if the query is to compute an aggregation and for determining that the query is to be executed on one or more of the second databases if the query is to compute an aggregation.

10. A computer system for performing the method of claim 1.

11. A computer readable medium comprising a computer program operable to cause a computer system to perform the method of claim 1.

12. A network transmission method comprising transmitting, over a network, a computer program operable to program a computer system to perform the method of claim 1.

14. The database management system of claim 12 wherein each second DBMS is a columnar DBMS.

15. The database management system of claim 14 wherein the query router comprises circuitry and/or software for performing one or more tests on the query, the one or more tests comprising a test for checking if the query is to compute an aggregation and to indicate that the query is to be routed to one or more of the second DBMS'"'"'s if the query is to compute an aggregation.

13. A database management system comprising:

one or more first DBMS'"'"'s; and

one or more second DBMS'"'"'s for storing data stored by the first DBMS'"'"'s, with at least one attribute of the data stored in columnar form on at least one second DBMS but not on any first DBMS;

wherein the database management system comprises a query router for routing a query to one of the first and second DBMS'"'"'s.

16. The database management system of claim 13 wherein each said first and second DBMS comprises an execution planner for providing an estimated execution time for executing a query.

17. A database management system for accessing one or more first DBMS'"'"'s, the database management system comprising one or more second DBMS'"'"'s for reading data from one or more of the first DBMS'"'"'s which store at least one data attribute in a non-columnar form, and for storing the data attribute in a columnar form in one or more databases managed by the one or more second DBMS'"'"'s;

wherein the database management system comprises circuitry and/or software for determining if a query is to be executed on the one or more of the first DBMS'"'"'s or one or more of the second DBMS'"'"'s, and for either executing the query on one or more of the second DBMS'"'"'s or providing the query to one or more of the first DBMS'"'"'s as determined in the determining operation.

18. The database management system of claim 17 wherein each second DBMS is a columnar DBMS.

19. The database management system of claim 17 wherein the determining operation comprises performing one or more tests on the query, the one or more tests comprising a test for checking if the query is to compute an aggregation and for determining that the query is to be executed on the one or more second DBMS'"'"'s if the query is to compute an aggregation.

20. A computer readable medium comprising a computer program for implementing the database management system of claim 17 when executed by a computer system.

21. A network transmission method comprising transmitting, over a network, a computer program operable to program a computer system to implement the database management system of claim 17.