JP6239407B2 - Database system - Google Patents

Description

  Embodiments described herein relate generally to a database system.

  A relational database is a database that operates data (tables) managed in a tabular format using SQL (Structured query language). A relational database can dynamically associate a plurality of tables by specifying a join condition in SQL.

  The process of associating multiple tables (join: JOIN) can be very heavy depending on the conditions. For this reason, a cache table that combines multiple tables is prepared in advance. Has also been done.

JP 2006-343798 A

  There are a synchronous type and an asynchronous type as a countermeasure when the table that is the basis of the cache table is updated. In the synchronous type, the cache table is updated immediately when the table is updated. On the other hand, in the asynchronous type, the update for each period is updated in a lump for every fixed period.

  However, the synchronous type may increase the update time, while the asynchronous type may refer to old data before update.

  An object of the present invention is to provide a database system that efficiently updates a cache table.

According to an embodiment, the database system uses a second table in which the deformation of two or more of the first table and the two or more first table to suit the reference request in advance, in response to a data reference request . The database system includes first management means and second management means. The first management means manages the two or more first tables. The second management means manages the second table. The second table is generated by combining the two or more first tables so as to be suitable for the reference request, and for each data set in which the data of the two or more first tables are combined. , Two or more key fields that store primary key values of the two or more first tables, and a first flag that stores a value indicating whether or not the data set is valid. The first management means includes notification means. When the data in the two or more first tables is updated, the notification unit transmits an update notification indicating the update contents to the second management unit. The second management means includes invalidation means and update means. The invalidation means invalidates the data in the second table corresponding to the update content indicated by the update notification when the update notification is received. The update means receives a reference request for data of the two or more first tables, and the data of the second table necessary for responding to the reference request for the data is invalidated The data is acquired from the two or more first tables and stored in the second table. The invalidation means is applicable when the update content indicated by the update notification is an update other than the key value related to the combination of the two or more first tables and other than the primary key value of the two or more first tables. The value of the first flag corresponding to the data set is updated to a value indicating invalidity, and the update content indicated by the update notification is a key value related to the combination of the two or more first tables or the two or more first tables. The primary key value of the first table, the key field value other than the key field storing the primary key value of the first table that is the update target in the two or more key fields corresponding to the corresponding data set; The value of the first flag is updated to a value indicating invalidity.

The figure which shows the system configuration | structure of the database system of 1st Embodiment. The figure which shows an example which produces | generates the table for a reference from the table for an update. The figure which shows the general example which updates the table for a reference synchronously when the table for an update is updated. The figure which shows the general example which updates the table for a reference asynchronously when the table for an update is updated. The figure for demonstrating the general view about the update of the reference table when the table for an update is updated. The figure for demonstrating the basic view about the update of the reference table when the update table in the database system of 1st Embodiment is updated. The figure which shows an example when the table for an update is frequently updated locally. The figure which shows the flow of a process regarding the update of the table for a reference accompanying the update of the table for an update in "update side initiative". The 1st figure for demonstrating the calculation required at the time of the update of the table for an update in the case of "update side initiative". The 2nd figure for demonstrating the calculation required at the time of the update table update in the case of "update side initiative". The figure which shows the flow of a process regarding the update of the reference table accompanying the update of the update table in "reference side initiative". The figure which compares the flow of a process regarding the update of the reference table when the update table is updated locally by "update side initiative" and "reference side initiative." The figure which shows the example of 1 structure of the table for a reference in the database system of 1st Embodiment. The figure which shows one structural example of the typical reference table in "update side initiative." The figure for demonstrating the procedure which updates the reference table of FIG. 14 with the update of the table for an update in "update side initiative". The functional block diagram regarding the update of the reference table of the database system of 1st Embodiment. The figure for demonstrating the analysis of the query which requests | requires the production | generation of the reference table performed with the database system of 1st Embodiment. The 1st figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 1st Embodiment. The 2nd figure for demonstrating the update procedure by the "reference side initiative" of the table for a reference by the database system of 1st Embodiment. The 3rd figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 1st Embodiment. FIG. 14 is a fourth diagram for explaining an update procedure of “reference side initiative” of the reference table by the database system of the first embodiment. The 5th figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 1st Embodiment. The 6th figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 1st Embodiment. The 7th figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 1st Embodiment. The 8th figure for demonstrating the update procedure by the "reference side initiative" of the table for a reference by the database system of 1st Embodiment. The figure which shows the operation | movement procedure at the time of the production | generation of the reference table of the update side database engine in the database system of 1st Embodiment. The figure which shows the operation | movement procedure at the time of the production | generation of the reference table of the reference side database engine in the database system of 1st Embodiment. The figure which shows the operation | movement procedure at the time of the update of the update table of the update side database engine in the database system of 1st Embodiment. The figure which shows the operation | movement procedure at the time of the update of the update table of the reference side database engine in the database system of 1st Embodiment, and the reception of a reference request. The figure which compares the processing amount with the case where the update of the reference table accompanying the update of the table for an update is performed by "update side initiative" and the case where it performs by "reference side initiative". The figure which compares the time required when the update of the reference table accompanying the update of the update table is performed by “update side initiative” and “reference side initiative”. The figure which shows the example of 1 structure of the table for a reference in the database system of 2nd Embodiment. The 1st figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 2nd Embodiment. The 2nd figure for demonstrating the update procedure by the "reference side initiative" of the reference table by the database system of 2nd Embodiment. The figure which shows the example of 1 structure of the table for a reference in the database system of 3rd Embodiment.

  Hereinafter, embodiments will be described with reference to the drawings.

