Database management device and database management method

Abstract

This reduces the processing load required for merging multiple B-tree indexes that correspond to multiple chunks of a database. [Solution] For each of the multiple chunks to be merged, the database management device identifies one or more pages in the B-tree index corresponding to that chunk where the difference between the maximum and minimum key values ​​is greater than or equal to a threshold. For each of the identified one or more pages, the database management device divides the page containing the maximum and minimum key values ​​into a page containing the maximum key value and a page containing the minimum key value. The multiple pages, including the divided pages, are sorted according to the magnitude of multiple key values ​​in multiple instances, and a single B-tree index containing the sorted pages is constructed as the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged.

Description

Technical Field

[0001] The present invention generally relates to database management.

Background Art

[0002] There are databases into which data is imported continuously or intermittently (e.g., every fixed period of time). Examples of such databases are those used in manufacturing, healthcare, and the like.

[0003] For each import of data, a logical data area called a "chunk" is created, and the data is imported into that area. When the index of the database is a B-tree index, a B-tree index is created for each chunk.

[0004] The performance of searches using a B-tree index deteriorates when the number of chunks is large. This is because when the number of chunks is large, the number of B-tree indexes that need to be searched is also large.

[0005] It is possible to reduce the B-tree indexes to be referenced by narrowing down the chunks using conditions related to searches or joins. However, since the number of checks for whether a chunk contains data that satisfies the conditions related to searches or joins is proportional to the number of chunks, the possibility of improving search performance is low.

[0006] Therefore, as a method for which an improvement in search performance is expected, chunk merging that combines a plurality of chunks into one is conceivable. Chunk merging includes index merging that combines a plurality of B-tree indexes into one.

[0007] Regarding the merging of B-tree indexes or partitioned B-trees, for example, the techniques disclosed in Patent Documents 1 to 3 are known. Also, regarding the merging of partitions of a database, for example, the techniques disclosed in Patent Documents 4 to 6 are known.

Prior Art Documents

Patent Documents

[0008] [Patent Document 1] US6694323 [Patent Document 2] US9262458 [Patent Document 3] US9298761 [Patent Document 4] US11238019 [Patent Document 5] US9489411 [Patent Document 6] US7987164 [Overview of the project] [Problems that the invention aims to solve]

[0009] Chunk merging includes index merging, which involves merging multiple B-tree indexes corresponding to multiple chunks to be merged. Executing index merging requires considerable time and resources.

[0010] For example, in manufacturing, a single chunk may contain the key values ​​of multiple products (e.g., data on the history of processing, etc.). This is because multiple products are manufactured within a certain period. The key values ​​of each of the multiple products manufactured within that period are stored in a single chunk. As time passes, the number of chunks increases; in other words, chunks are added at regular intervals. If all the key values ​​in each chunk to be merged need to be sorted for index merging, the process involves many operations such as recreating key values ​​and copying data, resulting in a heavy processing load.

[0011] Such challenges may also apply to other databases, either as replacements or in addition to those used in manufacturing. [Means for solving the problem]

[0012] A database management apparatus according to one aspect of the present invention identifies, for each of a plurality of chunks to be merged, one or more pages in a B-tree index corresponding to the chunk, where the difference between the maximum value and the minimum value of the key values is greater than or equal to a threshold. The database management apparatus divides, for each of the identified one or more pages, the page including the maximum value and the minimum value of the key values into a page including the maximum value of the key values and a page including the minimum value of the key values, arranges a plurality of pages including the divided pages for each of the one or more pages according to the magnitudes of the key values of a plurality of instances, and constructs one B-tree index including the arranged pages as the B-tree index after merging a plurality of B-tree indexes corresponding to the plurality of chunks to be merged.

Advantages of the Invention

[0013] According to the present invention, it is possible to reduce the processing load required for index merging of a plurality of B-tree indexes corresponding to a plurality of chunks of a database.

Brief Description of the Drawings

[0014] [Figure 1] Shows a configuration example of the entire system of a database management apparatus according to an embodiment. [Figure 2] Shows a configuration example of a database management apparatus. [Figure 3] Shows a configuration example of a database. [Figure 4] Schematically shows an overview of a leaf page of a B-tree index. [Figure 5] Schematically shows a part of an overview of chunk merging. [Figure 6] Schematically shows the rest of an overview of chunk merging. [Figure 7] Shows a configuration example of a DB element management table. [Figure 8] Shows a configuration example of an index detail table. [Figure 9] Shows a configuration example of a condition management table. [Figure 10] Shows an example of a flow of page unit merging. [Figure 11]Schematically shows an example of a situation or process in the page unit merge flow. [Figure 12] Shows the flow example of S14 in FIG. 10. [Figure 13] Shows the flow example of the subtree unit merge. [Figure 14] Schematically shows an example of a situation or process in the subtree unit merge flow. [Figure 15] Shows the flow example of S34 in FIG. 13. [Figure 16] Shows the flow example of S35 in FIG. 13. [Figure 17] Shows the flow example of the page unit merge according to a modification example. [Figure 18] Shows the flow example of the subtree unit merge according to a modification example.

Mode for Carrying Out the Invention

[0015] In the following description, the "interface device" may be one or more interface devices. The one or more interface devices may be at least one of the following. · One or more I / O (Input / Output) interface devices. The I / O (Input / Output) interface device is an interface device for at least one of an I / O device and a remote display computer. The I / O interface device for the display computer may be a communication interface device. At least one I / O device may be either an input device such as a user interface device, for example, a keyboard and a pointing device, or an output device such as a display device. · One or more communication interface devices. The one or more communication interface devices may be one or more of the same type of communication interface devices (for example, one or more NICs (Network Interface Cards)) or two or more different types of communication interface devices (for example, a NIC and an HBA (Host Bus Adapter)).

[0016] Furthermore, in the following explanation, "memory" refers to one or more memory devices, which are examples of one or more storage devices, and may typically be main memory devices. At least one memory device in memory may be a volatile memory device or a non-volatile memory device.

[0017] Furthermore, in the following explanation, "persistent storage device" may refer to one or more persistent storage devices, which are examples of one or more storage devices. Persistent storage devices are typically non-volatile storage devices (e.g., auxiliary storage devices), and specifically, may be, for example, HDDs (Hard Disk Drives), SSDs (Solid State Drives), or NVMe (Non-Volatile Memory Express) drives.

