System and method for query processing using virtual table interface

• US 6,694,306 B1
• Filed: 09/19/2000
• Issued: 02/17/2004
• Est. Priority Date: 10/06/1999
• Status: Expired due to Fees

• Current Assignee: Hitachi, Ltd.
• Original Assignee: Hitachi, Ltd.
• Inventors: Inohara, Shigekazu, Shimizu, Akira, Sagawa, Nobutoshi, Nishizawa, Itaru
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/665,069
• Time in Patent Office: 1,246 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5, 707/10, 707/100, 707/101, 707/102, 707/104.1
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A query processing system in which a virtual table to enable multiple mapping to a plurality of databases is provided in a data processing system to receive a query from an application program and partial replicas as copies of part of the data in the virtual table is prepared in storage so that the whole or part of a query can be processed using the partial replicas. For query units which cannot be processed using the partial replica, means to select an appropriate distributed join method and optimization to push down query processing to real databases as far as possible are used. The system allows transparent access to multiple different real databases according to access conditions without changing the column to be accessed in the virtual table or switching the virtual table, thereby eliminating the need to create a new application for each newly developed service.

Claims

1. A query processing system connected, through a network, with at least one client to run application programs and with a plurality of servers each having at least one database, which receives a query from said client and returns the result of the query;

said query processing system having;

at least one virtual table composed of a plurality of virtual columns which contain no real data and are mapped to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs and multiple mappings to different columns of different databases can be defined on each of the virtual columns;

a virtual table manager which manages said virtual table and provides each of virtual columns, on which multiple mappings are defined, with a switching designation to select one of defined mappings;

a query analyzer which analyzes said received query;

a query generator which generates a database reference query from the query analyzed by said query analyzer to reference said database using the definition of said virtual tables; and

a query executor which executes processing of said database reference query generated by said query generator.

2. The query processing system as defined in claim 1, wherein said switching designation to select one of defined mappings is according to specified condition that is data freshness, data precision, user role, application program or access location, or any combination of these.

3. The query processing system as defined in claim 1, wherein said query executor has:

syntax translation means to translate the query syntax before executing database reference queries;

data type and data value translation means to transform the data type and data value as query conditions and the data type and data value obtained from said databases as a result of said translated query; and

wherein said data type and data value translation means has means to store and transform data of data type proper to each database without deterioration in data precision by using a universal data type consisting of a set of data type ID and data itself.

4. The query processing system as defined in claim 1, wherein said query executor has:

syntax translation means to translate the query syntax before executing database reference queries;

data type and data value translation means to transform the data type and data value as query conditions and the data type and data value obtained from said databases as a result of said translated query; and

wherein said syntax translation means and data type and data value translation means each have interface means to enable dynamic insertion of modules necessary for translation as plug-ins.

5. The query processing system as defined in claim 1, wherein said virtual table manager has interface means to define said virtual table.

6. The query processing system as defined in claim 1, wherein said virtual table manager has interface means to switch multiple mapping to said virtual table.

7. A query processing system connected, through a network, with at least one client to run application programs and with a plurality of servers each having at least one database, which receives a query from said client and returns the result of the query;

said query processing system having;

virtual tables, each being composed of a plurality of virtual columns which contain no real data and are mapped respectively to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs multiple mappings to different columns of different databases or to the result of calculation for said columns can be defined on each of the virtual columns;

a virtual table manager which manages said virtual tables and provides each of virtual columns, on which multiple mappings are defined, with a switching designation to select one of defined mappings;

a partial replicas which hold columns defined in said virtual tables or the whole or part of calculation result for said columns as real data;

a partial replica manager which manages data in said partial replicas and performs data retrieval, updating, deletion or insertion;

a query analyzer which analyzes said query;

a query optimizer which judges whether it is possible to process the query analyzed by said query analyzer using said partial replicas;

a partial replica reference query generator which generates partial replica reference queries from said query to reference said partial replicas if judged possible;

a database reference query generator which generates database reference queries from said query to reference said database using the definition of said virtual table if judged not possible; and

a query executor which executes at least either of said generated partial replica reference queries or said generated database reference queries.

8. The query processing system as defined in claim 7, wherein said partial replica manager has interface means to define said partial replica.

9. The query processing system as defined in claim 7, wherein said query optimizer has:

means to transform said analyzed query to disjunctive normal form;

means to judge whether it is possible to process elements of said disjunctive normal form query using said partial replicas;

means to generate a set of partial replicas required for processing elements of said disjunctive normal form query if judged possible.

10. A query processing system connected, through a network, with at least one client to run application programs and with a plurality of servers each having at least one database, which receives a query from said client and returns the result of the query;

said query processing system having;

virtual tables, each being composed of a plurality of virtual columns which contain no real data and are mapped respectively to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs and multiple mappings to different columns of different databases or to the result of calculation for said columns can be defined on each of the virtual columns;

a virtual table manager which manages said virtual tables and provides each of virtual columns, on which multiple mappings are defined, with a switching designation to select one of defined mappings;