(First embodiment)
FIG. 1 is a diagram showing a system configuration of a database system 1 according to the present embodiment.

  As shown in FIG. 1, the database system 1 includes one or more databases 10 each accepting a data processing request from the application program 2. Here, although two databases 10 are shown, it is only an example and the present invention is not limited to this. That is, the database system 1 can be constituted by one database 10 or can be constituted by three or more databases 10.

  The database 10 is a relational database that can combine a plurality of tables, and includes a controller 11 and a file system 12. The application program 2 includes a GUI (Graphical user interface) unit 21 and a logic / arithmetic operation unit 22. For example, the logic / arithmetic operation unit 22 is in SQL in response to an instruction from the user received by the GUI unit 21. The described data processing request is issued to the database 10, and the returned data processing result is presented to the user by the GUI unit 21. Data processing requests issued by the application program 2 are roughly classified into data reference requests and data update requests, and the data update requests include data addition requests and deletion requests. A data processing request described in SQL is referred to as an SQL sentence or a query.

  The controller 11 of the database 10 is a module that serves as a reception window for receiving data processing requests from the application program 2. The controller 11 has a database engine 100 that executes the requested data processing. Further, the controller 11 has a function of communicating with the controller 11 of another database 10. Therefore, the application program 2 may issue a data processing request involving a combination of a table managed by the database 10 that is the destination of the data processing request and a table managed by the database 10 different from the database 10. it can. In other words, the application program 2 can set all tables existing in one or more databases 10 constituting the database system 1 as data processing targets. The file system 12 of the database 10 is a module that manages the location of various data, including table data, in the database 10 on the storage medium.

  The database system 1 has a function in which each of the databases 10 prepares a cache table in advance so that the table is suitable for a reference request, and responds to a data reference request using the table and the cache table. have. A transformation is typically the joining of two or more tables. The database system 1 implements a mechanism for efficiently updating the cache table when the base table of the cache table is updated. This mechanism will be described in detail below.

  First, in order to help understand the mechanism for efficiently updating the cache table implemented by the database system 1, referring to FIG. 2 to FIG. 4, the general case where the table that is the basis of the cache table is updated is described. A typical countermeasure will be described.

  FIG. 2 shows an example in which a cache table is generated by combining two tables (tables A and B). Hereinafter, the cache table is referred to as a reference table 201 (materialized view: MATERIALIZED VIEW [MVIEW]), and the table that is the basis of the cache table is referred to as an update table 202.

  In FIG. 2, “CREATE MATERIALIZED VIEW READABLE AS SELECT name, location FROM A, B WHERE a2 = 1 and a3 = b0 and b2 = 'now'” is a query for generating the reference table 201 from the update table 202 It is. In this query, a conditional statement is described in the “WHERE” part, and the conditional statement can include a join condition and a narrowing condition. “A3 = b0” is a join condition, and specifies that a row in which the value stored in the third field of the table A matches the row number of the table B is joined. “A2 = 1” and “b2 = 'now'” are narrowing conditions, the value stored in the second field of table A is “1”, and the value stored in the second field of table B This is to specify that the value is narrowed down to the “now” line. The “SELECT” part describes the item name (field name) to be extracted, and the “FROM” part describes the file name (table name) to be processed. Here, the item name of the first field of the table A is “name”, and the item name of the first field of the table B is “location”.

  By generating the reference table 201 shown in FIG. 2 in advance, a reference request for data including “SELECT name, location FROM A, B WHERE a2 = 1 and a3 = b0 and b2 = 'now'” is performed thereafter. When a certain query is issued, the requested data can be promptly returned without combining Table A and Table B.

  Incidentally, as described above, the query issued by the application program 2 includes not only a data reference request but also a data update request. Therefore, it is conceivable that the update table 202 is updated after the reference table 201 is generated. In this case, it is necessary to reflect the updated contents in the reference table 201. Here, it is assumed that the first field of the row with the row number “2” of the table A that is the update table 202 that is the basis of the reference table 201 is updated from “Tanaka” to “Satonaka”. . Hereinafter, the row number may be referred to as a primary key value.

  FIG. 3 shows an example in which the update contents of the update table 202 are reflected in the reference table 201 synchronously.

  As shown in FIG. 3, when the update table 202 is updated, if the reference table 201 is updated promptly, the data returned in response to the data reference request is always the latest data. On the other hand, the reference table 201 may be frequently updated, which may increase the update time.

  FIG. 4 shows an example in which the update contents of the update table 202 are asynchronously reflected in the reference table 201.

  Even if the update table 202 is updated, instead of updating the reference table 201 immediately, as shown in FIG. 4, if the update for each period is updated in batches at regular intervals, the update is performed. Time can be kept within the expected range. On the other hand, data returned in response to a data reference request may become old data before update.

  In consideration of the features of both the synchronous type and the asynchronous type, which are general countermeasures when the update table 202 that is the basis of the reference table 201 is updated, FIG. With reference to FIG. 6, a basic concept for updating the reference table 201 when the update table 202 in the database system 1 is updated will be described.

  First, FIG. 5 shows a general concept for updating the reference table 201 when the update table 202 is updated.

  The above-described countermeasure (synchronous) for reflecting the update contents of the update table 202 on the reference table 201 synchronously and the countermeasure for reflecting the update contents of the update table 202 asynchronously on the reference table 201 (asynchronous) Both types are based on the concept of “update side initiative”, that is, the update of the reference table 201 is executed when the event of the update of the update table 202 occurs.

  Next, FIG. 6 shows a basic concept for updating the reference table 201 when the update table 202 in the database system 1 is updated.

  In the database system 1, when updating the update table 202, only the old data is invalidated, and the reference table 201 is not updated. In FIG. 6, the pointer value indicating the location of the data set including the old data is updated to “0”. Here, “0” is defined as a value indicating invalidity.

  In the database system 1, when the corresponding data is invalidated when the reference table 201 is referenced, new data is acquired from the update table 202 and the reference table 201 is updated. In this way, the database system 1 adopts the concept of “reference side initiative”.

  FIG. 7 shows an example in which the update table 202 that is the basis of the reference table 201 is frequently updated locally.

  Consider a case where the same portion of data referred to at a certain time (a1 in FIG. 7) is frequently updated (a2 to a6 in FIG. 7). Here, “3” at the time of reference is updated as “3” → “8” → “9” → “5” → “9” → “6”. During this time, it is assumed that this data is not referred to, and then this data is referred to twice (a7 and a8 in FIG. 7).

  In such a case, in “update side initiative”, particularly in the “synchronous type”, the reference table 201 related to this data is updated by the number of times of update. Also in the “asynchronous type”, if each update is performed within different periods, the reference table 201 related to this data is updated by the number of times of update as in the “synchronous type”. During this time, since this data is not referred to, the update of “3” → “8” → “9” → “5” → “9” is useless.

  On the other hand, in the “reference side initiative”, new data is acquired and the reference table 201 is updated at the time of the first reference after the update (a7 in FIG. 7). Can be eliminated. Further, at the time of subsequent reference (a8 in FIG. 7), the new data stored in the reference table 201 can be referred to promptly.

  In this way, “reference side initiative” minimizes the overhead associated with data update while ensuring that the latest data is always returned in response to a data reference request.

  FIG. 8 is a diagram illustrating a flow of processing related to the update of the reference table 201 accompanying the update of the update table 202 in “update side initiative”. Here, it is assumed that a module corresponding to a data reference request and a module corresponding to a data update request are provided. The module corresponding to the data reference request is a module that manages the reference table 201, and the module corresponding to the data update request is the module that manages the update table 202.

  As shown in FIG. 8, in the “update side initiative”, when an update of the update table 202 is requested (b1 in FIG. 8), in addition to the update of the update table 202, in each case (“synchronous”) Calculation is performed to check whether the new data meets the conditions of the reference table 201 (i.e., deviated from the conditions of the reference table 201) (b2 in FIG. 8). Data for updating the table 201 is transferred (b3 in FIG. 8). Reflection of the update data to the reference table 201 (b4 in FIG. 8) is also executed when the update table 202 is updated. These processes are executed regardless of whether the reference table 201 is referenced (b5 in FIG. 8).

  First, referring to FIG. 9 and FIG. 10, whether or not new data meets the conditions of the reference table 201 when the update table 202 is updated, which is necessary in the case of “update side initiative” (reference table). The calculation for confirming whether or not the condition 201 is not met will be described.

  FIG. 9 shows an example in which the third field (a3) of the row with the row number “2” in the table A is changed from “1” to “3”. Since this field is a field related to the join condition, and the second field (b2) of the table B to be joined is a field related to the narrowing condition, the join is performed again based on the new data, and the join destination The value of the second field of the table B is confirmed. The value is “old” and does not satisfy the narrow-down condition.

  At this time, when the value of the second field of the table B joined by the old data “1” before update is confirmed, the value is “now”, which satisfies the narrowing condition. Thereby, it is understood that one row needs to be deleted from the reference table 201.

  On the other hand, FIG. 10 shows an example in which the third field (a3) of the row with the row number “3” in the table A is changed from “3” to “1”. Since this field is a field related to the join condition, and the second field (b2) of the table B to be joined is a field related to the narrowing condition, the join is performed again based on the new data, and the join destination The value of the second field of the table B is confirmed. The value is “now”, which satisfies the narrow-down condition.

  At this time, when the value of the second field of the table B joined by the old data “3” before update is confirmed, the value is “old” and does not satisfy the narrowing condition. As a result, it can be seen that one row needs to be added to the reference table 201. Note that, when the narrowing-down condition is satisfied, it is understood that the data set existing on the reference table 201 side needs to be updated. For example, when the old data before update is “2”, “Yokohama” in the corresponding data set on the reference table 201 side must be updated to “Kawasaki”.

  As described above, in the case of “update side initiative”, when the update table 202 is updated, a calculation with a heavy load is essential.

  FIG. 11 is a diagram illustrating a flow of processing related to the update of the reference table 201 accompanying the update of the update table 202 in “reference side initiative”.

  As shown in FIG. 11, in “reference side initiative”, when an update of the update table 202 is requested (c1 in FIG. 11), the update of the update table 202 (c2 in FIG. 11) and notification of the update (C3 in FIG. 11) is performed. In addition, with respect to the reference table 201, only deletion of the row related to the data notified of the update (c4 in FIG. 11), that is, invalidation is performed, and the update of the reference table 201 is not executed.

  In addition, when the update of the update table 202 (c2 in FIG. 11) is an update after the second place for the same record of the same table, or data that is not related to the reference table 201 in the first place. In such a case, the update notification (c3 in FIG. 11) is not performed, and the corresponding line in the reference table 201 is not deleted (c4 in FIG. 11).

  Then, when referring to the reference table 201 (c5 in FIG. 11), only when the corresponding data does not exist, the data is acquired from the update table 202 and the reference table 201 is updated (c6 in FIG. 11). ).

  FIG. 12 is a diagram comparing the flow of processing related to the update of the reference table 201 when the update table 202 is locally updated between “update side initiative” and “reference side initiative”.

  After the reference table 201 is updated, if the data is not referenced, the update is useless. Therefore, as shown in FIG. 12, in the “update side initiative”, for example, when the update table 202 is locally updated, many unnecessary updates of the reference table 201 occur. On the other hand, in the “reference side initiative”, the update of the reference table 201 is limited to the necessary reference. From this, it can be said that “reference side initiative” is effective for burst processing.

  Next, the basic principle by which the database system 1 realizes the “reference side initiative” will be described.

  FIG. 13 is a diagram illustrating a configuration example of the reference table 201 in the database system 1 that is “referred by the reference side”.

  Here, in order to compare with a configuration example of the reference table 201 shown in FIG. 13, referring to FIGS. 14 and 15 once, a configuration example of a typical reference table 201 in “update side initiative”. The update procedure of the reference table 201 accompanying the update of the update table 202 will be described.

  As shown in FIG. 14, in the case of “update side initiative”, the reference table 201 usually has a row number (rowid) that is a combination of row numbers (primary key values) of the joined tables and the arrangement of the data set. And a pointer (ptr) indicating the location.

  Now, it is assumed that the data stored in the update table 202 and cached on the reference table 201 side is updated from “Tanaka” to “Satonaka”.

  In this case, as shown in FIG. 15, the join is executed for the row including the updated data, the row number in the reference table 201 is calculated, and transferred as the update data for the reference table 201. Then, the reference table 201 is updated based on the row number attached to the update data.

  Reference is again made to FIG.

  Regarding this reference table 201, this database system 1 that is “referred by the reference side” has a key field for storing the primary key value for each table (update table 202) separately from the row number (rowid) (rA , rB). In the database system 1, first, a flag indicating whether or not the cache is valid is provided for each line (cache). Second, each table (update table 202) has an index (index_rA, index_rB) for enabling direct search of the row number by the primary key value. Third, each table (update table 202) has a flag indicating whether or not the designation of the primary key value as the narrowing condition is valid.

  Hereinafter, it will be described how the database system 1 updates the reference table 201 in “reference side initiative” by configuring the reference table 201 as illustrated in FIG. 13.

  FIG. 16 is a functional block diagram relating to the update of the reference table 201 of the database system 1.

  The database engine 100 manages the reference table 201 and the update table 202 in the database system 1. 16A shows functional blocks of the database engine 100 that operate as a module corresponding to a data reference request, that is, a module that manages the reference table 201, and FIG. 16B shows a data update request. A functional block of the database engine 100 that operates as a corresponding module, that is, a module that manages the update table 202 is shown. One database engine 100 may be operated as both a module corresponding to a data reference request and a module corresponding to a data update request, and one of the two database engines 100 corresponds to a data reference request. The other module may be operated as a module corresponding to a data update request. Hereinafter, the database engine 100 that operates as a module corresponding to a data reference request is referred to as a reference-side database engine 100, and the database engine 100 that operates as a module corresponding to a data update request is referred to as an update-side database engine 100.

  As shown in FIG. 16A, the reference-side database engine 100 includes an SQL sentence analysis unit 111, a data control unit 112, a data change unit 113, a data request unit 114, and a memory 115.

  The SQL sentence analysis unit 111 is a module that analyzes a reference request for data described in SQL, that is, a query. The SQL sentence analysis unit 111 determines whether the reference request is applicable to the reference table 201. The data reference request to which the reference table 201 can be applied is returned after the data is acquired from the reference table 201, and the data reference request to which the reference table 201 is not applicable is acquired from the update table 202. Will be returned.

  The data control unit 112 is a module that returns data requested to be referenced, and whether or not the data requested to be referenced exists in the reference table 201 (whether or not the data in the reference table 201 is valid). It has the function to judge. The data changing unit 113 is a module that executes an update of the reference table 201 such as invalidating the corresponding data in the reference table 201 when receiving an update notification of the update table 202 from the update-side database engine 100. . The data changing unit 113 has a function of creating the reference table 201 based on the schema sent as an update notification.

  When the data control unit 112 determines that the data requested for reference does not exist in the reference table 201, the data request unit 114 acquires the corresponding data from the update table 202 and stores it in the reference table 201. It is a module to do. The memory 115 is a storage area for storing the reference table 201.

  Further, as shown in FIG. 16B, the update-side database engine 100 includes an SQL sentence analysis unit 121, a data control unit 122, an update notification unit 123, and a memory 124.

  The SQL sentence analysis unit 121 is also a module that analyzes a query, like the SQL sentence analysis unit 111 of the reference database engine 100. The data update request also includes a cache table generation request, that is, a reference table 201 generation request, and the SQL statement analysis unit 121 determines whether or not the query requests generation of the reference table 201. It has a function to judge. The SQL sentence analysis unit 121 has a function of extracting a conditional sentence including a narrowing condition and a joining condition from a query that requests generation of the reference table 201. The extraction of the conditional statement is performed as a preliminary preparation for performing the update of the reference table 201 accompanying the update of the update table 202 by “reference side initiative”, which will be described later.

  The data control unit 122 is a module that updates data in the update table 202 for which an update is requested. At the time of this update, the data control unit 122 stores in the memory 124 updated row information 210 indicating the row number of the row whose data has been updated. When the generation of the reference table 201 is requested, the data control unit 122 creates a schema for the reference table 201.

  The update notification unit 123 is a module that transmits the schema created by the data control unit 122 and the updated row information 210 stored in the memory 124 to the reference database engine 100 as an update notification. The memory 124 is a storage area for storing updated row information 210.

  Here, with reference to FIG. 17, the analysis of the query which requests | requires the production | generation of the reference table 201 by the SQL sentence analysis part 121 of the update side database engine 100 is demonstrated.

  Assume that a query such as “CREATE MATERIALIZED VIEW READABLE AS SELECT name, location FROM A, B WHERE a2 = 1 and a3 = b0 and b2 =’ now ’” is issued. The SQL sentence analysis unit 121 extracts only the conditional sentence, that is, “WHERE a2 = 1 and a3 = b0 and b2 =“ now ”” from the query. The SQL statement analysis unit 121 also includes a key (a3, b0) of each table specified by the join condition (a3 = b0) included in the extracted conditional statement and a primary key (a0, b0) of each table to be joined. ) As a key related to the combination.

  Next, with reference to FIG. 18 to FIG. 26, the “reference side initiative” update procedure of the reference table 201 using the key acquired as advance preparation will be described. Among these, FIGS. 18 to 22 are diagrams relating to the update of the update table 202, and FIGS. 23 to 26 are diagrams relating to the reference table 201 being referred to.

  FIG. 18 shows an example of the update of the update table 202 that is a change other than the key acquired as advance preparation.

  As shown in FIG. 18, here, the first field (a1) in the row of the row number (a0) “2” of the table A is updated from “Tanaka” to “Satonaka”. Since “a1” is not a key acquired as a pre-preparation, that is, a key related to the join, the update notification unit 123 of the update-side database engine 100 updates the data set including the data of this row to be invalidated. (Clear) is transmitted to the reference-side database engine 100. This update notification includes information (A2) indicating the row number “2” of the table A.

  On the other hand, when the reference-side database engine 100 receives this update notification, the data changing unit 113 acquires the row number of the reference table 201 with the index for the table A and updates the flag (cache) of the row to be invalid. To do. Here, for each flag, “1” indicates valid and “0” indicates invalid.

  When updating the update table 202, which is a change other than the key acquired as a preliminary preparation, the process ends only by performing the above processing.

  FIG. 19 shows an example of the update of the update table 202 that is a change of the key acquired as a preliminary preparation, and the primary key value of the updated row exists in the index for the table. The case where it exists in the index is a case where a data set including the data of the row exists on the reference table 201 side or has existed in the past.

  As shown in FIG. 19, here, the third field (a3) of the row with the row number (a0) “2” in the table A is updated from some value to “3”. Since “a3” is a key acquired as a preliminary preparation, that is, a key related to the combination, the update notification unit 123 of the update-side database engine 100 sends an update notification (Key) for invalidating the combination with this row. It transmits to the reference side database engine 100. This update notification includes information (A2) indicating the row number “2” of the table A.

  On the other hand, when the reference-side database engine 100 receives this update notification, the data change unit 113 acquires the row number of the reference table 201 using the index for the table A, and all the key fields other than the table A in the row (RB), flag (cache) and pointer (ptr) are updated invalid. In addition, when the data changing unit 113 updates the key field to be invalid, the data changing unit 113 invalidates and updates the flag corresponding to the key field as to whether or not the designation of the primary key value as the narrowing condition is valid.

  FIG. 20 shows an example of the update of the update table 202 that is a change of the key acquired as a preliminary preparation, and the primary key value of the updated row does not exist in the index for the table. . When the data does not exist in the index, the join condition by the data before the update does not satisfy the narrowing condition, and the data set including the data of the row is not created on the reference table 201 side, or the primary key value is stored. This is a case where all the key fields that have been updated are invalid.

  As shown in FIG. 20, here, the third field (a3) in the row of the row number (a0) “2” of the table A is updated from some value to “1”. Since “a3” is a key acquired as a preliminary preparation, that is, a key related to the combination, the update notification unit 123 of the update-side database engine 100 sends an update notification (Key) for invalidating the combination with this row. It transmits to the reference side database engine 100. This update notification includes information (A2) indicating the row of the row number “2” in the table A.

  On the other hand, when the reference-side database engine 100 receives this update notification, the data changing unit 113 tries to acquire the row number of the reference table 201 using the index for the table A. When the primary key value of the updated row does not exist in the index for the table A, the data changing unit 113 adds a row to the reference table 201 and also adds the row of the added row with the updated primary key value. Also add an index to get the line number. The key field (rA) of the added row table A stores the primary key value “2” that is the row number of the updated row, and all other key fields (rB), flags (cache), and pointers. A value “0” indicating invalidity is stored in (ptr). Further, when a key field storing a value “0” indicating invalidity is generated, the data changing unit 113 determines whether or not the designation of the primary key value as a narrowing condition corresponding to the key field is valid. Update the flag to invalid.

  FIG. 21 shows an example in which a row is added to the update table 202, which is a change of the key acquired as advance preparation.

  As shown in FIG. 21, the row with the row number (a0) “5” is added to the table A here. Since the data of “a0” and “a3”, which are keys related to the combination, is added, the update notification unit 123 of the update-side database engine 100 sends an update notification (Key) for invalidating the combination with this row. It transmits to the reference side database engine 100. This update notification includes information (A5) indicating the row number “5” of table A.

  In this case, the processing performed by the data change unit 113 of the reference database engine 100 that has received this update notification is the update of the update table 202 that is a change of the key acquired as a preliminary preparation described with reference to FIG. Thus, the processing is the same as the processing when the primary key value of the updated row does not exist in the index for the table. That is, the data changing unit 113 adds a row to the reference table 201 and also adds an index so that the row number of the added row can be obtained with the updated primary key value of the row. The key field (rA) of the added row table A stores the primary key value “5” which is the row number of the updated row, and all other key fields (rB), flags (cache), and pointers. A value “0” indicating invalidity is stored in (ptr). Further, when a key field storing a value “0” indicating invalidity is generated, the data changing unit 113 determines whether or not the designation of the primary key value as a narrowing condition corresponding to the key field is valid. Update the flag to invalid.

  On the other hand, FIG. 22 shows an example in which a row is deleted from the update table 202, which is a change of the key acquired as a preliminary preparation.

  As shown in FIG. 22, the row with the row number (a0) “2” is deleted from the table A here. Since the data of “a0” and “a3”, which are keys related to the combination, is deleted, the update notification unit 123 of the update-side database engine 100 sends an update notification (Key) for invalidating the combination with this row. It transmits to the reference side database engine 100. This update notification includes information (A2) indicating the row number “2” of the table A.

  In this case, the processing performed by the data change unit 113 of the reference database engine 100 that has received this update notification is the update of the update table 202 that is a change of the key acquired as a preliminary preparation described with reference to FIG. Thus, the processing is the same as the processing when the primary key value of the updated row exists in the index for the table. That is, the data changing unit 113 acquires the row number of the reference table 201 using the index for the table A, and stores all the key fields (rB), flags (cache), and pointers (ptr) other than the table A in that row. Update to invalid. In addition, when the data changing unit 113 updates the key field to be invalid, the data changing unit 113 invalidates and updates the flag corresponding to the key field as to whether or not the designation of the primary key value as the narrowing condition is valid.

  The above is the update procedure of the reference table 201 when the update table 202 of the database system 1 is updated. Even when the update table 202 is updated, the update-side database engine 100 does not satisfy the narrowing-down condition included in the conditional sentence extracted by the SQL sentence analysis unit 121 when the reference table is generated. If the update is for data, the update notification unit 123 does not transmit the update notification to the reference database engine 100.

  FIG. 23 shows an example in which the primary key value of the row updated on the update table 202 is specified as a narrowing condition, and the update is a change other than the key acquired as advance preparation. ing.

  As shown in FIG. 23, here, the primary key (a0) value “2” of the table A is designated as the narrowing condition. In the row of the row number (a0) “2” in the table A, the first field (a1) is updated from “Tanaka” to “Satonaka”. Since “a1” is not a key acquired as a pre-preparation, that is, a key related to the join, the flag (cache) of the row is updated to be invalid on the reference table 201.

  The reference-side database engine 100 returns the data requested for reference by the data control unit 112. When the flag (cache) of the reference target row indicates valid, the data control unit 112 uses the data set existing at the arrangement location indicated by the pointer (ptr) of the row to request the reference Can be returned.

  On the other hand, when the flag (cache) of the reference target row indicates invalidity, the data control unit 112 determines that the data requested to be referenced does not exist in the reference table 201 (data in the reference table 201 is not valid). judge. In this case, the data request unit 114 creates a query in which the primary key values (“2”, “1”) stored in the key fields (rA, rB) of the row are specified as the filtering condition, and the updated data Is obtained from the update table 202. The data request unit 114 updates the pointer (ptr) of the reference target row to a value indicating the location of the acquired data set, and effectively updates the flag (cache) of the reference target row. Then, the data control unit 112 uses this data set to return the data requested for reference.

  By creating a query in which the primary key value stored in the key field of the row to be referenced is specified as a refinement condition, the latest data set can be quickly acquired from the update table 202 at the time of reference. .

  FIG. 24 is a reference that designates the primary key value of a row that has been joined (not updated) with a row that has been updated on the update table 202 as a refinement condition, and that update was acquired as a preliminary preparation. An example of a change other than a key is shown.

  As shown in FIG. 24, here, the primary key (b0) value “1” of the table B is specified as the narrowing-down condition. The row with the row number (b0) “1” in the table B is not updated, but the row with the row number (a0) “2” and the row number (a0) “3” in the table A to which this row is joined. The first field (a1) of the row with the row number (a0) “2” is updated from “Tanaka” to “Satonaka”. Since “a1” is not a key acquired as a pre-preparation, that is, a key related to the join, the flag (cache) of the row is updated to be invalid on the reference table 201.

  Since the flag (cache) of the reference target row indicates invalidity, the reference-side database engine 100 uses the primary key value of the row updated on the update table 202 described with reference to FIG. It is a reference to be specified, and the same processing as that in the case where the update is a change other than the key acquired as advance preparation is performed. That is, the data control unit 112 determines that the data requested to be referenced does not exist in the reference table 201 (the data in the reference table 201 is not valid), and the data request unit 114 displays the key field ( A query specifying the primary key values (“2”, “1”) stored in rA, rB) as a narrowing condition is created, and a data set including the updated data is acquired from the update table 202.

  Also in this case, since the query specifying the primary key value stored in the key field of the row to be referenced as a narrowing condition is created, the latest data set is quickly acquired from the update table 202 at the time of reference. be able to.

  FIG. 25 shows an example in which the primary key value is specified as a narrowing condition, and the reference table 201 is not in a valid state for specifying the primary key value. The case where the specification of the primary key value as the filtering condition is not valid is when there is a row storing a value indicating invalidity in the key field of the primary key value. For example, the key acquired as a preliminary preparation This corresponds to a case where a row is added or the key field of the primary key value in a certain row is invalidally updated in association with the update of the update table 202 that is a change in the above.

  As shown in FIG. 25, here, the primary key (b0) value “1” of the table B is specified as the narrowing-down condition. In addition, since the key field (rB) for the table B in the row of the row number (rowid) “3” of the reference table 201 indicates invalidity, as a narrowing condition corresponding to the key field (rB) A flag indicating whether the specification of the primary key value is valid indicates invalid. There is a possibility that “1” should be stored in the key field (rB) for the table B of the row with the row number “3”, and such a key field for the table B ( The presence / absence of a row for which rB) is indeterminate can be immediately determined by a flag corresponding to the key field (rB) for the table B whether or not the designation of the primary key value as a narrowing condition is valid. it can.

  In this case, in the reference-side database engine 100, first, the data control unit 112 determines that the data requested for reference does not exist in the reference table 201 (data in the reference table 201 is not valid). Then, the data request unit 114 creates a query using the primary key (b0) value “1” of the table B specified as the narrowing condition in the reference request, and the table B specified as the narrowing condition in the reference request. A data set related to the primary key (b0) value “1” is acquired from the update table 202. By creating a query using the primary key value specified as a narrowing condition in the reference request, acquisition of a data set from the update table 202 at the time of reference can be minimized.

  The data request unit 114 stores the pointer (ptr) for the location of the acquired data set for the row in which the primary key value “1” is stored in the key field (rB) and the flag (cache) indicates invalid. Update to the indicated value and update the flag (cache) effectively. Further, for a row in which a value indicating invalidity is stored in the key field (rB) and the primary key value “1” should have been stored in the key field (rB), The primary key value “1” is stored in the key field (rB). At this time, if there is no more row storing a value indicating invalidity in the key field (rB), the data requesting unit 114 is valid to specify the primary key value as the narrowing condition corresponding to the key field (rB). The status flag is updated effectively.

  The above is the update procedure of the reference table 201 when referring to the reference table 201 of the database system 1.

  As described above, the database system 1 realizes the update of the reference table 201 accompanying the update of the update table 202 on the “reference side initiative” basis.

  FIG. 26 is a diagram illustrating an operation procedure when the reference database 201 of the update-side database engine 100 is generated.

  When the update-side database engine 100 receives the query (block A1), the SQL statement analysis unit 121 determines whether the query requests generation of the reference table 201 (block A2). If the SQL statement analysis unit 121 determines that the query is a query that requests generation of the reference table 201 (YES in block A2), the data control unit 122 creates a schema for the reference table 201 (block A3). ). The update notification unit 123 transmits the schema created by the data control unit 122 to the reference database engine 100 as an update notification (block A4).

  FIG. 27 is a diagram illustrating an operation procedure when the reference database 201 of the reference-side database engine 100 is generated.

  When the reference database engine 100 receives the schema as the update notification (block B1), the data changing unit 113 creates the reference table 201 having the configuration shown in FIG. 13 in the memory 115 based on this schema (block). B2).

  FIG. 28 is a diagram illustrating an operation procedure when updating the update table 202 of the update-side database engine 100.

  When the update-side database engine 100 receives the query (block C1), the SQL statement analysis unit 121 determines whether or not the query requests an update of the update table 202 (block C3). If the SQL statement analysis unit 121 determines that the query is a request for updating the update table 202 (YES in block C3), information on the update target row (update row information 210) is stored in the memory 124. (Block C4).

  When a query requesting a commit for this update is received (YES in block C2), the data control unit 122 executes a commit process (block C6). In addition, the update notification unit 123 transmits an update notification to the reference database engine 100 based on the updated row information 210 stored in the memory 124 (block C7).

  FIG. 29 is a diagram illustrating an operation procedure when the update table 202 of the reference database engine 100 is updated and when a reference request is received.

  When the reference database engine 100 receives an update notification of the update table 202 from the update database engine 100 (block D1), the data changing unit 113 changes the reference table 201 according to the notification content (block). D2). The change of the reference table 201 at this time is not to reflect the update data but to record that the update data needs to be reflected at the time of reference.

  Further, when the reference database engine 100 receives a query for requesting data reference (block D3), the SQL sentence analysis unit 121 determines whether the request is applicable to the reference table 201. Determination is made (block D4). If the reference table 201 is applicable (YES in block D4), the data control unit 112 returns the data if the reference target data exists in the reference table 201 (NO in block C5). (Block D8).

  On the other hand, when the reference target data does not exist in the reference table 201 (YES in block C5), the data request unit 114 uses the information in the reference table 201 to retrieve the reference target data from the update table 202. Obtain (block C6). The data change unit 113 stores the data acquired by the data request unit 114 in the reference table 201 (block D7), and the data control unit 112 returns the data (block D8).

  FIG. 30 is a diagram for comparing the processing amount when the update of the reference table 201 accompanying the update of the update table 202 is performed by “update side initiative” and when it is performed by “reference side initiative”. FIG. 31 is a diagram for comparing the required time when the update of the reference table 201 accompanying the update of the update table 202 is performed by “update side initiative” and when it is performed by “reference side initiative”.

  As is clear from FIG. 30 and FIG. 31, the amount of processing and the time required for updating the reference table 201 accompanying the update of the update table 202 are both “reference side initiative” and “update side initiative”. Is much less.

  As described above, according to the database system 1, it is possible to efficiently update the cache table.

