JP2000076288A - Database management system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To shorten processing time against an inquiry. SOLUTION: A database management system is provided with an inquiry reception means 10, an inquiry registration means 12, a plural inquiries optimizing means 14, a registration inquiry starting means 16, an inquiry optimizing means 18, an inquiry execution means 20 and a data accumulation means 22. The inquiry reception means receives a started inquiry. The inquiry registration means 12 registers the received inquiry. The plural inquiries optimizing means 14 optimizes the execution of plural registered inquires and registers an optimum execution procedure. The registration inquiry starting means 16 starts plural registered inquires in accordance with the registered execution procedure at designated time. The inquiry optimizing means 18 optimizes the processing system of the started inquiry. The inquiry execution means 20 retrieves and updates data stored in the data accumulation means in accordance with the optimized processing system and processes the started inquiry.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an inquiry processing method for a database management system.

[0002]

2. Description of the Related Art A database management system manages a database efficiently even when a plurality of users update and search the database at the same time, so that no inconsistency occurs. As shown in FIG. 6, a conventional database management system includes:
Inquiry receiving means 100, Inquiry registration means 10
2. It is composed of a registration query activation unit 104, a query optimization unit 106, a query execution unit 108, and a data storage unit 110. The inquiry receiving means 100 receives the activated inquiry (command, request, inquiry to the database). The inquiry registration unit 102 registers the received inquiry. The registration inquiry activation unit 104 activates the registered inquiry at a designated time according to the registered execution procedure. The inquiry optimizing means 106 individually optimizes the processing method of the activated inquiry. The inquiry execution unit 108 searches and updates data stored in the data storage unit 110 according to the optimized processing method, and processes the activated inquiry.

[0003]

However, in the above-described configuration, in order to optimize individual processes for a plurality of queries to be processed periodically, the same process is repeatedly performed for each query. This requires a long processing time.

Therefore, there has been a demand for a database management system that can shorten the processing time for an inquiry.

[0005]

Therefore, according to the database management system of the present invention, an inquiry receiving means for receiving an activated inquiry, an inquiry registering means for registering the received inquiry, and a plurality of registered inquiries are provided. Query optimization means for optimizing the execution of a query and registering an optimal execution procedure, and registration query activation means for activating a plurality of registered queries at a specified time in accordance with the registered execution procedure. Query optimizing means for optimizing the processing method of the query, and searching and updating the data stored in the data storage means according to the optimized processing method,
Query execution means for processing the activated query.

As described above, since a plurality of query optimizing means are provided to optimize the processing of a plurality of queries and the execution order of the plurality of queries periodically, the processing time for each query is long. However, the effect of reducing the total processing time of a plurality of inquiries can be obtained.

In a database management system, a relational (relational) data model is frequently used. In the relational data model, data is represented by a two-dimensional table including rows and columns. For example, rows correspond to records in a regular file, and columns correspond to items (fields). Searching and updating data are operations on the table.

In the database management system according to the present invention, preferably, when a plurality of queries relate to the same plurality of tables, the plurality of query optimizing means generates a temporary table combining the plurality of tables. It is good to configure.

With this configuration, the query execution time can be reduced by using the temporary table.

In the database management system according to the present invention, preferably, the multiple query optimizing means finds a query related to the same item from a plurality of queries, and generates a query grouped by the item. It is good to configure.

As described above, since queries grouped by common items are generated, it is possible to reduce the query execution time.

[0012]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS An embodiment of the present invention will be described below with reference to the drawings. It should be noted that the drawings are only schematically shown to the extent that the configuration, connection relation, and arrangement relation of the present invention can be understood. The conditions such as numerical values described below are merely examples. Therefore, the present invention is not limited to this embodiment.

The configuration of the database management system according to this embodiment will be described with reference to FIG. FIG. 1 is a block diagram showing the configuration of the database management system according to the embodiment.

As shown in FIG. 1, a database management system according to this embodiment includes an inquiry receiving unit 10, an inquiry registering unit 12, and a multiple inquiry optimizing unit 1.
4, a registration query activation unit 16, a query optimization unit 18, a query execution unit 20, and a data storage unit 22. Inquiry receiving means 10
Accepts the activated query. The inquiry registration unit 12 registers the accepted inquiry.
The multiple inquiry optimizing unit 14 optimizes the execution of the registered multiple inquiries, and registers an optimal execution procedure. The registration inquiry activation means 16 activates a plurality of registered inquiries at a designated time according to the registered execution procedure. The inquiry optimizing unit 18 optimizes the processing method of the activated inquiry. The query execution unit 20 searches and updates the data stored in the data storage unit 22 according to the optimized processing method, and processes the started query.