a query analyzer which analyzes said query;

a query generator which generates database reference queries from the query analyzed by said query analyzer to reference said databases using the definition of said virtual tables; and

a query executor which executes processing of said database reference queries generated by said query generator,

said query generator for generation of said database reference queries having;

means to transform a query to conjunctive normal form;

means to judge whether it is possible to process each of elements of said conjunctive normal form query using a single database;

means to rewrite said query so as to process it using said database if judged possible; and

means to rewrite said query so as to process it using said database and data processing system if judged not possible.

11. The query processing system as defined in claim 10, wherein said means to rewrite said query so as to process it using said databases and data processing system has:

means to check the applicability of each of the following processing methods (1) through (4) for the query if it requires join processing between different databases, that is

(1) a first processing method in which the respective join target data from the join target first and second databases is transferred to the data processing system and said data processing system performs join processing;

(2) a second processing method which executes join processing at the second database by using a query that incorporates the query result that is obtained from the first database;

(3) a third processing method in which query conditions are described as parameters in the query formula for the second database and the result obtained from the first database is given as parameter values to perform join processing;

(4) a fourth processing method in which the values obtained from the first database are inserted into a temporary table in the second database to perform join processing at the second database; and

means to select the least costly method from among the applicable processing methods, based on processing cost calculation.

12. The query processing system as defined in claim 11, wherein said means to calculate costs of processing has means to calculate the cost of processing by the sum of required CPU time for calculation and CPU time for I/O processing.

13. The query processing system as defined in claim 11, wherein said means to calculate costs of processing by data transfer volumes.

14. The query processing system as defined in claim 10, wherein said query generator has:

means to rewrite a query so as to process it using both databases and the data processing system;

means to list several candidate query execution methods;

means to check the applicability of each of the following processing methods (1) through (4) for a query which requires more than one join processing between different databases;

(1) a first processing method in which the respective join target data from the join target first and second databases is transferred to the data processing system and said data processing system performs join processing;

(2) a second processing method which executes join processing at the second database by using a query that incorporates the query result obtained from the first database;

(3) a third processing method in which query conditions are described as parameters in the query formula for the second database and the result obtained from the first database is given as parameter values to perform join processing;

(4) a fourth processing method in which the values obtained from the first database are inserted into a temporary table in the second database to perform join processing at the second database; and

means to calculate the cost of processing for each of the rest of candidate query execution methods after removing candidate query execution methods containing inapplicable formulas; and

means to select as the execution method the method with minimum processing cost from among the applicable processing methods.

15. A query processing method in which a query to be executed in a data processing system connected, through a network, with at least one client to run application programs and with a plurality of servers each having at least one database to reference said database according to access requests, is received from said client, and the result of execution of the query is returned to said client;

said query processing method having;

a process to analyze the received query;

a process to generate database reference queries from said analyzed query to reference said databases using the definition of a virtual table composed of a plurality of virtual columns which contain no real data and are mapped respectively to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs and multiple mappings to different columns of different databases or to the result of calculation for said columns can be defined on each of the virtual columns; and

a process to execute the generated queries.

16. A storage medium which records a computer program to execute one of the methods as defined in claim 15 using a computer.

17. A query processing method in which a query to be executed in a data processing system connected, through a network, with at least one client to run application programs, and with a plurality of servers each having at least one database to reference said database according to access requests, is received from said client, and the result of execution of the query is returned to said client;

said query processing method having;

a process to analyze the received query;

a process to generate database reference queries from the analyzed query to reference said databases using the definition of a virtual table composed of a plurality of virtual columns which contain no real data and are mapped respectively to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs and multiple mappings to different columns of different databases or to the result of calculation for said columns can be defined on each of the virtual columns;

a process to generate partial replica reference queries to reference partial replicas which hold columns defined in said virtual table or the whole or part of calculation result for said columns as real data;

a process to judge whether it is possible to process said analyzed query using said partial replicas;

a process to execute at least said partial replica reference queries if judged possible; and

a process to execute said database reference queries if judged not possible.

18. A query processing method in which a query to be executed in a data processing system connected, through a network, with at least one client to run an application and with a plurality of servers each having at least one database to reference said database according to access requests, is received from said client, and the result of execution of the query is returned to said client;

said query processing method having;

a process to generate database reference queries to reference said databases using the definition of a virtual table composed of a plurality of virtual columns which contain no real data and are mapped respectively to columns of real databases, wherein, individual names of the virtual columns are provided to said application programs and multiple mappings to different columns of different databases or to the result of calculation for said columns can be defined on each of the virtual columns; and

a process to execute the generated database reference query,

said process to generate a database reference query having;

a process to transform a generated query to conjunctive normal form;

a process to judge whether it is possible to process each of query elements of said conjunctive normal form query using a single database;

a process to rewrite said query so as to process it using said database if judged possible; and

a process to rewrite said query so as to process it using said database and data processing system if judged not possible.