JP2001155028A - Aggregate calculation processing method and device for relational database, and computer readable recording medium recorded with aggregate arithmetic processing program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To solve problems such as the deterioration of response performance or complexity of retrieval caused by input/output and calculation overhead since aggregate calculation operation is executed or stored in different tables each time of retrieval execution in conventional retrieval processing including aggregate calculation in a retrieval processing including aggregate calculation operation in a relational database system. SOLUTION: The result of arbitrary aggregate calculation is held in an index and the aggregate calculation operation result stored in the index is utilized for retrieving processing including aggregate calculation. Thus, it is not necessary to perform aggregate calculation in the case of retrieval execution.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an aggregation operation processing method for a relational database, and more particularly to a database processing method suitable for search processing including an aggregation operation.

[0002]

2. Description of the Related Art Conventionally, in a relational database system, there is a search process including an aggregation operation for counting specified search items. In such a process, among the search conditions specified by the user, an index is searched for the conditions for the items that make up the index, and the database storage area containing the target data is accessed from the index key entry that matches the search conditions and aggregated. , The information of the necessary items is obtained, and an aggregation operation is executed. Then, every time a search process including an aggregation operation is performed, a database access or an aggregation operation is performed. As a method of not performing the aggregation operation every time the search processing is executed, a method of acquiring the aggregation operation result in advance and holding the operation result as another table as described in Japanese Patent Application Laid-Open No. 8-329101 is known. is there.

[0003]

In a search process including an aggregation operation in a conventional relational database, input / output overhead due to database access and operation overhead relating to the aggregation operation occur when executing the aggregation operation. There has been a problem that the response performance of the search processing deteriorates due to the frequent occurrence of the output operation and the aggregation operation of a large amount of data.

In the method described in Japanese Unexamined Patent Publication No. 8-329101, in which an aggregation operation result is acquired in advance and held as a separate database, it is necessary to switch which database is searched during search processing. However, there is a problem that a search method becomes complicated.

SUMMARY OF THE INVENTION It is an object of the present invention to suppress input / output and operation overhead related to an aggregation operation, and to improve the response performance of search processing including the aggregation operation.

[0006]

In order to solve the above-mentioned problems, the present invention holds the result of an arbitrary aggregation operation in an index and uses the result of the aggregation operation stored in the index for search processing including the aggregation operation. To use.

[0007]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS An embodiment of the present invention will be described below. Indexes are provided to speed up access to a relational database (RDB). It has a correspondence table of "key values" and "address information" indicating storage positions of records having the key values. This is made for particularly frequently used key items. The index is referred to when searching the RDB. Therefore, if the result of the aggregation operation is also stored therein, if it is used again as it is, or if it is processed and edited and used, the aggregation operation is performed by searching the database at the time of executing the search. This eliminates the necessity, and can suppress the input / output overhead and the operation overhead of the aggregation operation.

[0008] The aggregate index in the embodiment of the present invention (here, the index in which the result of the aggregate operation is stored is referred to as an aggregate index) is described as an index storage method.
An index key storage area for storing one or more key items in an index storage area, an aggregation operation result storage area for storing an operation result of an arbitrary aggregation operation specified by a user, and a storage location of data in a corresponding database And a control information storage area for storing pointers to previous and next index entries, and these storage areas are grouped to form one index entry.

[0009] The relational database management system (RDBMS) may specify a key item to be stored in the index key storage area and an aggregation operation to be stored in the aggregation operation storage area as a means for defining an index according to the storage method. Index definition means to perform indexing.

Further, the RDBMS determines whether or not the index can be used and generates an access procedure using the aggregated operation result in the aggregated operation result storage area if the index can be used, as a database search method for search processing including an aggregate operation. Means and a database search means for accessing the storage device in accordance with the access procedure and acquiring the aggregation operation result from the aggregation operation result storage area.

Further, in the RDBMS according to the present invention, as a method for updating the database defining the index, the RDBMS always maintains the aggregation operation results held in the index in a state consistent with the main body of the database. Creating an internal processing procedure for updating the index key storage area, the aggregation operation result storage area, and the control information storage area of the index, that is, means for generating an access procedure, accessing a storage device according to the access procedure, and A database updating means for updating the contents of each area of the index is provided.

An embodiment of the present invention will be described below in detail with reference to the drawings. FIG. 1 is a configuration example of a database system according to the present invention. Reference numeral 1 denotes a processing device, which is a database management system 11, a user program 12 for defining an index, and a business application program 1.
There are three. An external storage device 2 stores a database and the like, and stores a database 21. Although the sales DB is mentioned as an example, for example, a human resources DB may be used, and the type of data does not matter. Reference numeral 3 denotes an external storage device that stores indexes and the like, and stores an index 31 corresponding to the database 21. Reference numeral 311 denotes one index entry of the index 31, which includes an index key storage area, an aggregation operation result storage area, and a control information storage area. Index 3 stored in external storage device 3
1 may be stored in the external storage device 2.