In addition, these inquiry receiving means 10, inquiry registration means 12, and multiple inquiry optimizing means 1
4. The registration query activation unit 16, the query optimization unit 18, the query execution unit 20, and the data storage unit 22 are connected in this order, and can exchange data.

The inquiry receiving means 10 receives an inquiry periodically started. For example, a user of the database inputs a required inquiry to the inquiry receiving means 10 from outside. The query you entered is
It is passed to the inquiry registration means 12 and stored or registered in the inquiry registration means 12.

The multiple query optimizing unit 14 generates an execution procedure and the like in order to optimize the execution of the multiple queries registered in the query registration unit 12. The generated execution procedure and the like are registered in the inquiry registration unit 12.

Upon receiving the optimization request, the multiple query optimizing unit 14 reads and analyzes the multiple queries registered in the query registering unit 12. For example,
The multiple query optimizing unit 14 searches for a query B that can use the intermediate result and the final result obtained for the query A, and stores the intermediate result and the final result generated for the query A in the query registration unit To accumulate.
The inquiry B is optimized by processing using the generated intermediate result and final result. Also, the inquiry B generates an inquiry execution procedure so that the inquiry B is executed after the inquiry A. These “optimized query A”, “optimized query B”, “interim result and final result”, and “execution procedure”
Is stored in the inquiry registration means 12 in the form of a source code.

As described above, the optimized and periodically executed inquiry and the execution procedure of the inquiry are respectively registered in the inquiry registration unit 12 as “optimized inquiry”.

The optimized inquiry registered in the inquiry registration unit 12 is activated by the registration inquiry activation unit 16 at a designated time in accordance with the registered execution procedure. The registration inquiry activation means 16
When the start time of the inquiry that is periodically executed comes, the optimized inquiry is started according to the registered inquiry execution procedure.

The activated query is passed to the query execution unit 20 after the query optimizing unit 18 optimizes the processing method of each query. The query optimizing unit 18 performs index selection, join processing, optimization of a search order, parallel processing, and the like in order to optimize each query.

The query executing means 20 executes the query sent from the query optimizing means 18 and searches and updates the data stored in the data storage means 22. The query execution unit 20 searches and updates data stored in the data storage unit 22 according to the optimized processing method, and processes the query.

Then, the result of the executed inquiry is returned from the database management system to the user.
The user obtains the inquiry result by using display means such as a display.

As described above, in this configuration example, since the plural query optimizing means 14 is provided, it is possible to efficiently execute processing for each query. Next, an example of an optimization processing algorithm performed by the multiple query optimizing unit 14 will be described with reference to FIGS. 2, 3, 4, and 5. FIG. FIG. 2 and FIG.
FIG. 9 is a diagram provided for explanation of optimization processing of FIG. FIG. 4 is a diagram provided for explanation of the second optimization process. FIG. 5 is a diagram provided for explanation of the third optimization process. In the following description, S which is frequently used as a query language of the database
A description will be given based on QL.

<First Processing Example> In the first processing example, when a plurality of queries relate to the same plurality of tables, the multiple query optimizing means 14 sets a temporary table obtained by combining the plurality of tables. Is performed. Hereinafter, description will be made with reference to Table 1, Table 2, Table 3 and Table 4. Table 1 shows items 1 and 5, Table 2 shows items 2 and 6, Table 3 shows item 3, Table 4 includes item 4 respectively.

(1) Query before optimization 1 SELECT Item 1, Item 2 From Table 1, Table 2, Table 3 Where Table 1, Item 1 = Table 2, Item 2 And Table 1, Item 5 = Table 3, Item 3. That is, in the inquiry 1, the items 1 and 2 which are the items included in the tables 1, 2 and 3 are searched.
At this time, item 1 in table 1 and item 2 in table 2 are in a correspondence relationship, and item 5 in table 1 and item 3 in table 3 are in a correspondence relationship (FIG. 2A).