[0018] Furthermore, in the following explanation, "processor" may refer to one or more processor devices. At least one processor device may typically be a microprocessor device such as a CPU (Central Processing Unit), but may also be other types of processor devices such as a GPU (Graphics Processing Unit). At least one processor device may be single-core or multi-core. At least one processor device may be a processor core. At least one processor device may be a broad-sense processor device such as a hardware circuit that performs some or all of the processing (e.g., FPGA (Field-Programmable Gate Array), CPLD (Complex Programmable Logic Device), or ASIC (Application Specific Integrated Circuit)).

[0019] Furthermore, in the following explanation, functions may be described using the expression "yyy section," but a function may be realized by the execution of one or more computer programs by a processor, by one or more hardware circuits (e.g., FPGA or ASIC), or by a combination thereof. When a function is realized by the execution of a program by a processor, the defined processing is carried out using memory and / or interface devices as appropriate, so the function may be at least a part of the processor. Processing described with a function as the subject may be processing performed by the processor or a device having that processor. Programs may be installed from program source. Program source may be, for example, a program distribution computer or a storage medium that the computer can read (e.g., a non-temporary storage medium). The description of each function is an example, and multiple functions may be combined into one function, or one function may be divided into multiple functions.

[0020] Furthermore, in the following explanation, when describing similar elements without distinction, a common reference code will be used, and when describing similar elements with distinction, a reference code (or element identification number) will be used.

[0021] Figure 1 shows an example of the overall system configuration including the database management device 100 according to the embodiment.

[0022] The database management device 100 is a device that enables efficient chunk merging. The database management device 100 is connected to the user terminal 10 and the disk 110 via the network N, allowing for cooperation as needed. The database management device 100 may include at least the disk 110 in its system configuration, out of the user terminal 10 and the disk 110. The disk 110 may be an example of persistent storage, or an example of a device that includes persistent storage. The user terminal 10 may be a physical computer such as a personal computer, or a virtual computer based on a physical computer. The network N may be the Internet, a LAN (Local Area Network), a WAN (Wide Area Network), or a mobile phone network, etc.

[0023] The database management device 100 is a computer system configured on a single physical computer or on multiple logically or physically configured computers, and may operate on a virtual computer built on multiple physical computing resources. The database management device 100 may be configured on the cloud or on-premises on a specific computer (hardware).

[0024] The database is stored on disk 110. The database may be stored on a single disk 110 or it may span multiple disks 110.

[0025] The user terminal 10 may, for example, periodically send a request to the database management device 100 via the network N to import data that includes values ​​observed on a manufacturing line. The source of the data to be imported may be a device or program other than the user terminal 10. The source of the data to be imported may also be an application running inside or outside the database management device 100.

[0026] Figure 2 shows an example configuration of the database management device 100.

[0027] The database management device 100 has a configuration in which a CPU 101, memory 102, communication device 103, and I / O 104 are connected by a bus 105.

[0028] The communication device 103 and I / O 104 are examples of interface devices. The communication device 103 connects to the network N and communicates with the user terminal 10. The I / O 104 accesses external devices such as the disk 110 via the network N. Alternatively, the user terminal 10, connected to the database management device 100 via the network N and the communication device 103, may provide input and output devices (so-called user interface devices) via the I / O 104.

[0029] Memory 102 stores one or more computer programs. These programs are executed by the CPU 101 to realize the DBMS (Database Management System) 1021. The DBMS 1021 has functions such as a chunk merge unit 1022, a query reception unit 1023, and a query execution unit 1024.

[0030] The query reception unit 1023 receives queries to the database from the user terminal 10. Queries are written, for example, in SQL (Structured Query Language). The user terminal 10 is just one example of a query source (i.e., the source of data to be imported into the database).

[0031] The query execution unit 1024 may create a query plan necessary to execute a query based on the query received by the query reception unit 1023. The query plan may include, for example, information including one or more database operators and the relationship between the execution order of the database operators. The query plan may be represented, for example, as a tree structure with database operators as nodes and the relationship between the execution order of the database operators as edges. The query execution unit 1024 executes the query based on the created query plan and returns the execution result of the query to the user terminal 10. In query execution, the query execution unit 1024 can create a task to execute a database operator and, by executing the created task, issue a read request (or write request) for the data required by the database operator corresponding to that task. The query execution unit 1024 may execute multiple database operators in a single task. As for the implementation of the task, for example, processes or kernel threads implemented by an OS (Operating System) (not shown), or user threads implemented by libraries, etc., may be used. A query plan is not required for query execution. The query execution unit 1024, for each data import into the database, responds to the import query by storing the data to be imported in the chunk corresponding to the import and constructs a B-tree index corresponding to that chunk.

[0032] The chunk merge unit 1022 performs the chunk merge described later.

[0033] Figure 3 shows an example of the configuration of database 111.

[0034] Database 111 includes database tables (hereinafter referred to as DB tables) 1111 that span one or more chunks 1112, and B-tree indexes (hereinafter referred to as B-trees) 113 for each chunk 1112.

[0035] DB table 1111 is the table that stores the data to be imported. The data imported into DB table 1111 may hereafter be referred to as "actual data". The actual data is managed in association with pointers to leaf pages (so-called leaf nodes) of B tree 1113. Data is imported into database 111, for example, at regular intervals. The actual data may be so-called IoT (Internet of Things) data, or processed data such as IoT data. For example, the actual data may include values ​​that are frequently observed on a manufacturing line.

[0036] DBMS1021 (e.g., query execution unit 1024) generates a new chunk 1112 each time actual data is imported into the table 1111. For each chunk 1112, a B-tree 1113 is stored by DBMS1021 (e.g., query execution unit 1024). The B-tree 1113 consists of one or more pages, and the multiple pages include a root page, intermediate pages, and leaf pages. Each leaf page contains a key value and a pointer to the reference in the actual data. Each page in the B-tree 1113 may also be called a node. For the same instance (e.g., product), the key value may include a sequential number in addition to the instance ID. The sequential number may be a number that is incremented and assigned each time the data is observed. Also, the key value may be included in the actual data as well as the leaf pages. The key value may include the actual data.