FIG. 2 shows an example of the configuration of internal components of the database management system 11. Details of each component will be described later. The database management system 11 includes a definition unit 1 for defining an index according to the present invention.
11, a means 112 for generating an access procedure in a database search process and an update process, a database search means 113 for accessing an index or a database stored in an external storage device in accordance with the access procedure for the search process, and an access for the update process. A database updating unit 114 for updating a database and an index according to a procedure is provided.

FIG. 3 shows a configuration example of an index (also referred to as an index file) according to the present invention. In general, the index has a balanced tree structure, starting from the root page that is the root, passing through multiple intermediate pages, and arriving at the leaf page that holds the index key value and the address of the data with that key value, etc. (Figure 3
(A)). An intermediate page called a root page or a node is for searching for a leaf page.

In the leaf page, one index entry is formed for each index key value, and the index entries are interconnected by a pointer attached to the index entry in order to sequentially follow the page having the index entry.

FIG. 3B shows a configuration of an index entry according to an embodiment of the present invention, in which a plurality of index key items 312 and a plurality of aggregation operation results 3 specified by the user are set.
13, a row identifier 314 indicating the data storage position of the database corresponding to the key value, and a pointer 315 indicating previous and next index entries.

FIG. 4 is a flowchart showing one embodiment of a method of creating an index by the index definition means 111. First, the value of the key item is acquired by accessing the database (step 41) (for example, in the example shown in FIG. 8, the contents of the branch and the product classification, that is, Tokyo, Osaka, etc.). It is determined whether the key value is a newly appearing value or a value that has already appeared (step 42). If the key value is a newly appearing value, it is stored in an index key value storage area (step 4).
3) The initial value corresponding to the aggregation operation is stored in the aggregation operation result storage area (step 44). At this time, if the target of the aggregation operation is other than the key item, the database is accessed to acquire the value of the item to be calculated. Next, the address of the previous index entry is stored in the control information storage area,
The address of the index entry is stored in the control information storage area of the previous index entry (step 4).
5). If the value of the key item has already been found, an index entry having the same value is searched (step 48), and the value of the aggregation operation storage area is updated (step 49). Value).
At this time, if the aggregation target is other than the key item, the database is accessed to acquire the value of the calculation target item. Finally, a row identifier indicating the data storage position of the database is stored in the control information storage area (step 46). The above processing is repeated for all data in the database (step 47). An aggregate index is created by the above processing.

FIG. 5 is a flowchart showing one embodiment of the search access procedure generating means. Generally, one or several indexes are assigned to one table of the database. FIG. 5 is a flowchart for selecting an appropriate one and instructing a reference when an appropriate one can be selected. The following processing is performed on all indexes of the table to be searched. First, it is determined whether or not there is a designation of grouping in the SQL statement defined by the user (step 51). If there is a designation of grouping (for example, if the key items are branch and product classification), Exact match (e.g., branch and product classification), partial match (e.g., branch or product classification), mismatch (e.g., even in branch) It is evaluated separately (not the product category) (exact match is the most efficient and highly evaluated). In the case of partial matching, the number of matching items is further subdivided and evaluated, and the priority order of the index is provisionally determined (step 52). If no grouping is specified, the search condition is evaluated without tentatively determining the index priority.

Next, it is determined whether or not a search condition, a sort item, and an aggregation operation are specified in the SQL statement (step 53). If specified, the item in the search condition and the definition in the database are defined. The degree of matching with the key item and sort item of each index is evaluated in the same way as for grouping items, and the condition types of search conditions such as = conditions and range conditions are evaluated, and the index temporarily determined earlier (Step 54), and the index with the highest priority is selected (step 55).

Next, it is determined whether or not the selected index is an aggregated index (step 56). If the selected index is an aggregated index, an instruction to refer to the aggregated operation result storage area of the index is issued as a method of acquiring the aggregated operation (step 5).
7). If the selected index is not an aggregation index, a search for an aggregation target item is instructed as an acquisition method of an aggregation operation, and an execution of an aggregation operation is instructed (step 58). If no search condition, sort item, or aggregation operation is specified, an instruction is given to directly access the database without using the index (step 59).

FIG. 6 is a flowchart showing one embodiment of a database search method for search processing including an aggregation operation. In the present embodiment, it is assumed that a BETWEEN predicate and a comparison predicate for an aggregation operation are specified for a key item of an index as a search condition. At first,
Among the search conditions specified in the SQL statement, the reference range of the index is limited by the condition for the key item of the index. That is, by tracing the hierarchical structure of the index, an index entry having an index key value that starts the reference range and an index entry that ends the reference range are obtained, and the index entry that starts the reference is positioned as a processing target (step 61). Next, the result of the aggregation operation that matches the aggregation operation specified in the search condition is acquired from the aggregation operation result storage area of the start index entry (step 62), and it is determined whether the value satisfies the search condition (step 63). If the condition is satisfied, return the search result according to the search item. At this time, it is determined whether or not all the search items are included in the index entry (step 6).
4) If all search items are included in the index entry, the value stored in the index entry is returned as a search result (step 65). If there is a search item that is not included in the index entry, the search item is obtained by referring to the target database from the row identifier of the control information storage area, and returned as a search result (step 6).
8). After returning the search result or when the search condition is not satisfied, follow the pointer of the next index entry stored in the control information storage area of the index entry,
Move to the next index entry (step 66).
The above processing is repeated up to the end index entry of the reference range (step 67).

FIG. 7 is a flowchart showing one embodiment of the index update determining method in the update access procedure generating means. First, all indexes defined in the update target database are extracted (step 7).
1). Next, for one index, it is determined whether or not the item to be updated includes an index key item (step 72). If so, an instruction to update the index key item is issued (step 75). Recalculate the aggregation operation and instruct to update the result (step 7)
6). If the item to be updated does not include the index key item, it is checked whether the item to be updated matches the item to be subjected to the aggregation operation of the index (step 73). If the item to be updated is determined, the aggregation operation of the index is recalculated and the result is calculated. Update is instructed (step 76). The above processing is repeated for all the indexes defined in the database (step 74).

FIG. 8 is a diagram showing a data storage state of the database and the aggregate index. The sales database is composed of items such as a branch, a product classification, a product number, and a sales amount. As an example, it is assumed that each item stores a value shown in FIG. FIG. 8B shows an aggregate index for the sales database, in which a branch and a product classification are designated as key items, and an aggregate operation COUNT (*) and an aggregate operation SUM (sales amount) are designated as aggregate operations. The state is shown.

Here, the branch and the merchandise classification are the key items grouped as described above. Also, the SUM of the set operation result
Is recalculated and updated when the sales amount in the data of the database is updated as described in FIG. The control information <row identifier> serves as a pointer to the database. This aggregate index is effective for routine tasks such as daily calculation and monthly calculation.

The presence or absence of an index is defined in a separately provided dictionary. FIG. 10A shows an example of an SQL statement that defines this index.

FIG. 9 is a flowchart showing the flow of processing in a business application example. First, it is determined whether the process is a search process or an update process (step 81). The search process of the business application program according to the present embodiment is a process of grouping by branch and product classification, and totaling the number of sales and the total sales amount for each group. The SQL shown in FIG.
Issue When the SQL is input, the access procedure generation means 112 generates an access procedure using an appropriate index by the method shown in FIG. 5 (step 8).
3) The database search means 113 obtains a search result according to the access procedure (step 8).
4). On the other hand, in the update process, the SQL shown in FIG.
Issue When the update SQL is input, the access procedure generating means 112 generates an access procedure for appropriately updating the index according to the item to be updated by the method of FIG. 7 (step 86), and the database updating means shown in FIG. According to 114, the database and the index are updated (step 87).

In the search SQL of FIG. 10B used in this example, the grouping item of the SQL matches the index key item, and the aggregation operation specified in the selection item matches the aggregation operation defined in the index. FIG. 8 (b)
By accessing only the index shown in (1) and referring to the aggregation operation result, an SQL search result can be obtained. That is, access to the database and aggregation processing are not required.

When the SQL of FIG. 10B including the aggregation operation is input, the aggregation operation COUNT (*) is used in the prior art.
Can be operated only from the index, but the aggregate operation S
The UM (sales amount) cannot be calculated unless the original sales database is accessed, resulting in input / output overhead. In addition, both aggregation operations have to be performed at the time of execution of the search processing, and there has been an operation overhead. On the other hand, when the aggregate index according to the present invention is used, since both aggregate operations hold the operation result in the index, it is possible to obtain the search result by accessing only the index and without performing the operation process. Thus, the input / output and operation overhead can be suppressed, and the response performance can be improved.

In the method of the prior art, the SQL shown in FIG.
The execution time of the statement is the sum of the input time of the index and the database and the processing time of the aggregation operation. On the other hand, the execution time when the present invention is applied is only the index input time. Aggregated indexes have a larger capacity than conventional indexes, so when compared by index input time alone, they are longer than conventional index input times, but are sufficiently smaller than database input times, thus reducing SQL statement execution time. You can do it.