(2) Query before optimization 2 SELECT Item 1, Item 4 From Table 1, Table 2, Table 4 Where Table 1, Item 1 = Table 2, Item 2 And Table 2, Item 6 = Table 4, Item 4 In other words, in the inquiry 2, the items 1 and 4 which are the items included in the tables 1, 2 and 4 are searched.
At this time, item 1 in Table 1 and item 2 in Table 2 are in a correspondence relationship, and item 6 in Table 2 and item 4 in Table 4 are in a correspondence relationship (FIG. 2B).

(3) Generate a temporary table, Table 5, by combining Table 1 and Table 2 (FIG. 3A). The inquiry for this generation is expressed as SELECT Item 1, Item 2, Item 5, Item 6 From Table 1, Table 2 Where Table 1, Item 1 = Table 2, Item 2. The result searched by this inquiry is stored in the inquiry registration means 12 as Table 5. Table 5
Is a table containing items 1, 5, 2 and 6 (FIG. 3)
(A)).

(4) Query 1 ′ after optimization (a modification of query 1 using Table 5) SELECT Item 1, Item 2 From Table 5, Table 3 Where Table 5, Item 5 = Table 3, Item 3 Inquiry 1 'retrieves items 1 and 2 which are the items included in Tables 5 and 3. At this time, item 5 in Table 5 and item 3 in Table 3 are in a correspondence relationship (FIG. 3B).

(5) Query 2 'after optimization (a modification of query 2 using Table 5) SELECT Item 3, Item 4 From Table 5, Table 4 Where Table 5, Item 6 = Table 4, Item 4 Inquiry 2 'retrieves items 3 and 4 which are the items included in Tables 5 and 4. At this time, item 6 in Table 5 and item 4 in Table 4 are in a corresponding relationship (FIG. 3C).

As described above, in the first processing example, Table 5 is generated by combining predetermined items as a temporary table, and an inquiry is performed on Table 5, so that the processing time for the inquiry can be reduced. Can be achieved.

<Second Processing Example> In a second processing example, the multiple query optimizing unit 14 finds a query related to the same item from a plurality of queries, and generates a query grouped by this item. Perform the following processing. Hereinafter, description will be made with reference to Tables 1, 2, 3, and 4,
Table 1 includes items 1, 2, 3, 4, 5, and others (not shown) (FIG. 4A), and Table 2 includes items 1, 2, and 3 (FIG. 4B). ), Table 3 includes items 4, 5 and 3 (FIG. 4 (C)), and Table 4 includes items 1, 2,
3, 4, and 5 are included (FIG. 4D).

(1) Query 1 before optimization 1 SELECT Item 1, Item 2, Item 3 From Table 1 Group By Item 3 That is, in Query 1, items 1, item 2, and item included in Table 1 Search for 3. At this time, item 1 and item 2 are grouped by item 3 (FIG. 4B). The result searched by this inquiry is stored in the inquiry registration means 12 as Table 2 (FIG. 4B).

(2) Inquiry 2 before optimization: SELECT Item 4, Item 5, Item 3 From Table 1 Group By Item 3 That is, in Query 2, items 4, item 5, and item included in Table 1 Search for 3. At this time, item 4 and item 5 are grouped by item 3 (FIG. 4C). The result searched by this inquiry is stored in the inquiry registration means 12 as Table 3 (FIG. 4C).

(3) Generate an inquiry for acquiring item 3 of both inquiries. This inquiry is expressed as SELECT item 1, item 2, item 4, item 5 From Table 1 Group By Item 3. The result searched by this inquiry is stored in the inquiry registration means 12 as Table 4 (see FIG. 4).
(D)).

(4) Query 1 'after optimization (a modification of query 1 using Table 2) SELECT Item 1, Item 2, Item 3 From Table 4 That is, in Query 1', items included in Table 4 Are searched for item 1, item 2 and item 3.

(5) Query 2 'after optimization (variation of query 2 using Table 2) SELECT Item 4, Item 5, Item 3 From Table 4 That is, in Query 2', the items included in Table 4 Are searched for item 4, item 5, and item 3.

As described above, in the second processing example, a table 4 in which a plurality of tables are joined by common items is generated, and an inquiry is performed on the table 4, thereby shortening the processing time for the inquiry. Can be achieved.

