KR20000038104A - Method for generating subquery for global query in multi-database system - Google Patents

Abstract

PURPOSE: A method for generating a subquery for a global query in a multi-database system, is provided to obtain data of desired through a query result transmitted from a subquery hereafter, by perfectly reflecting data demanded by the global query on every subquery without loss. CONSTITUTION: A method for generating a subquery for a global query in a multi-database system, connected through many local databases and networks, comprises the steps as follows. In a first step, table information in a from-passage from a parsing result of the global query is added to a global table list, projection information in a select-passage of the global query is added to a projection list, and then whether a where-passage exists in the global query is confirmed. In a second step, when a where-passage exists in the first step, table information having the where-passage is added to the global table list, and then join-field is added to a selection list. In a third step, when a where-passage doesn't exist in the first step, join field is added to the selection list. In a fourth step, whether an alias in the selection is coinciding with an alias in the global table is confirmed. In a fifth step, local data information corresponding to an alias and field information in the projection list is searched in mapping information only in case of coincidence in the fourth step, and the alias and field information in the projection list are added to a select and from-passage corresponding to the searched area database.

Description

The subquery generation method in a multidatabase system

The present invention relates to a method for generating a subquery from a global query in a multidatabase system. More particularly, the present invention faithfully reflects each subquery without losing data required by a global query in a multidatabase system. Preferably, the present invention relates to a method of generating a subquery from a global query that can obtain desired data through a query result transmitted from a subquery later.

Multidatabase systems are a type of distributed database system that provides a single view to a user by logically integrating several heterogeneous database systems that already exist. At this time, the database participating in the multidatabase is called a local database. The user retrieves the data stored in the multidatabase system through the global query, and the multidatabase system decomposes the submitted global query and delivers it to the local database with the actual data. The local database returns the execution result of the query to the multidatabase system, and the multidatabase integrates the query result and outputs it to the user. In the process of decomposing a global query into a subquery, the decomposed subquery may not represent the data required by the global query, that is, data loss may occur.

Therefore, in the present invention, in converting a standard global query into a subquery to be delivered to each local database in a multidatabase system, the present invention faithfully adheres to each subquery without losing data required by the global query. The purpose of the present invention is to provide a method for generating a subquery for a global query so that desired data can be obtained from a query result delivered in response to a subquery in the future.

1 shows a general structure of a multidatabase system.

2 is a flow chart for generating a subquery from a global query in accordance with the present invention.

The method according to the present invention for achieving the above object is a method for generating a subquery for a global query in a multi-database system connected through a plurality of local databases and a network, the global query from the result of parsing the global query from Adding table information in the clause to the global table list, adding projection information in the select clause of the global query to the projection list, and then checking whether the where clause exists in the global query; A second step of adding table information in the where clause to the global table list and then adding a join field to the selection list if the where clause exists in the first step; A third step of adding a join field to a selection list if the where clause does not exist in the first step; A fourth step of checking whether an alias in a projection list matches an alias of a table in a global table after performing the third step; And searching the mapping information for local database information corresponding to the alias information in the projection list and the field information in the projection list only in the case of matching in the fourth step, and selecting and from the subquery corresponding to the found local database. Clause includes a fifth step of adding aliases and field information in the projection list.

In addition, the recording medium according to the present invention is a computer-readable recording medium for performing a function of generating a subquery from a global query in a multi-database system connected through a plurality of local databases and a network. From the parsing result of the query, add the table information in the from clause of the global query to the global table list, add the projection information in the select clause of the global query to the projection list, and then check if the where clause exists in the global query. The first step to do; A second step of adding table information in the where clause to the global table list and then adding a join field to the selection list if the where clause exists in the first step; A third step of adding a join field to a selection list if the where clause does not exist in the first step; A fourth step of checking whether an alias in a projection list matches an alias of a table in a global table after performing the third step; And searching the mapping information for local database information corresponding to the alias information in the projection list and the field information in the projection list only in the case of matching in the fourth step, and selecting and from the subquery corresponding to the found local database. The program is recorded in the section for executing the fifth step of adding aliases and field information in the projection list.

Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings.

1 illustrates a general structure of a multidatabase system. A multidatabase system is connected to a plurality of local databases and a network to access data. In addition, the multi-database system is connected to the network and sends a subquery to a plurality of local databases and receives a query result therefor. Then, the query results input from each local database are integrated and provided to the user or administrator.

2 shows a flowchart for generating a subquery from a global query according to the present invention.

The method for generating a subquery from the global query according to the present invention adds the table information in the from clause of the global query to the global table list from the parsing result. The projection information in the select clause of the global query is added to the projection list (102). Then, if there is a where clause in the global query (103), the table information in the where clause is added to the global table list (104), and then the join field is added to the selection list (105). On the other hand, if the where clause does not exist (103), only the process of adding the join field to the selection list (105) is performed.