As another example of a search pattern, even when an aggregation operation is specified as a search condition, as in SQL shown in FIG. 10C, the condition evaluation is performed with reference to the aggregation operation result held in the index. By doing so, a search result can be obtained. In this case, neither input / output to the database nor arithmetic processing at the time of executing the search is necessary. As another search pattern example, when the number of grouping items is smaller than the number of index key items, as in SQL shown in FIG. 10D, the aggregation operation result held in the index cannot be used as it is. However, the result can be obtained by performing the aggregation operation based on the aggregation operation result held in the index, and the operation overhead related to the aggregation operation can be reduced as compared with the related art.

[0031]

According to the present invention, in a search process including an aggregation operation of a relational database, an input / output overhead and an operation overhead for performing the aggregation operation can be suppressed, and the response performance is improved.

[Brief description of the drawings]

FIG. 1 is an explanatory diagram showing one embodiment of the present invention.

FIG. 2 is an explanatory diagram showing a configuration of a database management system.

FIG. 3 is an explanatory diagram showing a configuration of an index according to the present invention.

FIG. 4 is a flowchart illustrating an index creation method according to the present invention.

FIG. 5 is a flowchart illustrating an access procedure generation method in a search process.

FIG. 6 is a flowchart illustrating a database search method.

FIG. 7 is a flowchart illustrating an access procedure generation method in an update process.

FIG. 8 is an explanatory diagram showing a configuration of a sales database and an index used in a business application example.

FIG. 9 is a flowchart illustrating processing of a business application program used in a business application example.

FIG. 10 is an example of an SQL statement used in a business application example.

[Explanation of symbols]

 1: Processing device 2: External storage device for storing a database 3: External storage device for storing an index 11: Database management system 12: User program for index definition 13: Business application program 21: Database 31: Index for the database 21 31: Index entry storage area

Claims (10)

[Claims] In a relational database, an aggregation operation result designated and created by a user is stored in an index, and the operation result stored in the index is referred to in a search process including an aggregation operation. Aggregation operation processing method. 2. In a relational database, when an index entry having a key item, an aggregation operation result, and a row identifier in the database corresponding to the key item is formed in an index, and a search including an aggregation operation is performed, An aggregation operation processing method, wherein the aggregation operation result is used when the aggregation operation result is available. 3. In a relational database, 1
Alternatively, store the aggregation operation results with key items as grouping items in multiple indexes, select an index that has a high degree of matching between the key items and the grouping items specified in the search, and select the selected index as the aggregation operation result In the case of an index including (1), an instruction is issued to refer to the result of the aggregation operation of the index. 4. In a relational database, 1
Alternatively, store the aggregation operation results in multiple indexes, select the index with the key item that has a high degree of matching with the search condition and the key item that further narrows the search results at the head, and aggregate the selected indexes In the case of an index including an operation result, an aggregation operation processing method is provided, wherein an instruction is issued to refer to the aggregation operation result of the index. 5. In a relational database, an index entry having a key item, an aggregation operation result, a row identifier in the database corresponding to the key item, and pointers indicating previous and next index entries is formed in the index, and The entry is traced by the pointer, and when the aggregation operation result satisfies the search condition, the value stored in the index entry is returned according to the search item and moved to the next index entry, and the aggregation operation result satisfies the search condition. An aggregation processing method characterized by moving to the next index entry when there is no such entry. 6. In a relational database, an aggregation operation result specified and created by a user is stored in an index, and when a database is updated, all indexes related to the database are extracted, and the items to be updated are aggregated indexes. An aggregation operation processing method, characterized in that, when a match is found with an operation target item, recalculation of an index aggregation operation and instructing an update to the result are repeatedly performed for all the indexes. 7. A relational database, comprising: an index storing an aggregate operation result designated and created by a user; and means for referring to the operation result stored in the index in a search process including an aggregate operation. Aggregate arithmetic processing device. 8. In a relational database, 1
Alternatively, the aggregation operation result is stored in multiple indexes, and the key item selects the index that is most suitable for the search.If the selected index is an index that includes the aggregation operation result, the aggregation operation result of the index is Means for instructing to refer to. 9. An index key storage area for storing one or more key items, an aggregation operation result storage area for storing an operation result of an aggregation operation specified by a user, and a pointer indicating a storage position of data in a database. An index in a relational database including a control information storage area for storing the information. 10. A program in a relational database, wherein an aggregation operation result designated and created by a user is stored in an index, and the operation result stored in the index is referred to in a search process including the aggregation operation. A computer-readable recording medium having recorded thereon an aggregation processing program.