<Third Processing Example> In a third processing example, the multiple query optimizing unit 14 finds a query related to the same item from a plurality of queries and generates a query grouped by this item. Perform the following processing. In this example, UNION of multiple grouping items
(Union) is grouped by items. Less than,
The description will be made with reference to Tables 1, 2, 3, and 4. Table 1 includes items 1, 2, 3, 4, 5, and others (not shown) (FIG. 5A), and Table 2 Contains items 1, 2, 3, 4, and 5
(FIG. 5 (B)), and Table 3 shows items 1, 2 and 3
(FIG. 5 (C)), and Table 4, items 4, 2 and 5
(FIG. 5D).

(1) Inquiry 1 before optimization 1 SELECT Item 1, Item 2, Item 3 From Table 1 Group By Item 2, Item 3 In other words, in Inquiry 1, items 1 and 2 are items included in Table 1. Search for item 2 and item 3. At that time, item 1 is grouped by item 2 and item 3.

(2) Inquiry 2 before optimization 2 SELECT Item 4, Item 2, Item 5 From Table 1 Group By Item 2, Item 5 In other words, in Query 2, items 4, items included in Table 1 Search for item 2 and item 5. At that time, item 4 is grouped by item 2 and item 5.

(3) Generate an inquiry for acquiring items 2, 3, and 5 included in both inquiries.
This query is for SELECT item 1, item 2, item 3, item 4,
Item 5 From Table 1 Group By Item 2, Item 3, Item 5 are represented. The result searched by this inquiry is stored in the inquiry registration unit 12 as Table 2 (see FIG. 5).
(B)).

(4) Query 1 'after optimization (a modification of query 1 using Table 2) SELECT Item 1, Item 2, Item 3 From Table 2 Group By Item 2, Item 3, ie, Query 1' , Item 1, item 2 and item 3, which are items included in Table 2. The result searched by this inquiry is stored in the inquiry registration means 12 as Table 3 (FIG. 5C).

(5) Query 2 'after optimization (a modification of query 2 using Table 2) SELECT Item 4, Item 2, Item 5 From Table 2 Group By Item 2, Item 5 That is, query 2' , Item 4, item 2, and item 5, which are items included in Table 2. The result searched by this inquiry is stored in the inquiry registration means 12 as Table 4 (FIG. 5 (D)).

As described above, in the third processing example, a table 2 in which a plurality of tables are joined by common items is generated, and an inquiry is performed on the table 2, thereby shortening the processing time for the inquiry. Can be achieved.

[0046]

According to the database management system of the present invention, a plurality of query optimizing means are provided to optimize the processing of a plurality of queries periodically executed and the execution order of the plurality of queries. In some cases, the processing time increases, but the effect of reducing the total processing time of a plurality of inquiries is obtained.

[Brief description of the drawings]

FIG. 1 is a diagram showing a configuration of a database management system according to an embodiment.

FIG. 2 is a diagram provided for explanation of a first optimization process.

FIG. 3 is a diagram, following FIG. 2, for explaining a first optimization process;

FIG. 4 is a diagram provided for explanation of a second optimization process.

FIG. 5 is a diagram provided for explanation of a third optimization process.

FIG. 6 is a diagram showing a conventional database management system.

[Explanation of symbols]

 10: Inquiry receiving means 12: Inquiry registration means 14: Multiple inquiry optimizing means 16: Registration inquiry activating means 18: Inquiry optimizing means 20: Inquiry executing means 22: Data storage means 100: Inquiry receiving means 102: Inquiry registration means 104 : Registration query activation means 106: query optimization means 108: query execution means 110: data storage means

Claims (3)

[Claims] 1. An inquiry receiving means for receiving an activated inquiry; an inquiry registering means for registering the received inquiry; and a plurality of means for optimizing the execution of the plurality of registered inquiries and registering an optimal execution procedure. Query optimizing means, registration query activating means for activating the plurality of registered inquiries in accordance with the registered execution procedure at a specified time, and query optimizing for optimizing a processing method of the activated query. Means for searching and updating data stored in the data storage means according to the optimized processing method, and processing the activated query. system. 2. The database management system according to claim 1, wherein, when the plurality of queries are related to the same plurality of tables, the plurality of query optimizing means is a temporary table obtained by combining the plurality of tables. A database management system characterized by generating 3. The database management system according to claim 1, wherein the multiple query optimizing unit finds a query related to the same item from the plurality of queries and generates a query grouped by the item. A database management system characterized by: