JP2001216307A - Relational database management system and storage medium stored with same - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a relational database management system which enables multidimensional analysis type aggregate real-time retrieval, guarantees the consistency of detail data and aggregate retrieval results, and further reduces a necessary disk area and enables the on-line update of detail data in real time. SOLUTION: This relational database management system comprises a main database stored with classification items specifying classifications and information item sectioned by the classification items in prescribed format and a main managing means which manages the main database is equipped with an auxiliary database which stores classification axis data generated by sectioning the classification items by plural classification axes, integrates classification data of all classification items belonging to the classificaiton axes by them into one piece of data, and coding the data so that they are arranged without overlapping on the axes and information data corresponding to the classification axis data in prescribed format and an auxiliary managing means which matches the data in the auxiliary database with the data of the main database.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

The present invention relates to a relational database management system (hereinafter abbreviated as "RDBMS"), and more particularly to a multidimensional analysis-adaptive RDBMS suitable for online search.

[0002]

2. Description of the Related Art Relational databases (hereinafter referred to as "relational databases")
Abbreviated as "RDB". ) Is a table format database, which is widely used for sales management, parts management, and the like.
By the way, in the RDB, the number of classification items and the number of hierarchies that define the attribute of the data have recently increased, and in order to cope with this, a plurality of classification items belonging to a common classification are set as one classification axis. A multi-dimensional analysis type search process for performing a search process in a multi-dimensional space composed of the following has been performed.

[0003] In this multidimensional analysis type search, the classification axis often has a hierarchical structure, and in many cases, a search process is performed in which detailed data of information items is totaled at various levels in the hierarchical structure. The user frequently issues a query for aggregating detailed data at various levels using a common classification axis, and also issues a query for obtaining detailed data on which a certain aggregate value is based.

However, detailed data to be searched is stored in an RDBMS, and can be searched by using index technology conventionally provided in this system. It has been difficult to efficiently process complicated queries such as aggregation at various levels based on the classification axis within a practical time.

[0005] Therefore, conventionally, for such a multidimensional analysis type query, an original query that has a multidimensional array structure with secondary data aggregated at the minimum granularity level for all classification axes that can consider detailed data is used. A so-called MOLA in which a database based on secondary data different from the RDB is configured and an aggregation query is processed by the aggregation database having the multidimensional array structure.
P (Multi-dimensional On-line Analitical Processin
g), or prepare various aggregated data in advance in the original RDB at the aggregation level that is frequently queried, and use the prepared aggregated data for these aggregation queries. So-called ROLAP (Relational On-line Analytical Proc)
A method called essing) has been used.

[0006]

However, in the above-described MOLAP, a multidimensional analysis type query can be efficiently processed by having a unique data structure of secondary data aggregated at the lowest level of classification. But the original RD
A database independent of B must be constructed, and the original R
Real-time analysis on the DB could not be performed. Furthermore, since the original RDB is not stored in the MOLAP database, the query for the original RDB must be made to the RDBMS having the original RDB.
The consistency between the detailed data of the DB and the aggregated data was to be lost.

[0007] Further, the above-mentioned ROLAP can secure real-time properties. However, as the number of classification axes or their hierarchical levels increases, the number of pre-aggregated ones for all combinations of levels becomes extremely large. Not only does this require disk space, but also increases the number of aggregated data that must be updated simultaneously when the original RDB specification data is updated, increasing the update cost. It is not realistic to update at the same time as the update process. On the other hand, if pre-aggregation is provided only for limited combinations in order to avoid such a problem, there is a problem that it takes a long time to process a combination that is not pre-aggregated.

Therefore, an object of the present invention is to solve the above-mentioned problems of the prior art, to enable a multidimensional analysis type aggregate search having real-time properties, and to make the detailed data of the original RDB consistent with the aggregate search results. It is an object of the present invention to provide a relational database management system that guarantees, reduces the required disk area, and enables online updating of detailed data within a realistic time.

[0009]

The above objects are achieved by the present invention described below. That is, the present invention relates to the conventional RD
BMS, that is, a main database that stores a plurality of classification items specifying a classification and information items classified by the classification items in a predetermined format, a table definition of the format of the main database, data integrity management in a table format, and data search In this RDBMS, the classification items are divided into a plurality of classification axes, and classification data of all the classification items belonging to each of the classification axes is classified into each classification axis. An auxiliary database storing, in a predetermined format, classified axis data coded so as to be integrated into one data and arranged so as not to overlap on the axis, and information data corresponding to the classified axis data; An auxiliary DB management means for matching data in the database with data in the main database. It is over relational database management systems.

The storage medium storing the relational database management system according to the present invention is characterized in that the main management means, the auxiliary management means and a program for each means described below are stored. This is a well-known storage medium such as a magnetic disk and an optical disk, which is suitable for storage and distribution.

According to the present invention, as described above, the data of the auxiliary database storing the data of the information items based on the classification axis coded so as to correspond one-to-one with the data of the main database is stored in the auxiliary DB management means. Thus, the data of the main database is always made to match the data of the main database in accordance with the update, insertion, etc. of the main database, and an RDBMS suitable for online multidimensional analysis type search is realized.

Hereinafter, the details of the present invention will be described based on an embodiment in which the present invention is applied to nationwide sales management of clothing. It is clear from the gist that the present invention is not limited to such an embodiment.

[0013]

FIG. 1 is an explanatory diagram of a basic configuration of a system according to an embodiment. As shown in FIG. 1, the present system is based on a conventional RDBMS, and the database is a main DB management comprising an RDB management means of the RDBMS in response to a request from a business application 31 relating to daily routine business processing. An original table 11 of a main database composed of a relational database which is directly updated and searched by means 21;
1 and an auxiliary table 13 of an auxiliary database and construction, maintenance and search of the auxiliary table 13 in order to efficiently obtain a solution to a search request of the multidimensional analysis application 32 relating to various specific theme processes derived from 1 as described later. There are three databases of classification code conversion tables 12 required for execution.

The auxiliary database classification code conversion table 12 and the auxiliary table 13 are constructed and maintained from the main database 11 by the auxiliary DB management means 22 for managing them, and are auxiliary in response to a request from the multidimensional analysis application 32. DB management means 22 More specifically, the auxiliary table 13 through a multidimensional correspondence search means described later.
Is searched, and the result is returned.

As shown in FIG. 2, the auxiliary DB management means 22 of the present embodiment includes a multidimensional correspondence search means 23, which will be described sequentially below.
It comprises auxiliary DB maintenance means 24, classification code conversion means 25, auxiliary DB definition means 26, and auxiliary DB construction means 27.

First, the structure of the original table 11 of the main database, which is the basis of the present embodiment, will be described with reference to FIGS. This example is an example of clothing sales management. As shown in FIG. 3, as the classification items for classifying information data, there are three classifications of <locality>, <prefecture>, <municipalities> A total of seven items including <product classification>, <product name>, and <size> as product classification items, and one item of <date of sale> as the classification item indicating the sales date <Sales volume>, <
It is an RDB table having two items of <sales amount>. When storing a table having such a configuration on the auxiliary storage device, a physical storage location of each data is specified, and the data can be efficiently read from the auxiliary storage device to the main storage device. An identifier unique to the data (hereinafter, "I
D ". ) Is allocated. In FIG. 3, the ID for specifying the position of the data on the auxiliary storage device is <ROW.
ID>.

FIG. 4 shows a reference key and a reference key for each combination of several classification items having common properties for a data holding type original table in which each item is directly held as detailed data as shown in FIG. A reference table in which the unique IDs are allocated and a combination of those classification items are replaced with a reference key, so that the key retention type element excluding the redundancy from the data retention type original table in FIG. It is a table.
The definition of the original table as shown in FIG. 3 or FIG. 4 is performed by the main DB management unit 21 of FIG. 1, and addition, update, and retrieval of data from the business application 31 or the like to the original table are also performed by the main DB management unit of FIG. This is performed by the management unit 21. The definition, update, and search methods for these source tables by the main DB management means 21 are the same as those realized by the conventional RDBMS.

By the way, the main point of the present invention is that a plurality of classification items consisting of data which can be updated simultaneously with the original table as shown in FIG. 3 or FIG. Classification axis data is divided into classification axes, and classification data of all the classification items belonging to each classification axis is integrated into one data, and the classification axis data is coded so as to be arranged on the axis without overlapping. An auxiliary table storing information data corresponding to the classification axis data in a predetermined format is constructed, thereby efficiently giving a search request from the multidimensional analysis application 32 to the solution. The auxiliary table 12 and the auxiliary DB management means 22 for managing the same will be described.

As shown in FIG. 2, the auxiliary DB management means 22 includes a multidimensional correspondence search means 23 and an auxiliary DB maintenance means 2.
4. Classification code conversion means 25, auxiliary DB definition means 26,
It is composed of auxiliary DB construction means 27.

First, the auxiliary DB definition means 26 for defining basic items in the auxiliary database according to the present invention will be described. The auxiliary DB definition means 26 defines an item or an item group (hereinafter, referred to as “dimension”) which is a classification axis necessary for constructing the auxiliary DB as follows.

That is, the dimension serving as the classification axis is processed by dividing it into a time dimension representing the concept of time and other general dimensions. For general dimensions such as the classification axis 1 relating to the sales place or the classification axis 2 relating to the product in the examples of FIGS. 3 and 4, the data holding type is such that the items in the original table in FIG. In this case, for each dimension, generally, a plurality of items constituting the dimension and a hierarchical relationship (hereinafter, referred to as “level”) of the item on the hierarchical structure of the dimension are designated. Specifically, the sales area dimension is shown in FIG.
As shown in the figure, the locality is level 1, the prefecture is level 2,
Municipalities are designated as level 3.

In the case of a key holding type in which a reference table is prepared as shown in FIG. 4 and a reference key is used in the original table, as shown in FIG. In general, a plurality of items constituting the dimension are specified, the level of the item in the hierarchical structure of the dimension is specified, and a reference key is specified as a dimension ID.

The sales date of the classification axis 3 is a time dimension. In the time dimension, there is generally no table in which all the data constituting the dimension are listed. Specialized in that it has a conventional hierarchical structure that is calculated by calculation, the hierarchical structure is not always explicitly expressed by multiple items like general dimensions, and has an absolute order relationship In order to be able to handle such specialty, the time dimension is specified.

Although not included in the original table of the above-described example, the classification axis has a numerical dimension such as a dimension representing age as a dimension having an intermediate property between the general dimension and the time dimension. In the case of a numerical dimension, the hierarchical structure is not always conventional, but rather a grouping of breaks specified by the user, for example, teens in age, 20 in age.
Although the point that the hierarchical structure is defined by the classification method like the generation is close to the general dimension, internally, it can be handled more flexibly by applying the handling method to the time dimension. In the system of this example, processing is performed in the same manner as in the general dimension.

In this example, these designations are displayed on a display device by a definition table in which the classification items extracted from the original table are displayed by the auxiliary DB definition means 26, and the user explicitly specifies the definition items through input means such as a keyboard. However, it is also possible to use a classification axis automatic generation system as disclosed in JP-A-11-212988.

Next, a unique code integrated for each classification axis, that is, each data of the information item, which is used for the auxiliary table constructed in the present invention with the classification data of the classification item defined as described above, is overlapped. A description will be given of a classification code conversion unit 25 that converts a code (hereinafter, referred to as a “classification code”) arranged in a multidimensional space constituted by the classification axes so as not to match. 7 to 9 are explanatory diagrams of the procedure of the classification code conversion means. The correspondence between the obtained classification data of the classification items and the classification codes is held in the auxiliary database as the classification code conversion table 12 for each dimension.

The classification code conversion means 25 is adapted to perform processing by dividing into properties of dimensions, that is, general dimensions and time dimensions. First, processing of a general dimension such as a sales dimension of the present example will be described. In this process, first, as shown in FIG. 7, the original table 12 is scanned to extract all non-overlapping classification data from the classification data of the classification items constituting each designated dimension. In the figure, the extraction result of the sales place dimension is illustrated in the upper right table. Then, for those having a hierarchical structure such as the sales place dimension of this example, the classification code is automatically converted as follows by a coding method for hierarchical structure data as shown in the example of the sales place dimension of FIG. Allocate to. In addition,
Hereinafter, the data constituting the hierarchical structure of the dimension will be referred to as members. In the example of the tree-shaped hierarchical diagram of the sales place dimension on the left side of FIG. 8, the classification data enclosed by squares, starting from the highest nationwide and locating in Hokkaido / Tohoku, Hokkaido, Asahikawa, etc., are all members.

First, a sequential number is given to all the members belonging to the dimension in order from the upper level as follows. For members with the same parent at a higher level in the hierarchy,
Numbers are assigned in order. At this time, the number of digits of the number to be allocated is adjusted to a number sufficient to allocate different numbers to all members having the same parent in this manner. In this example, the lowest municipal level is 2 in decimal.
Digits and other higher levels use one decimal digit, and have Hokkaido as the same parent as illustrated in the figure.
Asahikawa, Abashiri,. . . 01, 02,. . . And numbers are assigned in order. In this example, there is only one nation at the highest level, and there is no classification function. No code is required because no code is required, and a number is given from a lower local level. With this method, the same number can be assigned to different parents, even at the same level, such as Hokkaido for Hokkaido and Tohoku and Tokyo for Kanto.

The numbers assigned in this way to the members of each level for each dimension are arranged such that the numbers of the upper levels are in the higher digits so as to include all the levels, thereby forming one integrated data. Is used as the classification code of the classification data. For example, the classification code in the case of Abashiri in a sales area dimension is as follows: 1 in Hokkaido / Tohoku, 1 in Hokkaido in middle, and 02 in Abashiri in the lower rank, arranged in the same order from the top to the bottom in the same order. Becomes The table on the right side of FIG. 8 is an example of a classification code conversion table showing the correspondence between the obtained classification data and the classification codes.

When coding according to the hierarchical structure, a dimension is designated by a reference key as an item of the reference table of FIG. 4 which is different from the data retention type original table of FIG. In the case of a key-retention type original table, the data of the reference key, specifically the data of the dimension ID item, are also extracted at the same time, but the conversion to the classification code is performed by using the dimensions other than the ID item related to the reference key of the reference table. This is performed in the same manner as in the case of the data holding type shown in FIG. In this way, the obtained classification codes, the original classification data corresponding to the classification codes, and the reference keys as necessary are collected and stored as a classification code conversion table. FIG. 9 is an example of a classification code conversion table including the ID data of the reference key.
In the example of the first line in FIG. 9, it can be seen that the classification code 1101 is assigned to a group of classification data of “Hokkaido / Tohoku”, “Hokkaido”, and “Asahikawa”, where the sales place ID is 001. . In the drawing, the classification code is stored in the item of the sales location CD.

In the classification code obtained by the classification code conversion means 25 of the present embodiment, for example, in the case of the Kanto region, the corresponding classification code is from 2000 to 2999. It is possible to mechanically obtain a range that includes all classification codes to be performed, and that the members represented by the classification codes included in this range are all members at lower levels represented by intermediate level members. Is guaranteed to be
For example, when the data of the sales area is Kanto, it is understood that the data of the sales area CD should be 2000 to 2999.

The time dimension is processed as follows. In other words, in the time dimension, the lowermost member can be arranged in order in this example, "day", so in this example, the date of the sale date is arranged as it is from the upper digit to form a classification code. As a result, the members of the intermediate layer in the hierarchical structure can be represented by a range expression using the classification code of the lowest layer member. In addition, in the time dimension,
A method of uniquely assigning a specific classification code to the order of the lowest layer member, a method of performing coding for maintaining a hierarchical structure similar to the general dimension described above, and the like can also be applied.

Next, the auxiliary DB construction means 27 for constructing the auxiliary table 12 from the original table 11 using the classification code table obtained as described above will be described. The following description is based on the example shown in FIG. The auxiliary DB construction means 27 sequentially processes the first row of the original table 11 as follows, processes all the rows, and constructs an auxiliary table.

That is, first, in the first row of the original table in FIG. 10, the classification code of the sales place dimension is obtained. Since the detailed data of this classification item is given by the sales place ID of the reference key, a classification code corresponding to the classification key is obtained using a classification code conversion table based on this reference key, and FIG.
1. The corresponding item column of the auxiliary table shown in FIG.
Write in column D. In this example, the detailed data of the sales place ID in the sales place dimension is "121", and the classification code corresponding thereto is "2201" from FIG. 9, which is written in the first row of the sales CD column of the auxiliary table in FIG. It is.

Next, a classification code for the next product dimension is obtained. Since the detailed data of the classification item of this product dimension is given by the item data itself, a corresponding classification code is obtained by using a classification code conversion table based on the item data constituting this dimension, and the same as that of the sales location CD. Write in the product CD column of the corresponding item column of No. 11. In this example, the detailed data of each classification item of this product dimension has a product classification of “sports clothing”, a product name of “AAAAA”, and a size of “L”.
"1" is written in the first row of the product CD column of the auxiliary table in FIG.

Next, the next sales day dimension classification code is obtained. As described above, the sales date dimension is the time dimension, and in this example, the detailed data is given by the date, and has an absolute order relationship. You are doing. However, in the present example, the year “99” is next “00” and the order is not maintained, so the year is set to “29” on the assumption that the data is aggregated from 1970 to 2069. Therefore, in this example, since the sales date, which is a classification item of the sales date dimension, is “99/10/10”, the classification code is “291010”, and this value is the corresponding item in the auxiliary table of FIG. Column is written in the sales date CD column.

Next, in the original table, an information item (hereinafter referred to as "major") indicating specific information of the classification classified by the dimension to be tabulated and analyzed in the multidimensional analysis search.
Specifically, the data of the sales amount and the sales amount in FIG. 10 are written as they are in the corresponding item column of the major item column of the auxiliary table, that is, the sales amount and the sales amount columns. Therefore, the sales quantity “10” and the sales amount “60,000” of the original table in FIG. 10 are written as they are in the sales quantity column and the sales amount column of the major item of the auxiliary table in FIG.

When the processing of the first row in FIG. 10 is completed as described above, the auxiliary DB construction means 27 proceeds to the processing of the second row, and sequentially performs classification code conversion on all rows of the original table in FIG. Post information items. As a result, in FIG. 11, an auxiliary table excluding the column of "ROW ID" is obtained. In the case of using the “auxiliary table as a secondary index” described later, the auxiliary DB constructing unit 27 writes the above-described major column and then uses the corresponding detailed data of “ROW ID” of the original table as shown in FIG. The information is written in the “ROW ID” column provided on the right side in the figure of the last column of the auxiliary table. As a result, in this case, an indexed auxiliary table shown in FIG. 11 is obtained.

The data of each row of the auxiliary table of FIG. 11, that is, each tuple, as shown in FIG.
It can be represented as a data object arranged without overlapping in a multidimensional space with the classification code of each dimension as an axis. Specifically, for example, the tuple in the first row of the auxiliary index in FIG.
D, located at the coordinates (2201, 1011, 291010) in the three-dimensional space centered on each classification code of the sales day CD, and can be represented as a point having data (10, 60000, 1).

The auxiliary DB constructing means 27 of this embodiment uses the auxiliary table as a data object in this multidimensional space.
Index technology (multidimensional index technology) that provides an efficient search means for a multidimensional space. Specifically, U is disclosed in International Publication WO9810357.
The data is stored in the auxiliary storage device using the B-tree. As a multi-dimensional index technology, in addition to the UB tree, there is a paper in the 1984 SAMSIGMOD paper collection.
A. GUTTMAN 「R-TREES: A DYN
AMIC INDEX Structure For
An R-tree described in “SPATIAL SEARCHING” is known, but these techniques have a feature that a data object included in a search area constituting a sub-space in a space can be quickly found. Have.

In the UB tree, the data object is arranged in the auxiliary storage device as it is in the tuple format shown in FIG. 11, and a numerical value called “address” corresponding to each dimensional coordinate value constituting the space on a one-to-one basis. An index is formed using the address. If the address “A” is larger than the address “B”, each value of the coordinate values (X1, X2,..., Xn) corresponding to A corresponds to B. It must have the property of being greater than or equal to each of the coordinate values (Y1, Y2, ..., Yn).
In this example, an address is obtained from the coordinate values (X1, X2,..., Xn) by the following method called bit interleaving. That is, X1 to Xn are all represented by binary numbers. First, the first bit of X1, the first bit of X2,. . . , Xn and fetch them in that order. Hereinafter, from there, the second bit of X1,. . . , Xn, the third bit of X1,. . . , Xn,. . . The value obtained by rearranging all the bits of all the values constituting the coordinates is used as the address. If the UB tree technology is used, the structure of the index formed using this address is the same as the conventional RDBM
Indexes such as B-trees and B ^* -trees, which have been widely used for S, can be made the same, and since these techniques can be applied, there is an advantage that affinity with RDBMS is high.

Next, an auxiliary DB search means 23 for performing a multidimensional analysis type search based on a request from a multidimensional analysis application using this auxiliary table will be described. Now,
In general, a multidimensional analysis type search is performed by limiting a member or a range of a member at a certain level in a hierarchical structure for each dimension, and performing required aggregation of data of information items of the member or members of the range, for example, aggregation. Is what you want. For example, a search of “What is the sales amount of sports clothing in Kanagawa Prefecture in October 1999?” Is that “prefecture level” of “sale area dimension” is “Kanto / Kanagawa prefecture” and “product dimension” is “ Under the limitation that the "product classification level" is "sportswear" and the "month level" of the "sale date dimension" is "October 1999", the sum of sales amounts among the data of the majors or information items belonging to this limitation is obtained. That is. At this time, for a dimension whose members are not particularly limited, it is assumed that a special member called “ALL level” which includes all members of the dimension is present at the highest level, so that the highest “ALL level” is obtained. Is limited by “ALL”, and can be processed in the same manner as the processing of each level described above.

The auxiliary DB search means 23 of this embodiment is configured as shown in FIG. 13 to perform such processing. That is, first, as shown in the figure, upon receiving, for example, a search totaling request from the application, in the step of converting the classification item in the figure, this request is made in the multidimensional space of the classification code suitable for the multidimensional search as follows. To a rectangular search area.

That is, as described in the classification code conversion means 25, the limitation of one member at a certain level in the hierarchical structure can be mechanically reduced to one range condition of the corresponding classification code. Using the classification code conversion means 25, the restriction conditions of all the members restricted in the search request of the application are sequentially converted into the range condition using the classification code with reference to the classification code conversion table. . It should be noted that such a member or the range of members is not limited to one for one dimension, but is often plural.

As shown in FIG. 14, the limiting condition of the search totaling request converted into the range condition of the classification code by the classification code conversion means 24 is the above-described multidimensional space constituted by the classification code axis of each dimension. (Three or more dimensions are rectangular as shown in the figure, but “rectangle” in the present specification includes this). As described above, there are many cases where the limited range of members is plural for each dimension. In this case, the limiting condition for each dimension includes an OR condition. By applying the reduction to the range condition of this classification code to all the members used for the limiting, each limiting condition connected by OR is individually set. It is possible to obtain a range condition linked by AND for all dimensions, that is, individual rectangular search areas, and a plurality of rectangular search areas according to individual limiting conditions.

Next, for this rectangular search area, the auxiliary DB search means 27 performs an auxiliary table search as follows. The auxiliary table search is performed by using the above-described multidimensional index formed by the UB tree from the auxiliary table. That is, the address of the minimum coordinate of the rectangular search area is obtained for each rectangular search area, and the index is searched based on the address to find the first tuple of the auxiliary table belonging to the rectangular search area. Hereinafter, the next tuple is also stored adjacently as long as the adjacent address is included in the rectangular search area, so that the next tuple can be easily obtained. The same processing is repeated for the address that intersects the search area. With this repetition, results can be obtained quickly.

Then, based on the tuples of the auxiliary table extracted and obtained as described above, aggregation processing required for the value of the major item in the auxiliary table is performed. In this example, aggregation processing is performed. Then, the obtained aggregation result is answered in the format of the request to the multidimensional analysis application that has issued the request, and the search is completed.

When an auxiliary table for a multidimensional analysis search is configured with an index added with a ROW ID as an auxiliary index, the indexed auxiliary table is used to search for a tuple of an original table that matches a given condition. It can also be used as a secondary index of the table (use as a secondary index of the auxiliary table). For example, when it is desired to obtain detailed data of an original table based on a specific aggregated value as an extension of the above-described multidimensional analysis search, as described above, the search condition is defined by the dimension code of the auxiliary table. By using this auxiliary index, the ROW ID of the target tuple of the original table can be efficiently obtained, and as a result, the target tuple can be efficiently extracted from the original table. You can get it.

For a search for a general original table as well as a search for obtaining detailed data as an extension of the multidimensional analysis search, for example, in the sales area dimension, “Region = Kanto AND
If constraints are applied in order from the higher-level items of the multiple classification items that make up that dimension, such as "Prefecture = Kanagawa Prefecture," search for equality conditions for the classification items that make up each dimension In a similar manner, the search condition is reduced to a rectangular search condition in a multidimensional space formed by the auxiliary table, and a ROW ID is obtained from the indexed auxiliary table.
The corresponding tuple of the original table can be obtained from the ROW ID. Furthermore, if the classification code based on the hierarchical structure also holds the order relation of the dimensional data, the inequality condition,
The range index can be extended to use as a secondary index. As described above, the search can be efficiently performed using the auxiliary table.

By the way, the auxiliary table needs to be updated in accordance with the update of the original table and the reference table accompanying the original table. Hereinafter, the auxiliary DB maintenance means 24 for performing this update in synchronization with the update of the original table will be described. I do. This embodiment can be performed at low cost because of its configuration.

First, the process of updating the classification code conversion table in the auxiliary database will be described based on the example of the original table in FIG. In this update processing, first, is the dimension data of the source table to be updated given as raw item data like the product dimension, or given as the index of the reference key based on the reference table like the sales place dimension? Judge.

When the item to be updated is an item in the original table, such as the product dimension, which is a classification item as it is, the dimension data is not updated or inserted explicitly, but is updated or inserted into the original table. It is necessary to update and insert the classification code conversion table implicitly with the processing.

Therefore, in this example, when the update is an update including the value of the classification item with respect to the existing data in the original table, and when inserting new tuple data into the original table, this update information is sent from the RDB management means. In response, the classification code conversion table is updated as follows. In other words, in the case of updating data, the value of the classification item of each dimension after updating, and in the case of inserting data, the value of the classification item of each dimension of the inserted data is registered in the existing classification code conversion table. It is checked whether or not there is a classification item of each dimension that has been registered. If the classification item is a registered classification item of each dimension, nothing is performed, the classification code conversion table is left as it is, and the process proceeds to update the auxiliary table. If not, a new classification code is assigned to the classification items of each dimension in the same manner as the classification code conversion means 25, and is added to the classification code conversion table, and then the next auxiliary table is updated.

On the other hand, if the dimension to be updated is defined in the reference table, such as the sales place dimension in FIG. 10, the data of the dimension that must be updated and inserted into the classification code conversion table Since the update occurs as an explicit update and insertion to the dimension data of the reference table, the classification code conversion table is updated and inserted in the same manner as the classification code conversion unit 25 upon receiving the update. In this case, it is not necessary to update and insert the classification code conversion table when updating and inserting the original table.

After the above-described classification code conversion table update processing, the auxiliary DB maintenance means 24 of this embodiment performs the auxiliary table update processing accompanying the update of the original table as follows. That is, when a new data tuple is inserted into the original table, the corresponding tuple is also inserted into the auxiliary table. The update of the auxiliary table at the time of insertion must be performed according to the method of updating the base multidimensional index. In this example, the address of the tuple to be inserted is obtained, and the insertion position is searched on the index of the UB tree. Insert a tuple at that position. As a result of inserting the tuple at the position, the index of the UB tree is updated if necessary.

When the data of the existing tuple in the original table is updated, the processing is performed in the following three cases. In other words, when the items that make up the dimension are updated, when the items that make up the dimension are not updated, but when the measure items are updated, they do not correspond to any of them. (In the case where an item that is neither an item nor a measure is in the original table and only an item that is neither a classification item nor a measure is updated).

When the items constituting the dimension are updated, in the case of an auxiliary table, tuples whose classification code and measure item values match those to be updated in the auxiliary table before update are added with auxiliary indexes. In this case, a tuple whose ROW ID value matches the value to be updated is searched for and deleted from the auxiliary index before update, and then a tuple made of data to be updated is inserted into the auxiliary table.

When the items constituting the dimension are not updated but the major items are updated, the corresponding tuple is searched for in the same manner as described above, and the value of the relevant major item in the auxiliary table is updated. ing.

If none of the above applies, nothing is performed on the auxiliary table. When existing data is deleted from the original table, a corresponding tuple is searched for and deleted from the auxiliary table in the same manner as described above.

The update processing of the auxiliary table accompanying the update of the original table or the reference table by the auxiliary DB maintenance means is the processing of updating the classification code conversion table corresponding to at most the number of dimensions to the update processing of one original table. Only one deletion and one insertion process for the auxiliary table is required, and the process of updating the auxiliary table or classification code conversion table associated with the insertion / deletion of the original table, insertion / update / deletion of the reference table, and so on is less. Update in a realistic time. That is,
It can also handle online processing.

Further, when an indexed auxiliary table is used as a secondary index, the conventional RD
B technology, which was built to speed up the search, often eliminates the need for multiple secondary indexes, resulting in the elimination of the time required for conventional secondary index updates. There is also an advantage in some cases that the processing is rather faster.

[0062]

As described above, according to the present invention,
Only the classification code conversion table for the data amount controlled by the number of members included in the dimension, and the auxiliary table whose data amount is much smaller due to the classification code by the code conversion in the dimension unit of the classification item with the same number as the original table. By using it, the required disk space is small, the consistency between the detailed data and the aggregated search results is always guaranteed, real-time multidimensional analysis-type aggregated search is possible, and the real time It is possible to realize an RDBMS that allows online updating of detailed data within a database.

As described above, the present invention provides an RDBMS that enables a multidimensional analysis type online search, and is widely applicable to various fields of industry.

[Brief description of the drawings]

FIG. 1 is an explanatory diagram of a configuration of an RDBMS according to an embodiment;

FIG. 2 is an explanatory diagram illustrating a configuration of an auxiliary DB management unit according to the embodiment;

FIG. 3 is an explanatory diagram of an example of an original table.

FIG. 4 is an explanatory diagram of an example of an original table using a reference key for a classification item;

FIG. 5 is an explanatory diagram of a definition of a sales place dimension.

FIG. 6 is an explanatory diagram of a definition of a sales place dimension of an original table having a reference table.

FIG. 7 is an explanatory diagram of extraction of data of a sales place dimension by the auxiliary code conversion means.

FIG. 8 is an explanatory diagram of conversion of sales place dimensional data into a classification code by an auxiliary code conversion unit.

FIG. 9 is an explanatory diagram of an example of a classification item conversion table having a reference key.

FIG. 10 is an explanatory diagram of an example of an original table having different types of classification dimension data;

FIG. 11 is an explanatory diagram of an example of an auxiliary table with an auxiliary index;

FIG. 12 is an explanatory diagram of a multidimensional space centered on a classification code for displaying data of an auxiliary table;

FIG. 13 is a flowchart of processing of an auxiliary DB search unit.

FIG. 14 is an explanatory diagram of a rectangular search area in a multidimensional space.

[Description of Signs] 11 Original table 12 Classification code conversion table 13 Auxiliary table 21 Main DB management means 22 Auxiliary DB management means 23 Auxiliary DB search means 24 Auxiliary DB maintenance means 25 Classification code conversion means 26 Auxiliary DB definition means 27 Auxiliary DB construction Means 31 Business application 32 Multidimensional analysis application

 ────────────────────────────────────────────────── ─── Continuing on the front page (72) Inventor Tsuyoshi Matsumoto 2-5, Nippon Odori, Naka-ku, Yokohama-shi, Kanagawa F-term in the Yokohama Research Center of Teijin Limited (Reference) 5B075 NK43 NK54 NR03 NR12 QT06 5B082 BA09 GA07

Claims (6)

[Claims] 1. A relational database management system comprising: a main database storing a plurality of classification items for specifying a classification; information items divided by the classification items in a predetermined format; and main management means for managing the main database. In the system, the classification items are divided into a plurality of classification axes, and the classification data of all the classification items belonging to each classification axis are integrated into one data, and are arranged so as not to overlap on the axes. Auxiliary database storing the encoded axis data and information data corresponding to the classified axis data in a predetermined format, and auxiliary management means for matching the data in the auxiliary database with the data in the main database. A relational database management system characterized by: 2. A method according to claim 1, wherein a plurality of classification items classified into one classification axis have a hierarchical structure, and the hierarchical structure is coded, and the auxiliary management unit includes a classification code converting unit for converting the classification item into the code. The relational database management system according to claim 1. 3. A multi-dimensional search means for converting a search condition into a rectangular or rectangular parallelepiped region of a multi-dimensional space constituted by each of a plurality of classification axis items of an auxiliary database as an axis. The relational database management system according to claim 1. 4. The relational database management system according to claim 1, wherein the auxiliary database has an index information item for storing information for specifying a physical position of each data of the main database. 5. An auxiliary database maintenance means for automatically updating the corresponding item of the auxiliary database with respect to the data when the auxiliary management means updates data addition, correction, etc. in the main database. 5. The relational database management system according to any one of 1 to 4. 6. A storage medium storing a program for executing the relational database management system according to claim 1.