(Second Embodiment)
Next, a second embodiment will be described.

  FIG. 32 is a diagram showing a configuration example of the reference table in the database system 1 of the present embodiment.

  As shown in FIG. 32, the database system 1 further has a flag indicating whether or not the cache of each row of the reference table 201 is valid for each table (cacheA, cacheB). In FIG. 32, the flag (cacheA) relating to the table A in the row of the row number (rowid) “B” of the reference table 201 indicates invalid. On the other hand, the flag (cacheB) relating to the table B in this row indicates validity. That is, whether or not the data is valid for each table of the update table 202 is managed instead of the data set unit in which the data of the table A and the data of the table B are combined. First, with reference to FIG. 33, an operation procedure at the time of updating the update table 202 in which the reference table 201 of the database system 1 is in the state shown in FIG. 32 will be described.

  As shown in FIG. 33, here, the first field (a1) of the row with the row number (a0) “2” in the table A is updated from “Tanaka” to “Satonaka”. “A1” is not a key obtained as a preliminary preparation, that is, a key related to the combination. In this case, the update notification unit 123 of the update-side database engine 100 transmits an update notification (Clear) for invalidating the data in this row to the reference-side database engine 100. This update notification includes information (A2) indicating the row number “2” of the table A.

  On the other hand, when the reference-side database engine 100 receives this update notification, the data change unit 113 acquires the row number of the reference table 201 using the index for the table A, and the flag (cacheA) for the table A of that row. Update to invalid. Thereby, the reference table 201 is in the state shown in FIG.

  Next, referring to FIG. 34, the state of the reference table 201 shown in FIG. 32, that is, the flag (cacheA) related to the table A in the row of the row number (rowid) “B” of the reference table 201 indicates invalid. The operation procedure when referring to the data in this row in the situation where the user is in the state will be described.

  When any of the flags (cacheA, cacheB) in the reference target row indicates invalidity, the data request unit 114 is a query condition included in the generation request of the reference table 201 and is included in the reference request query. Of the conditions "SELECT name, location FROM A, B WHERE a2 = 1 and a3 = b0 and b2 = 'now'", the filtering condition (a2 = 1) for the table with invalid flag (here, table A) Is applied, and a query that specifies the primary key value stored in the key field for the table as a narrowing condition is created. Thereby, it is possible to quickly acquire data indicating that the flag is invalid. The data changing unit 113 updates only the data portion in the data set of the row with the acquired data.

  As described above, according to the database system 1, the efficiency of the update of the reference table 201 accompanying the update of the update table 202, which is a change other than the key related to the join, acquired as advance preparation is further improved. Is realized.