After constructing a global table through this process, the following process is performed on each table in the global table.

Check that the alias A in the projection list matches the alias of table T in the global table (106). As a result of the check, only the case where there is a match, the local database information corresponding to the alias A in the projection list and the field information F in the projection list is searched in the mapping information (107). Then, add aliases (A) and field information (F) in the projection list to the select and from clauses of the subquery corresponding to the found local database (108). On the other hand, if the alias A in the projection list does not match the alias of the table T in the global table, it is sent as a subquery. Through this process, subqueries are generated from global queries and sent to each local database.

Referring to the operation of the present invention as a specific example as follows.

When a global query is submitted, the submitted global query must be parsed first, and the parsed global query must be analyzed along with the mapping information to create a subquery to be delivered to the local database. Mapping information is information that indicates which field of a table corresponds to which field of a table in the local database.

It is clear that a global query must be broken up into several subqueries and sent to each local database. However, only one subquery in each local database can achieve the purpose of a global query.

Consider the following example.

Assume that DB1 of any two databases has any two tables as shown in Tables 1 and 2, and DB2 has any two tables as shown in Tables 3 and 4.

Empolyee table ID name Age One James 30 2 John 44

Professor Table ID name Age 3 Tom 35 4 Kim 55

Empolyee table ID name Age 11 Park 60 12 Bill 10

Professor Table ID name Age 3 Tom 35 14 Lilly 20

And suppose a global query is submitted as follows:

Select p.name, e.name

From Professor p, Empolyee e

Where p.age <e.age

Since the tables in both databases are identical, there is no special fragmentation, so if you send this statement equally to each DB, the result of (Tom, John) from database A will be (from database B) Tom, Park) and (Lilly, Park).

However, this resulted in unexpected data loss. That is, (Lilly, James), (Lilly, John), (Kim, Park) results are not obtained. However, since both databases are integrated, you should generate these results. However, looking at the results, it can be seen that the contents of tables located in different DBs were not collected in the case of a query that spans two tables.

Such data loss cannot be handled by local databases. Because you can't know what is stored in other DB. Therefore, in this case, it is best to collect the results from multiple databases and test the possible combinations.

After all, multi-databases must first create global tables that aggregate the tables from each local database. And we need to extract all possible results that satisfy the query from these global tables.

Tables 5 and 6 create a global table using the example table above.

Empolyee table ID name Age One James 30 2 John 44 11 Park 60 12 Bill 10

Professor Table ID name Age 3 Tom 35 4 Kim 55 14 Lilly 20

After creating a global table like this, all possible combinations are created and compared with the conditions in the Where clause of the global query. This comparison prevents the loss of data in the example above. The disadvantage of this approach is that the work load on the multi-database is higher than when all the work is done to the local database.

In conclusion, the process of partitioning a global query requires merging all the tables in the From clause from each local database. Extracting results from these tables should be done in the integration of the partial results described below.

To create an intermediate global table, as in this example, the global query must be partitioned as follows:

DB _A

-1.Select p.id, p.name, p.age From Professor p

-2.Select e.id, e.name, e.age From Employee e

DB _B

-1.Select p.id, p.name, p.age From Professor p

-2.Select e.id, e.name, e.age From Employee e

The subquery created above is sent to each DB. The first results of the second DB _A subquery is the result of two of the first subquery of the first to be integrated with the results of the first subquery, and the DB _A DB _B should be integrated with the second-vain of the DB _B. Finally, these integrated results should be compared on the condition of 'Where e.age>p.age'.

The above example can be extended to the more general case as follows.

Suppose there are m local databases from DB ₁ to DB _m . Also, suppose that the local table name in DB _k is A _k (T _i ) for the global table name T _i . Similarly, suppose the local field name in DB _k of the global field name f _j of a global table T _i is A _k (f _j ). The division rule described above can be written as follows.

Global Query

-Select t ₁ .f ₁ , t ₁ .f ₂ , ... t _n .f _k

From T ₁ t ₁ , T ₂ t ₂ , ... T _n t _n

Where ...

SubQueries

DB ₁

-Q ₁ : Select t ₁ '.A ₁ (f ₁ ), t ₁ ' .A ₁ (f ₂ ), ... From A ₁ (T ₁ ) t ₁ '

-Q ₂ : Select t ₂ '.A ₁ (f ₁ ), t ₂ ' .A ₁ (f ₂ ), ... From A ₁ (T ₂ ) t ₂ '

-Q ₃ : ...

DB ₂

-Q ₁ : Select t ₁ '.A ₂ (f ₁ ), t ₁ ' .A ₂ (f ₂ ), ... From A ₂ (T ₁ ) t ₁ '

-Q ₂ : Select t ₂ '.A ₂ (f ₁ ), t ₂ ' .A ₂ (f ₂ ), ... From A ₁ (T ₂ ) t ₂ '

-Q ₃ : ...

DB _m

-Q ₁ : Select t ₁ '.A _m (f ₁ ), t ₁ ' .A _m (f ₂ ), ... From A _m (T ₁ ) t ₁ '

-Q ₂ : Select t ₂ '.A _m (f ₁ ), t ₂ ' .A _m (f ₂ ), ... From A _m (T ₂ ) t ₂ '

-Q ₃ : ...

By the way, there are in the Where clause of a query in the original global .f t _{j l} into, if this is included in that afterwards the Select section in the global query, each DB _k Q _j of the Select section edaga t _j '.A _k (f _l ) Should be added.

In addition, when merging the results to create a global table, there must be a key field as a reference to indicate whether the values of each tuple match. For this purpose, the value corresponding to the key field must be included in the select clause of the subquery. In the end, the final subquery looks like this:

DB _k

Q _i = Select t _i '.A _k (f _s ) (t _i .f _s is in Select Clause of Global Query)

T _i '.A _k (f _r ) (t _i .f _r is in Where Clause of Global Query)

T _i '.A _k (f _y ) (t _i .f _y is key of Global Table T _i )

From A _k (T _i ) t _i '

How to create a global table is as follows.

T _i = ∪ Result Q _i of DB _k , where k = 1 ... m

The following shows the data to be prepared in advance for the algorithm described above. Suppose a global query is submitted as follows.

Select p.name From Person p, Child c Where c.father = p.name

① Mapping information coming in first is composed as follows. This mapping information is for implementing A _k (f _i ) described above. Since a global field of one global table may have a different local table name and a local field name in each local database, multiple tuples can be generated with the same global table and field. Examples of such mappings are shown in Table 7 below.

Global table name Global field names location Local table name Region field name Person name DB A PERSON NM person name DB B PEOPLE P_NAME ... ... ... ... ...

Also, the key fields needed for joining should be given as input. Key fields are given for each global table. There can be several key fields in a table and they are organized as shown in Table 8.

Global table name Key field Person name, age ... ...

Parse the global query entered as the final preparation.

The first step in the algorithm is to find out which global tables are being used. You can easily create a table like Table 9 from the From clause of a global table.

Global class Global table name Global Elias Person p ... ...

③ Create a global projection table.

First we get all the projections from the Select clause.

Next, we get all the necessary projections that appear in the where clause.

Add keyfields to the projection.

-The created projection list is shown in Table 10. Since it may be an operation that requires a projection, it is added to the table whether it is an operation and a list of arguments. The operation value is 1 for no operation, 2 for no argument and 3 for argument.

Global projection Alias Field name Operation Argument p name One _ c father One _ p age One _

According to the present invention as described above, when generating a global query submitted to a multi-database as a subquery for submitting to each local database, no loss of data occurs, the algorithm is simple and can be easily realized. There is.

Claims (2)

In the method of generating subqueries for global queries in multi-database systems connected via multiple local databases and networks, From the parsing result of the global query, add the table information in the from clause of the global query to the global table list, add the projection information in the select clause of the global query to the projection list, and then check if the where clause exists in the global query. Confirming the first step; A second step of adding table information in the where clause to the global table list and then adding a join field to the selection list if the where clause exists in the first step; A third step of adding a join field to a selection list if the where clause does not exist in the first step; A fourth step of checking whether an alias in a projection list matches an alias of a table in a global table after performing the third step; And Only in the case of matching in step 4, local database information corresponding to the alias information in the projection list and field information in the projection list is searched in the mapping information, and the select and from clauses of the subquery corresponding to the found local database are searched. Step 5 of adding aliases and field information in the projection list to the Subqueries generation method for a global query comprising a. A computer-readable recording medium for performing a function for generating a subquery from a global query in a multidatabase system connected through multiple local databases and networks. From the parsing result of the global query, add the table information in the from clause of the global query to the global table list, add the projection information in the select clause of the global query to the projection list, and then check if the where clause exists in the global query. Confirming the first step; A second step of adding table information in the where clause to the global table list and then adding a join field to the selection list if the where clause exists in the first step; A third step of adding a join field to a selection list if the where clause does not exist in the first step; A fourth step of checking whether an alias in a projection list matches an alias of a table in a global table after performing the third step; And Only in the case of matching in step 4, local database information corresponding to the alias information in the projection list and field information in the projection list is searched in the mapping information, and the select and from clauses of the subquery corresponding to the found local database are searched. Step 5 of adding aliases and field information in the projection list to the A computer-readable recording medium having recorded thereon a program for executing the program.