[0037] The chunk merge unit 1022, in a chunk merge that merges multiple chunks 1112 into a single chunk 1112, refers to the DB element management table 112, the index detail table 113, and the condition management table 114. Details of these tables 112 to 114 will be described later.

[0038] Figure 4 schematically shows an overview of the leaf pages of a B-tree index according to this embodiment.

[0039] Figure 4 shows multiple leaf blocks in the B-tree 1113 for each of chunks 1 to N. A "block" consists of multiple pages, and a "leaf block" consists of multiple leaf pages. One leaf block corresponds to one or more instances. Each leaf page that makes up a leaf block stores one or more key values, such as "md06_act06_21A00". Therefore, as shown in Figure 4, for example, the observation timing for data stored in chunk 1 and the observation timing for data stored in chunk 2 are different, but if the instance (e.g., product) is the same, part of the key value, specifically the initial "md06_act06", is common. The rest of the key value, i.e., the sequential number, is different for the same instance. Multiple key values ​​are stored for each of the multiple chunks, from instance 1 to m.

[0040] Looking at it on a chunk-by-chunk basis, there is a large overlap in min-max values ​​(minimum and maximum key values) between chunks (between one chunk and the next). This is because each chunk contains both the minimum and maximum key values ​​for that chunk for each instance. Note that the number of leaf pages in each block is assumed to be the same across chunks.

[0041] On the other hand, when looking at B-tree 1113 at the subtree or page level, there are few subtrees or pages where the min-max values ​​overlap between chunks. Within chunks, only some subtrees (pages) have overlapping min-max values. Specifically, within chunks, only some subtrees (pages) contain a mix of the maximum value of a key value from one instance and the minimum value of a key value from another instance. For example, leaf page 401A1 in chunk 1 and leaf page 401A2 in the following chunk 2 each contain, with respect to that chunk, the maximum value of a key value from instance 1 (a key value containing "md06_act06") and the minimum value of a key value from instance 2 (a key value containing "md07_act07").

[0042] In the chunk merge according to this embodiment, a leaf page (or a subtree containing such a leaf page) containing the maximum value of one instance's key value and the minimum value of another instance's key value is identified as a portion that requires sorting and restructuring. The portions other than those identified do not require sorting and restructuring in the chunk merge and are used as is in the B-tree 1113 after the chunk merge.

[0043] Figures 5 and 6 schematically illustrate the overview of chunk merging in the embodiment.

[0044] Figure 5 shows B-tree 1113A of chunk 1 and B-tree 1113B of the following chunk 2.

[0045] Chunk merging may be performed, for example, in response to an instruction to merge multiple chunks. Such instruction may be sent from the user terminal 10, or from another device or program. The instruction may specify the IDs of each of the chunks to be merged. For example, chunk 1 and chunk 2 may be specified in the instruction.

[0046] Chunk merging may also be performed in response to the merging of DB tables. For example, if the merged DB table spans multiple chunks as a result of merging DB tables, these multiple chunks may be recognized as targets for merging, and multiple B-tree indexes corresponding to these chunks may be merged.

[0047] In tree B 1113A, the rightmost leaf page 401X of block 500V contains key values ​​from different instances, namely the key value "a40" from the first instance and the key value "b01" from the second instance. In multiple pages of block 500V other than the rightmost leaf page 401X, consecutive key values ​​such as "a01" to "a39" are stored for the first instance.

[0048] Similarly, in tree B1113B, the rightmost leaf page 401Y of block 500W contains both the key value "a80" of the first instance and the key value "b41" of the second instance. In multiple pages of block 500W other than the rightmost leaf page 401Y, consecutive key values ​​such as "a41" to "a80" are stored for the first instance. That is, for the first instance, chunk 2 contains the key value "a41" which is the next key value after the maximum key value "a40" stored in chunk 1, and subsequent key values.

[0049] The chunk merge unit 1022 identifies leaf pages 401X and 1113Y, which contain key values ​​of different instances, as targets for B-tree reconstruction. The chunk merge unit 1022 may also identify each of the parent pages (including the root page) of leaf page 401X in B-tree 1113A as targets for B-tree reconstruction. Similarly, the chunk merge unit 1022 may also identify each of the parent pages (including the root page) of leaf page 1113Y in B-tree 1113B as targets for B-tree reconstruction. This is because parent pages of leaf pages containing key values ​​of different instances also contain pointers to different instances, such as a pointer to the key value of the first instance and a pointer to the key value of the second instance. In Figure 5, the pages enclosed in thick borders are the pages identified as targets for B-tree reconstruction.

[0050] As shown in Figure 5, the chunk merge unit 1022 inserts the key values ​​"a41" to "a80" (i.e., key values ​​other than the key value "b41" of the second instance) from block 500W of tree B 1113B at the position between "a40" and "b01" stored in leaf page 401X of tree B 1113A.

[0051] During this insertion, the chunk merge unit 1022 sorts the subtrees to be merged based on their instances and key values, as shown in Figure 6. The chunk merge unit 1022 also generates or adds parent pages based on the key values ​​of each page in the subtrees targeted for reconstruction, and establishes mappings (links) with existing parent pages, thereby generating the merged B-tree 1113C of B-trees 1113A and 1113B. This generates the B-tree 1113C corresponding to the merged chunk.