(Third embodiment)
Next, a third embodiment will be described.

  FIG. 35 is a diagram showing a configuration example of a reference table in the database system 1 of the present embodiment.

  As shown in FIG. 35, in this database system 1, each row of the reference table 201 does not have a pointer indicating the location of the data set, but the data constituting the data set for each table of the update table 202. (When data of a plurality of fields is extracted from the same row of the same table, it has a pointer (Aptr, Bptr) indicating the arrangement location of the data set. These pointers (Aptr, Bptr) also serve as a flag indicating whether or not the cache is valid.

  When storing data in a table format, it may occur that the same data is stored in fields in different rows. Therefore, by having a pointer for each table of the update table 202, even in such a case, it is not necessary to secure a storage area for storing the same data for the number of rows, and there is duplication. Realize compression.

  Since the operation procedure of each embodiment can be realized by software (program), the software is installed in a normal computer through a computer-readable storage medium storing this software and executed. The same effect as can be easily realized.

  Although several embodiments of the present invention have been described, these embodiments are presented by way of example and are not intended to limit the scope of the invention. These novel embodiments can be implemented in various other forms, and various omissions, replacements, and changes can be made without departing from the scope of the invention. These embodiments and modifications thereof are included in the scope and gist of the invention, and are included in the invention described in the claims and the equivalents thereof.

  DESCRIPTION OF SYMBOLS 1 ... Database system, 10 ... Database, 11 ... Controller, 12 ... File system, 100 ... Database engine, 111 ... SQL sentence analysis part, 112 ... Data control part, 113 ... Data change part, 114 ... Data request part, 121 ... SQL sentence analysis unit, 122 ... data control unit, 123 ... update notification unit, 201 ... reference table, 202 ... update table

Claims (8)

2 or more of the first table and the two or more first table using the second table in which the modified to suit the reference request in advance, a database system that responds to a data reference request,
First management means for managing the two or more first tables;
Second management means for managing the second table;
Comprising
The second table is generated by combining the two or more first tables so as to be suitable for the reference request, and for each data set in which the data of the two or more first tables are combined. Two or more key fields that store primary key values of the two or more first tables, and a first flag that stores a value indicating whether or not the data set is valid,
The first management means includes notification means for transmitting an update notification indicating the update contents to the second management means when data of the two or more first tables is updated,
The second management means includes
When receiving the update notification, invalidation means for invalidating the data of the second table corresponding to the update content indicated by the update notification;
When the reference request for the data of the two or more first tables is received and the data of the second table necessary for responding to the reference request for the data is invalidated, the data is Updating means for acquiring from two or more first tables and storing in the second table;
Equipped with,
The invalidation means includes
When the update content indicated in the update notification is an update other than the key value related to the combination of the two or more first tables and other than the primary key value of the two or more first tables, the update data corresponding to the corresponding data set Update the value of the first flag to a value indicating invalidity,
When the update content indicated by the update notification is a key value related to the combination of the two or more first tables or the update of the primary key value of the two or more first tables, the two or more corresponding to the corresponding data set Updating the value of the key field other than the key field storing the primary key value of the first table to be updated in the key field and the value of the first flag to a value indicating invalidity.
Database system. The second table further includes an index for performing a direct search of a key field storing the primary key value by a primary key value for each of the two or more first tables.
The invalidation means searches for a data set corresponding to the update content indicated by the update notification using the index.
The database system according to claim 1 . The second table further includes a second flag for storing a value indicating whether or not a key field for storing a value indicating invalidity exists for each of the two or more key fields,
When the invalidation means updates the value of the two or more key fields to a value indicating invalidity, there exists a key field for storing a value indicating invalidity as the value of the second flag corresponding to the key field. Update to a value that indicates
The database system according to claim 2 . In the updating means, the value of the first flag corresponding to the data set of the second table necessary for responding to a data reference request involving the combination of the two or more first tables is a value indicating invalidity. In this case, an update request is acquired from the two or more first tables by issuing a data acquisition request including a narrowing condition using primary key values of the two or more key fields corresponding to the data set. 3. The database system according to 3 . The updating means includes a narrowing condition using a primary key value of the first table in a data reference request involving a combination of the two or more first tables, and stores the primary key value of the first table If the value of the second flag corresponding to the key field indicates that there is a key field storing a value indicating invalidity, the primary key of the first table used for the narrowing-down condition included in the data reference request The database system according to claim 4 , wherein a data acquisition request including a narrowing condition using a value is issued, and update data is acquired from the two or more first tables. The updating means stores a primary key value in the two or more key fields corresponding to the data set reflecting the acquired update data, and updates the value of the first flag to a value indicating validity, When there is no key field storing a value indicating invalidity due to storage of the primary key value, the value of the second flag corresponding to the key field is replaced with a value indicating that there is no key field storing a value indicating invalidity. The database system according to claim 5 , wherein the database system is updated. The notifying means sends the update notification when the update of data satisfying a narrowing condition for generating the second table from the two or more first tables is performed on the two or more first tables. 2. The database system according to claim 1 , which is transmitted to the management means. The notification means, when a plurality of update data on the same row of the same table has been made, to claim 1 for transmitting one update notification to the plurality of data updates to the second management means The database system described.

Images