[0052] Furthermore, inserting the key values ​​"a41" to "a80" in block 500W of tree B 1113B into the position between "a40" and "b01" stored in leaf page 401X of tree B 1113A can be done, for example, as follows. In other words, the insertion does not need to be an insertion in the strict sense, but rather an insertion in the substantive sense. - From leaf page 401X, which stores “a40” and “b01” in chunk 1, generate two leaf pages: one containing “a40” (key value of the first instance) and not containing “b01” (key value of the second instance), and another containing “b01” (key value of the second instance) and not containing “a40” (key value of the first instance). Specifically, for example, split leaf page 401X, which stores “a40” and “b01”, into a part containing “a40” and a part containing “b01”. Alternatively, generate a copy of leaf page 401X, delete “b01” from leaf page 401X, and delete “a40” from the copy of leaf page 401X. Similarly, from leaf page 401Y which stores "a80" and "b41", generate a leaf page that contains "a80" but does not contain "b41", and a leaf page that contains "b41" but does not contain "a80". - Re-establish links between leaf pages so that the key values ​​of multiple instances are consecutive in the merged chunk. Each leaf page has a link to the previous leaf page (e.g., a bidirectional link) and a link to the next leaf page. According to Figure 6, for example, the chunk merge unit 1022 sets the link to the next leaf page of the leaf page containing "a40" in chunk 1 as a link to the leaf page containing "a41" in chunk 2. Also, the chunk merge unit 1022 sets the link to the next leaf page of the leaf page containing "a80" in chunk 2 as a link to the leaf page containing "b01" in chunk 1. The link to the next leaf page of the leaf page containing "b01" is, as before the merge, a link to the leaf page containing "b02" in chunk 1. The chunk merge unit 1022 sets the link to the next leaf page of the leaf page containing "b40" (the maximum key value of the second instance in chunk 1) in chunk 1 as a link to the leaf page containing "b41" in chunk 2. The link from the leaf page containing "b41" to the next leaf page is, as before the merge, a link to the leaf page containing "b42" in chunk 2. As a result, in the merged chunk, the key values ​​of the first instance are listed consecutively from minimum to maximum, followed by the key values ​​of the second instance. In this way, the order of the key values ​​is maintained even in the merged chunk.

[0053] This section will explain the chunk merging method according to this embodiment in detail.

[0054] Figure 7 shows an example of the configuration of the DB element management table 112.

[0055] The DB element management table 112 has an entry for each chunk. Each entry represents the ID of the DB table to which the actual data stored in that chunk is imported, and the ID of the B-tree index corresponding to that chunk.

[0056] Figure 8 shows an example of the structure of the index detail table 113.

[0057] The index detail table 113 has an entry for each chunk. Each entry represents the ID of the B-tree index corresponding to the chunk, the ID of that chunk, and the ID of the root block of the B-tree index.

[0058] Figure 9 shows an example of the configuration of the condition management table 114.

[0059] The condition management table 114 has an entry for each process. Each entry represents the conditions under which that process is applied.

[0060] For example, if the process is pagination, one condition for the leaf pages to be divided is "the first n characters do not match." The value assigned to n may be a value that follows the structure of the key value. In this embodiment, "the first n characters do not match" corresponds to the condition that the difference between the maximum and minimum values ​​of the key value is greater than or equal to a threshold, as an example of key values ​​for different instances coexisting. However, from another perspective, this "the first n characters do not match" may also correspond to the condition that the specified range in the key value has a different structure, as an example of key values ​​for different instances coexisting.

[0061] Furthermore, for example, if the process is to determine whether chunk merging according to this embodiment is applicable, one of the conditions for chunk merging to be applied is that "the ratio of leaf pages that need to be split is less than m%." The value substituted for m may be a value that corresponds to the size of the B tree 1113 corresponding to the chunk. Also, "leaf pages that need to be split" are typically leaf pages in which key values ​​of different instances coexist. "The ratio of leaf pages that need to be split" is the ratio of the number of "leaf pages that need to be split" to the total number of leaf pages in the B tree 1113.

[0062] The following describes the chunk merge flow. In this implementation, either a "page-level merge," which follows the first method, or a "subtree-level merge," which follows the second method, can be adopted as the chunk merge.

[0063] First, let's explain page-level merging.

[0064] Figure 10 shows an example of a page-level merge flow. Figure 11 schematically shows an example of a situation or process in a page-level merge flow.

[0065] The chunk merge unit 1022 performs a merge of the data in DB table 1111 (S10). This process corresponds, for example, to the merge of multiple DB tables 1111 in database 111. The merge of these DB tables 1111 (for example, an existing DB table 1111 and a DB table 1111 composed of imported actual data) may trigger the merging of the B-tree indexes between the tables 1111. At this time, the chunk merge unit 1022 may store in the DB element management table 112 the ID of the table 1111 to be merged and the ID of the B-tree index of that table 1111. According to the example in Figure 7, for example, the merged DB table "T1" has two B-tree indexes "I11" and "I12".

[0066] The chunk merge unit 1022 refers to the DB element management table 112 and identifies all index IDs that are subject to chunk merge (S11). For example, index IDs "I11" and "I12" corresponding to the merged DB table "T1" are identified.

[0067] Next, the chunk merge unit 1022 repeatedly performs the following process for each of the index IDs identified in S11 (S12-S15).

[0068] The chunk merge unit 1022 searches the index detail table 113 using the index ID as the key and identifies the root block ID corresponding to that index ID (S13). Here, as illustrated in Figure 11, in S13 for the first index ID, the root block ID is identified for chunk 1, and in S13 for the second index ID, the root block ID is identified for chunk 2. Furthermore, it is assumed that the B trees of chunk 1 and chunk 2 are subject to page-level merging. Note that the B tree may have a balanced tree structure with the root block (root page) as the root node, and nodes (blocks or pages) branching downwards sequentially according to the B tree index construction rules, until it reaches the terminal leaf page (leaf node).

[0069] The chunk merge unit 1022 performs leaf page splitting and sorting on the B tree with the root block ID identified in S13 (S14). Details of this process will be described later with reference to Figure 12.

[0070] Next, the chunk merge unit 1022, in order to maintain the balanced tree structure in response to the loss of higher-level pages due to the splitting and sorting in S14, creates pages higher than the leaf pages in the necessary locations (S15). The chunk merge unit 1022 then performs a range index merge, that is, a merge of the subtrees created up to S15 (S16), and terminates this flow. An example of this S15 and S16 is schematically shown in "4. Merging into a single B-tree" in Figure 11. Note that the hierarchy depth of the B-tree index may be the same for all B-tree indexes. After the merge, it is sufficient that the depth from the root to the leaf is the same for all leaf pages.

[0071] Figure 12 shows an example of the flow of S14 (splitting and sorting of leaf pages) in Figure 10.

[0072] The chunk merge unit 1022 performs the following processing (S20~S26) for each leaf page of the B tree.

[0073] The chunk merge unit 1022 identifies the minimum and maximum key values ​​within the leaf page (S21). The chunk merge unit 1022 also stores the maximum key value within the leaf page in memory 102, for example (S22). S22 is necessary for creating the parent page in S28, which will be described later. Specifically, the chunk merge unit 1022 stores the pair of the maximum key value stored in S22 and a pointer to the leaf page in the parent page in S28, which will be described later.

[0074] Next, the chunk merge unit 1022 identifies the condition corresponding to the target "pagination" from the condition management table 114 (S23). In the example in Figure 9, the condition "first n characters mismatch" is identified. The condition corresponding to "pagination" is used to detect when key values ​​from different instances are mixed in leaf pages, and the condition depends on the structure of the key values. The condition "first n characters mismatch" is used, for example, when the first n characters of key values ​​are the same and they are key values ​​from the same instance.

[0075] The chunk merge unit 1022 determines whether the conditions identified in S23 are met based on the key values ​​identified in S21 and S22 (S24). For example, the chunk merge unit 1022 determines whether the first n characters of the maximum value identified in S21 and the first n characters of the minimum value do not match.

[0076] If the result of the S24 judgment is false (S24: NO), the chunk merge unit 1022 terminates the processing related to that leaf page (S20~S26).

[0077] On the other hand, if the result of the S24 check is true (S24:YES), the chunk merge unit 1022 divides the leaf page at locations where the difference between adjacent key values ​​is greater than or equal to a certain amount (S25). If, in a leaf page, the minimum value of the second instance follows the maximum value of the first instance, the "location where the difference between adjacent key values ​​is greater than or equal to a certain amount" is between that maximum and minimum value. Note that if a single leaf page contains key values ​​of N different instances (where N is an integer greater than or equal to 2), then there will be (N-1) locations in that leaf page where the minimum value of another instance follows the maximum value of one instance. Therefore, the (N-1) locations become the division points, and as a result, one leaf page is divided into N leaf pages.

[0078] Furthermore, the chunk merge unit 1022 updates the maximum key value within each of the new leaf pages created by the split in S25 (S26). In other words, for each new leaf page, the maximum key value becomes the maximum key value for the instance corresponding to that leaf page. Thus, although the maximum key value of a page changes when it is split, S26 is the process of updating the maximum key value.

[0079] After the above processes (S20-S26) are performed for each leaf page, the chunk merge unit 1022 sorts the leaf pages for the multiple chunks to be merged (for example, chunk 1 and chunk 2) using the maximum value of the leaf page's key value (S27). In this case, the chunk merge unit 1022 also rearranges the lateral links (links between leaf pages).

[0080] Furthermore, the chunk merge unit 1022 creates a higher-level page in accordance with the B-tree index construction rules, taking into account the relative sizes of key values ​​of other leaf pages, etc., in order to maintain a balanced tree structure, above leaf pages that have temporarily lost their higher-level pages due to routine page splitting (S28).

[0081] The above is an explanation of page-level merging. Note that "1. Page splitting and creation of leaf page list" as exemplified in Figure 11 corresponds to the identification of each leaf page in the flow shown in Figure 12. Also, "2. Identification of min-max values ​​of the leaf page list" as exemplified in Figure 11 corresponds to S21 and S26 in the flow shown in Figure 12. Furthermore, "3. Rearranging leaf pages" as exemplified in Figure 11 corresponds to S27 in the flow shown in Figure 12.

[0082] Next, we will explain subtree-level merging.

[0083] Figure 13 shows an example of a subtree-level merge flow. Figure 14 schematically shows an example of a situation or process in a subtree-level merge flow. Note that steps S30-S31, S32-S33, and S36 in this flow are the same as steps S10-S11, S12-S13, and S16 in Figure 10, so their explanation is omitted.

[0084] The chunk merge unit 1022 performs the following processes S33 to S35 for each B-tree index defined in the DB element management table 112. Of these, in S34, the chunk merge unit 1022 performs the calculation of the merge unit subtree based on the root block ID identified in S33 (S34).

[0085] Figure 13 shows an example of the S34 flow.

[0086] The chunk merge unit 1022 performs the following operations (S41-S45) (S40) on the upper-level pages of the B-tree index (each page above the leaf pages).

[0087] The chunk merge unit 1022 identifies the minimum and maximum key values ​​within the parent page to be processed (S41). The chunk merge unit 1022 identifies the conditions corresponding to the target "page splitting" from the condition management table 114 (S42). The chunk merge unit 1022 determines whether the key values ​​identified in S41 satisfy the conditions identified in S42 (S43).

[0088] If the result of the S43 judgment is false (S43: NO), the chunk merge unit 1022 finishes processing related to the parent page (S41-S45).

[0089] On the other hand, if the result of the judgment in S43 is true (S43:YES), the chunk merge unit 1022 determines whether the parent page of the parent page (hereinafter referred to as the target parent page) also satisfies the conditions identified in S42 (S44).

[0090] If the result of the S44 judgment is true (S44:YES), the chunk merge unit 1022 finishes processing related to the target higher-level page (S41~S45).

[0091] On the other hand, if the result of the judgment in S44 is false (S44: NO), the chunk merge unit 1022 stores the information of the target higher-level page in memory 102 (S45) and finishes processing related to the target higher-level page (S41~S45).

[0092] After processing each upper-level page (S40-S45), the chunk merge unit 1022 performs the following processing (S46-S54) for each page stored in memory 102 in S45.

[0093] First, the chunk merge unit 1022 determines whether the page stored in S45 (hereinafter referred to as the target page) is one level above the leaf page (S47). If the result of the determination in S47 is false (S47: NO), the chunk merge unit 1022 uses the subtree with the page pointed to by the target page as the merge unit subtree (S48).

[0094] If the result of the judgment in S47 is true (S47:YES), the chunk merge unit 1022 performs the following processing (S49~S54) for each of the one or more leaf pages one level below the target page.

[0095] The chunk merge unit 1022 identifies the minimum and maximum key values ​​within the leaf page (hereinafter referred to as the target leaf page) (S50). The chunk merge unit 1022 identifies the conditions corresponding to the target "page splitting" from the condition management table 114 (S51). The chunk merge unit 1022 determines whether the key values ​​identified in S50 satisfy the conditions identified in S51 (S52).

[0096] If the result of the S52 judgment is false (S52:NO), the chunk merge unit 1022 treats the target leaf page as a merge unit subtree (S54).

[0097] On the other hand, if the result of the S52 judgment is true (S52:YES), the chunk merge unit 1022 splits the target leaf page at locations where the difference between adjacent key values ​​is greater than a certain amount (S53). The chunk merge unit 1022 uses each leaf page obtained by the split as a merge unit subtree (S54).

[0098] When each of the processes in S47 to S54 described above has been executed for each page stored in S45, the chunk merge unit 1022 terminates this flow.

[0099] Once the calculation of the merge unit subtree described above (S34 in Figure 13) is complete, the chunk merge unit 1022 performs the merge of the B-tree indexes (S35 in Figure 13).

[0100] Figure 16 shows an example of the flow for S35 in Figure 13.

[0101] The chunk merge unit 1022 performs processing (S60~S61) for each merge unit subtree. That is, the chunk merge unit 1022 identifies the minimum and maximum values ​​of the subtree (S61).

[0102] Subsequently, the chunk merge unit 1022 determines the order of the subtrees (S62). The order of the subtrees follows the magnitude of the key values, based on the minimum and maximum values ​​identified in S61.

[0103] The chunk merge unit 1022 creates the necessary upper-level pages for the subtrees whose order was determined in S62, and points to the lower-level pages (S63).

[0104] Next, the chunk merge unit 1022 sets up horizontal links between subtrees (S64). The chunk merge unit 1022 also creates a parent page (S65) and terminates this flow.

[0105] The above is an explanation of subtree-level merging. Note that "1. Page splitting" illustrated in Figure 14 corresponds to S53 in the flow shown in Figure 15. Also, "2. Determining the min-max values ​​of subtrees" illustrated in Figure 14 corresponds to S61 in the flow shown in Figure 16. Furthermore, "3. Rearranging pages and subtrees" illustrated in Figure 14 corresponds to S62-S64 in the flow shown in Figure 16. Finally, "4. Merging into a single B-tree" illustrated in Figure 14 corresponds to S65 in the flow shown in Figure 16.

[0106] The above explains subtree-level merging.

[0107] The choice between page-level merging and subtree-level merging may be predetermined during the design phase of the chunk merging unit 1022. That is, a chunk merging unit 1022 that performs page-level merging and does not perform subtree-level merging may be designed, or a chunk merging unit 1022 that performs subtree-level merging and does not perform page-level merging may be designed.

[0108] Furthermore, the chunk merge unit 1022 may be designed to perform both page-level merges and subtree-level merges. The chunk merge unit 1022 may also dynamically decide which type of merge to perform. Regardless of whether the choice between page-level merges and subtree-level merges is predetermined or dynamically determined, it is preferable to perform page-level merges when there are many leaf pages to be divided, and subtree-level page merges when there are few leaf pages to be divided. Whether there are "many" or "few" leaf pages to be divided can be determined by whether the number of leaf pages to be divided is above or below a threshold. This threshold may be predetermined or dynamically determined by the chunk merge unit 1022 depending on the size of the database 111.

[0109] Furthermore, the page-level merge described with reference to Figures 10 to 12, and the subtree-level merge described with reference to Figures 13 to 16, are both examples of chunk merge according to this embodiment. However, as described below, modified page-level merges and modified subtree-level merges are also possible. In all of these modifications, the merging of the B-tree index includes a determination of whether to perform a fast B-tree merge (a merge where key-value sorting is required on some pages) or a normal B-tree merge (a merge where key-value sorting is required on all pages).

[0110] Figure 17 shows an example of a page-level merge flow for a modified version.

[0111] The chunk merge unit 1022 performs the process (S70~S75) for each leaf page of the B tree. Of the processes (S70~S75), S70~S74 are the same as the process shown in S20~S24 in Figure 12, so the explanation is omitted.

[0112] If the result of the determination in S74 is true (S74:YES), that is, if the leaf page satisfies the conditions corresponding to page splitting, the chunk merge unit 1022 stores the leaf page in memory 102, for example (S75).

[0113] After processing each leaf page (S70-S75), the chunk merge unit 1022 identifies the condition corresponding to the target "apply high-speed B-tree merge" from the condition management table 114 (S76). In the example shown in Figure 9, the condition "the percentage of leaf pages that need to be split is less than m%" is identified. Assume that m is set to an arbitrary value.

[0114] The chunk merge unit 1022 determines whether this condition is met (S77). In this case, the chunk merge unit 1022 calculates the ratio of the number of leaf pages stored in S75 to the total number of leaf pages in the B tree and determines whether this ratio is less than m%.

[0115] If the result of the S77 judgment is false (S77:NO), the chunk merge unit 1022 performs a normal B-tree merge without leaf page splitting, etc. (S78).

[0116] On the other hand, if the result of the judgment in S77 is true (S77:YES), the chunk merge unit 1022 performs the processing (S79~S81) for each leaf page stored in S75. However, since S80~S81 and the subsequent S82~S83 are the same as S25~S26 and S27~S28 shown in Figure 12, the explanation is omitted.

[0117] Figure 18 shows an example of a subtree-level merge flow related to a modified example.

[0118] Steps S90-S94, S97, and S99 in this flow are the same processes as S30-S34, S35, and S36 in the flow shown in Figure 13, so their explanation will be omitted.

[0119] The chunk merge unit 1022 identifies the condition corresponding to the target "Apply High-Speed ​​B-Tree Merge" from the condition management table 114 (S95). The chunk merge unit 1022 determines whether this condition is met (S96).

[0120] If the result of the S96 check is false (S96:NO), the chunk merge unit 1022 performs a normal B-tree merge (S98).

[0121] On the other hand, if the result of the judgment in S96 is true (S96:YES), the chunk merge unit 1022 performs a fast B-tree merge (S97).

[0122] The chunk merge unit 1022 completes processing for each B-tree index (S92-S98), and then performs range index merging (S99).

[0123] The above is a chunk merge method related to a modified version.

[0124] Although one embodiment and several modifications have been described above, these are merely illustrative examples for explaining the present invention and are not intended to limit the scope of the present invention to these embodiments or modifications. The present invention can also be carried out in various other forms.

[0125] Furthermore, the above explanation can be summarized as follows. The following summary may include supplementary explanations and explanations of variations of the above explanation.

[0126] The database management device (e.g., database management device 100) has a query execution unit (e.g., query execution unit 1024) and a chunk merge unit (e.g., chunk merge unit 1022). The query execution unit may be provided in a device outside the database management device.

[0127] The query execution unit, for each data import into the database, responds to the import query by storing the data to be imported in a chunk corresponding to the import (e.g., chunk 1112) and constructs a B-tree index corresponding to that chunk (e.g., B-tree 1113).

[0128] For each chunk, the B-tree index corresponding to that chunk is a tree-structured index with multiple pages as multiple nodes, and contains one or more key values, each of which has one or more values ​​for each of the multiple instances. For example, for each chunk, at least all leaf pages of the B-tree index contain one or more key values ​​for each of the multiple instances. For each leaf page, each of some or all of its parent pages may contain all the key values ​​(or a summary of the leaf pages) of the leaf pages directly or indirectly associated with that parent page. The "summary of the leaf pages" may be the minimum and maximum values ​​of all such key values. For example, each parent page may contain the maximum value of the key values ​​of each of its subordinate pages as a summary of that subordinate page. The subordinate page being referenced may be determined according to the relationship between the key value being searched and its maximum value.

[0129] The chunk merge unit identifies one or more pages in the B-tree index corresponding to each of the multiple chunks to be merged that satisfy the condition of containing key values ​​from different instances. For each of the identified one or more pages, the chunk merge unit splits it into a page containing the maximum value of key values ​​for one instance and a page containing the minimum value of key values ​​for another instance. The chunk merge unit then sorts the multiple pages, each containing the split pages, according to the magnitude of the multiple key values ​​from multiple instances. This sorting can be an actual rearrangement of the pages, for example, by rearranging the links between the pages. The chunk merge unit constructs a single B-tree index containing the sorted pages as the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged.

[0130] This minimizes the number of pages that need to recreate key values ​​and the need to move (copy) data, thereby reducing the processing load required for B-tree index merging.

[0131] For each instance, the key value corresponding to that instance may be larger the later the key value was obtained for that instance. For example, if the key value includes the instance ID, the ID itself may also be larger the later the key value was obtained for that instance. Specifically, for example, if there are multiple key values ​​for multiple different instances in each import, the key value sequence in that import may be such that consecutive key values ​​for one instance are followed by consecutive key values ​​for another instance.

[0132] The chunk merge section may perform the following: (11-a1) For each of the chunks to be merged, identify one or more leaf pages as pages, where the difference between the maximum and minimum key values ​​is greater than or equal to a threshold. (11-a2) For each of the one or more identified leaf pages, store the maximum value of the key value on that leaf page in memory, for example. (11-b) For each of the one or more identified leaf pages, if the leaf page satisfies the condition of containing key values ​​of different instances, it is divided into a leaf page containing the maximum value of key values ​​for one instance and a leaf page containing the minimum value of key values ​​for another instance, and the maximum value of key values ​​for each of the divided leaf pages is stored, for example, in memory. (12-a) Each of one or more leaf pages is sorted, including leaf pages that have been divided. For example, for each of the divided leaf pages, the link destination of that leaf page is set to the leaf page whose next key value after the maximum value of that leaf page is the smallest value. (12-b1) Create one or more higher-level pages for all leaf pages, including the rearranged leaf pages. (12-b2) Construct a single B-tree index that includes the rearranged leaf pages and the created parent pages, as the result of merging multiple B-tree indexes corresponding to multiple chunks to be merged.

[0133] In this way, the division position of the B-tree index can be determined on a leaf page basis, and merging into a single B-tree index can be performed. An example of a condition that includes key values ​​from different instances is that the difference between the maximum and minimum values ​​of the key values ​​is greater than or equal to a threshold. In this case, 1. and 2. shown in Figure 11 may correspond to (11-a1), (11-a2), and (11-b). 3. shown in Figure 11 may correspond to (12-a). 4. shown in Figure 11 may correspond to (12-b1) and (12-b2).

[0134] The chunk merge section may perform the following: (21-a) For each of the chunks to be merged, identify the top page that satisfies the condition of containing key values ​​from different instances. (21-b) If the parent page identified in (21-a) is one level above the leaf page, identify the leaf pages of that parent page that satisfy the condition of containing key values ​​of different instances, and divide the identified leaf page into a leaf page containing the maximum value of key values ​​for one instance and a leaf page containing the minimum value of key values ​​for another instance, and make the leaf page pointed to by the parent page identified in (21-a) and the divided leaf pages into merge unit subtrees. (21-c) If the identified parent page is not the parent page that is one level above the leaf page, the subtree with the page pointed to by that parent page as its vertex shall be the subtree of the merge unit. (22-a) For each subtree of the merge unit, identify the minimum and maximum values ​​of the key. (22-b) Sort multiple subtrees of the merge unit according to the size of the key value. (22-c) Create a top page according to the minimum and maximum values ​​for each subtree of the merge unit, and construct a single B-tree index that includes the rearranged subtree and the created top page as the merged B-tree index of the multiple B-tree indexes corresponding to the multiple chunks to be merged.

[0135] In this way, the division position of the B-tree index can be determined at the subtree level, and merging into a single B-tree index can be performed. An example of a condition that includes key values ​​from different instances is that the difference between the maximum and minimum values ​​of the key values ​​is greater than or equal to a threshold. In this case, 1. and 2. shown in Figure 14 may correspond to (21-a), (21-b), (21-c), and (22-a). 3. shown in Figure 14 may correspond to (22-b). 4. shown in Figure 14 may correspond to (22-c).

[0136] Sorting leaf pages can be done by updating the links between them. That is, for example, when sorting leaf pages or the subtree, the chunk merge unit can update the pointers (link destinations) set between leaf pages, thereby avoiding the physical movement of the leaf pages. This avoids the movement (copying) of data.

[0137] A page that satisfies the condition of containing key values ​​from different instances may be any of the following. Which condition is adopted may be determined by the structure of the key values. • Pages containing two or more key values ​​with different specified ranges. • Pages where the difference between the maximum and minimum values ​​of the key is greater than or equal to a threshold.

[0138] The range (for example, the value substituted for n in the example in Figure 9) or threshold in this condition may be a value set externally (for example, from the user terminal 10). For example, the chunk merge unit may receive instructions from a designated terminal regarding the threshold, and based on the threshold indicated by the received instructions, identify the splitting position of the leaf page or subtree and perform the split. This makes it possible to accurately determine the splitting position based on user-specified conditions that take into account, for example, the specific form of the data in the database to be chunk merged (e.g., the number of digits specific to the business operations that operate the DB, or the location of identification information). Note that the range or threshold in the condition corresponding to the target "page splitting" may differ for each hierarchy in the B-tree index, such as whether it is a leaf page or a higher-level page at any given level.

[0139] The chunk merge unit calculates the ratio of the number of leaf pages that satisfy the condition of containing key values ​​of different instances (for example, the number of leaf pages where the difference between the minimum and maximum key values ​​is greater than or equal to a threshold) to the total number of leaf pages in the B-tree index corresponding to that chunk, for each of the multiple chunks to be merged. If this ratio is less than the threshold, the unit may perform the processing including the splitting and sorting described above. This makes it possible to avoid performing the series of processes, including the splitting, sorting, and creation of parent pages of leaf pages, if the processing load required for this series of processes is likely to be equal to or greater than the processing load related to conventional B-tree index merging, thereby avoiding an increase in processing load. The threshold for this ratio may also be set externally.

[0140] For the same instance, the key value can typically be larger the later the time the value included in the key value was obtained for that instance. For different instances, if the time at which the value included in the key value was obtained is the same, the relationship between the key values ​​can be determined by the instance ID. Furthermore, instances can vary depending on the environment and industry in which the database is used. For example, if the database is used in manufacturing, instances could be products, materials, equipment, or workers. If the database is used in healthcare, instances could be subjects or devices worn by subjects. If the database is used in retail, instances could be orders or their details. [Explanation of Symbols]

[0141] 100: Database management unit, 101: CPU, 102: Memory, 1022: Chunk merging unit, 111: Database, 1112: Chunk, 1113: B-tree index

Claims

1. It comprises a query execution unit and a chunk merging unit, The query execution unit, for each data import into the database, responds to the import query by storing the data to be imported in the chunk corresponding to the import, and constructs a B-tree index corresponding to the chunk. For each chunk, the B-tree index corresponding to that chunk is a tree-structured index with multiple pages as multiple nodes, and each of the multiple instances contains one or more key values, which are one or more values. For each instance, the key value corresponding to that instance is larger the later the key value was obtained for that instance. The chunk merge unit is, (a) For each of the chunks to be merged, identify one or more pages in the B-tree index corresponding to that chunk that satisfy the condition of containing key values ​​of different instances, (b) For each of the one or more identified pages, divide the page into a page containing the maximum value of the key for one instance and a page containing the minimum value of the key for another instance. (c) Each of the one or more pages mentioned above is sorted according to the size of the key value, (d) Construct a single B-tree index that includes the rearranged pages as the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged. Database management device.

2. The chunk merge unit is, For each of the multiple chunks to be merged, one or more leaf pages are identified as the one or more pages in which the difference between the maximum and minimum values ​​of the key is greater than or equal to a threshold. For each of the one or more identified leaf pages, store the maximum value of the key value on that leaf page. For each of the one or more identified leaf pages, if the leaf page satisfies the condition of containing key values ​​from different instances, it is divided into a leaf page containing the maximum value of key values ​​for one instance and a leaf page containing the minimum value of key values ​​for another instance, and the maximum value of key values ​​is stored for each of the divided leaf pages. Each of the one or more leaf pages mentioned above rearranges the leaf pages, including the leaf pages that have been divided. For all leaf pages, including the rearranged leaf pages, create one or more higher-level pages. As the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged, a single B-tree index is constructed that includes the rearranged leaf pages and the created parent pages. The database management device according to claim 1.

3. The chunk merge unit is, For each of the multiple chunks to be merged, identify the top page that satisfies the condition of containing key values ​​of different instances. If the identified parent page is one level above a leaf page, identify the leaf pages of that parent page that satisfy the condition of containing key values ​​of different instances, divide the identified leaf page into a leaf page containing the maximum key value for one instance and a leaf page containing the minimum key value for another instance, and treat the leaf page pointed to by the parent page and the divided leaf pages as subtrees of merge units. If the identified parent page is not the parent page one level above the leaf page, the subtree with the page pointed to by that parent page as its vertex will be used as the merge unit subtree. For each subtree of a merge unit, identify the minimum and maximum values ​​of the key values. Sorting multiple subtrees of a merge unit according to the size of the key value, Based on the minimum and maximum values ​​for each subtree of the merge unit, a parent page is created. As the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged, a single B-tree index is constructed that includes the rearranged subtree and the created parent page. The database management device according to claim 1.

4. Sorting leaf pages involves updating the links between leaf pages. The database management device according to claim 1.

5. A page that satisfies the condition of containing key values ​​from different instances is one of the following: - Pages where there are two or more key values ​​with different specified ranges for the key values. Pages where the difference between the maximum and minimum values ​​of the key is greater than or equal to a threshold. The database management device according to claim 1.

6. The range or threshold is an externally set range or threshold. The database management device according to claim 5.

7. The chunk merge unit is, For each of the chunks to be merged, calculate the proportion of the number of leaf pages that satisfy the condition of containing key values ​​of different instances, relative to the total number of leaf pages in the B-tree index corresponding to that chunk. If this proportion is less than the threshold for this proportion, perform (b) to (d). The database management device according to claim 1.

8. The threshold for the aforementioned ratio is a value set externally. The database management device according to claim 7.

9. For each of the multiple chunks to be merged from among the multiple chunks of the database, identify one or more pages in the B-tree index corresponding to that chunk where the difference between the maximum and minimum key values ​​is greater than or equal to a threshold. For each data import into the database, the chunk corresponding to that import contains the data to be imported and a B-tree index corresponding to that chunk. For each chunk, the B-tree index corresponding to that chunk contains one or more key values, each of which has one or more values ​​for multiple instances. For each instance, the key value corresponding to that instance is larger the later the key value was obtained for that instance. For each of the one or more identified pages, the page containing the maximum and minimum values ​​of the key value is divided into a page containing the maximum value of the key value and a page containing the minimum value of the key value. The multiple pages, each of which contains a divided page, are sorted according to the magnitude of the multiple key values ​​of the multiple instances. As the B-tree index after merging the multiple B-tree indexes corresponding to the multiple chunks to be merged, a single B-tree index is constructed that includes the sorted pages. A database management method that is performed by a computer.

Images