Database management apparatus and database management method

Abstract

To keep the cardinality of an integer value after conversion large even when a data distribution is biased, and to prevent data whose values are largely different from being integrated into the same database segment.SOLUTION: For each column in the input data, the database management device constructs a hierarchical histogram of a data distribution related to the column by repeating division into a specified number of regions based on a degree and a radix suitable for a multidimensional sort algorithm and creation of a equal-width histogram as long as an empty bin exists in the histogram, and creates integer value conversion data for mapping a value range width of data in the input data and an integer value after conversion based on the hierarchical histogram. The database management device arranges data in the input data in a database by multi-dimensionally sorting the input data according to a multi-dimensional sort algorithm based on the integer value conversion data of each column.SELECTED DRAWING: Figure 4

Description

[Technical Field]

[0001] The present invention relates generally to data management in databases. [Background technology]

[0002] While the amount of data handled by database systems has been increasing year by year due to recent advances in digital transformation, further improvements in analytical query processing speeds are required to speed up decision-making. One approach to this issue is data placement optimization, which aggregates the data to be processed in analytical queries, i.e., data with similar values, into physically close locations. By aggregating the data required for query processing into a data set that serves as the unit of read from storage, the amount of data read from storage can be reduced, resulting in faster processing of analytical queries. In particular, optimization that takes into account the balancing of values ​​in multiple columns based on methods such as multidimensional sorting is effective in supporting a variety of analytical queries and workloads.

[0003] The database system disclosed in Patent Document 1 creates a depth-balanced histogram in which the range width of each bin is adjusted based on the value of each column so that the number of data assigned is as equal as possible, and converts each data into an integer value based on the bin ID to which it is assigned.The database system then optimizes data placement based on the converted integer values ​​and automatically updates the histogram according to changes in the distribution of data stored in the database. [Prior art documents] [Patent documents]

[0004] [Patent Document 1] US10114846 Summary of the Invention [Problem to be solved by the invention]

[0005] For convenience, the unit of data read from a database (data set) is called a "segment." One of the elements for making the most of a database system's performance is the division of data stored in the database. Specifically, for example, if the data to be processed by a query is aggregated in the same segment, the number of segments that can be omitted from reading increases, and as a result, improved read performance can be expected. Multidimensional sorting can be used to divide the data.

[0006] Multidimensional sorting generally only supports integer values. Therefore, to handle data other than integer values ​​(such as real numbers or strings), it is necessary to convert them to integer values ​​beforehand.

[0007] In the method of simply converting each value into an integer using only the most significant bits, if there is a bias in the input data, such as an outlier, the cardinality of the converted integer value becomes small, resulting in a coarse sorting granularity, i.e., sorting can only be done at a coarse level. Therefore, data with widely different values ​​ends up being aggregated in the same segment in the storage, resulting in an issue of an increase in the amount of data read from the storage.

[0008] On the other hand, in Patent Document 1, the range width of each bin is adjusted so that the number of data in each bin is equal. As a result, data with significantly different values ​​may be assigned to the same bin, that is, the same integer value may be assigned to data with significantly different values. Therefore, data with significantly different values ​​may be aggregated in nearby locations, resulting in an issue of an increase in the amount of data read from storage.

[0009] The object of the present invention is to maintain a large cardinality of the converted integer values ​​even if there is a bias in the data distribution, and to prevent data with significantly different values ​​from being aggregated in close locations within storage. [Means for solving the problem]

[0010] The database management device constructs a hierarchical histogram of the data distribution for each column in the input data by repeatedly dividing the input data into a prescribed number of regions based on the degree and a radix suitable for the multidimensional sorting algorithm and creating an equal-width histogram as long as there are empty bins in the histogram, and creates integer value conversion data that maps the range width of the data in the input data to integer values ​​after conversion based on this hierarchical histogram.The database management device arranges the data in the input data in a database by multidimensionally sorting the input data in accordance with the multidimensional sorting algorithm based on the integer value conversion data of each column. [Effects of the Invention]

[0011] According to the present invention, even if there is a bias in the data distribution, the cardinality of the converted integer values ​​can be kept large, and data with significantly different values ​​can be prevented from being aggregated into the same database segment. [Brief explanation of the drawings]

[0012] [Figure 1] Configuration diagram of a database system. [Figure 2] A diagram of the physical data structure within the storage. [Figure 3] Diagram of range index structure. [Figure 4] FIG. 1 is a detailed explanatory diagram of each element of a database system. [Figure 5] 10 is a flowchart showing the processing of an integer value conversion unit. [Figure 6] FIG. 10 is a diagram showing an example of the operation of an integer value conversion unit. DETAILED DESCRIPTION OF THE INVENTION

[0013] In the following description, an "interface apparatus" may refer to one or more interface devices. The one or more interface devices may be at least one of the following: An I / O interface device is one or more I / O (Input / Output) interface devices. The I / O (Input / Output) interface devices are interface devices 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 communications interface device. The at least one I / O device may be a user interface device, for example, either an input device such as a keyboard and a pointing device, or an output device such as a display device. A communication interface apparatus that is one or more communication interface devices. The one or more communication interface devices may be one or more homogeneous communication interface devices (e.g., one or more NICs (Network Interface Cards)) or two or more heterogeneous communication interface devices (e.g., an NIC and an HBA (Host Bus Adapter)).

[0014] In the following description, "memory" refers to one or more memory devices, which are an example of one or more storage devices, and may typically be a primary storage device. At least one memory device in the memory may be a volatile memory device or a non-volatile memory device.

[0015] In the following description, a "persistent storage device" may refer to one or more persistent storage devices, which are an example of one or more storage devices. A persistent storage device may typically be a non-volatile storage device (e.g., an auxiliary storage device), and more specifically, may be, for example, a hard disk drive (HDD), a solid state drive (SSD), a non-volatile memory express (NVME) drive, or a storage class memory (SCM).

[0016] In the following description, the term "storage device" may refer to at least one of memory and persistent storage device.

[0017] Furthermore, in the following description, a "processor" may refer to one or more processor devices. The at least one processor device may typically be a microprocessor device such as a CPU (Central Processing Unit), but may also be another type of processor device such as a GPU (Graphics Processing Unit). The at least one processor device may be single-core or multi-core. The at least one processor device may also be a processor core. The at least one processor device may also be a processor device in a broader sense, such as a circuit that is a collection of gate arrays written in a hardware description language that performs some or all of the processing (for example, an FPGA (Field-Programmable Gate Array), a CPLD (Complex Programmable Logic Device), or an ASIC (Application Specific Integrated Circuit)).

[0018] In the following description, functions are sometimes described using the expression "yyy unit." However, the functions may be realized by one or more computer programs executed by a processor, by one or more hardware circuits (e.g., FPGAs or ASICs), or by a combination thereof. When a function is realized by a program executed by a processor, the specified processing is performed using a storage device and / or an interface device, etc., as appropriate, and therefore the function may be considered to be at least a part of the processor. Processing described using a function as the subject may be processing performed by a processor or a device having the processor. A program may be installed from a program source. The program source may be, for example, a program distribution computer or a computer-readable storage medium (e.g., a non-transitory storage medium). The description of each function is merely an example; multiple functions may be combined into one function, or one function may be divided into multiple functions.

[0019] Hereinafter, one embodiment of the present invention will be described with reference to FIGS.

[0020] FIG. 1 is a configuration diagram of a database system according to an embodiment of the present invention.

[0021] In the figure, reference numeral 100 denotes a user device, 101 denotes a database management device, 102 denotes a memory, 103 denotes a processor, 104 denotes storage, 110 denotes a DBMS (DataBase Management System), 111 denotes a query reception unit, 112 denotes a preprocessing unit, 113 denotes a query execution unit, 120 denotes an integer value conversion unit, 121 denotes a multidimensional sorting unit, 130 denotes a data reading unit, 131 denotes a data writing unit, 140 denotes a database, 141 denotes a table, 142 denotes a range index, and 190 denotes an interface device. The table 141 and the range index 142 are components of one or more databases 140.

[0022] The database system includes a user device 100 and a database management device 101. The user device 100 may be a client, and the database management device 101 may be a server.

[0023] The user device 100 transmits a query including an instruction to write data to and / or read data from the DBMS 110 to the database management device 101. The user device 100 may be a physical computer or a logical computer (e.g., a virtual machine). The user device 100 is an example of a query source. The query source may be a program such as an application program executed on the user device 100 or the database management device 101.

[0024] The database management device 101 has an interface device 190, a memory 102, a processor 103, and a storage 104, which are connected to each other via a bus. An example of a storage device is at least the memory 102 out of the memory 102 and the storage 104. An example of a persistent storage device is the storage 104.

[0025] The interface device 190 communicates with the user device 100 via a communication network such as the Internet. Specifically, for example, a query is received through the interface device 190, and a response to the query (e.g., retrieved data) is sent to the user device 100 through the interface device 190.

[0026] The memory 102 is, for example, a dynamic random access memory (DRAM), and stores some of the data of the table 141 and range index 142 handled by the DBMS 110.

[0027] The processor 103 may be the central processing unit of the database management device 101 and executes the DBMS 110 .

[0028] The storage 104 may be, for example, an SSD (Solid State Drive) or an array of SSDs. The table 141 and the range index 142 are stored in the storage 104. The storage 104 may be an external storage of the database management device 101.

[0029] The DBMS 110 includes a query reception unit 111, a preprocessing unit 112, and a query execution unit 113. The DBMS 110 reads and writes data from and to a database 140 (a table 141 and a range index 142) based on a query provided by the user device 100.

[0030] The query receiving unit 111 receives a query from the user device 100, requests the query processing from the query executing unit 113, formats the results obtained from the query executing unit 113, and transmits the formatted results to the user device 100.

[0031] The preprocessing unit 112 has an integer value conversion unit 120 and a multidimensional sorting unit 121. The preprocessing unit 112 multidimensionally sorts input data for the database system based on values ​​of multiple columns.

[0032] The query execution unit 113 has a data reading unit 131 and a data writing unit 132. The query execution unit 113 reads and writes data from the memory 102 and the storage 104 based on the multidimensionally sorted data from the preprocessing unit 112 and the query processing request from the query reception unit 111.

[0033] As a preprocessing step for multidimensional sorting, the integer value conversion unit 120 creates a conversion table from the values ​​of each column to integer values ​​so that multidimensional sorting can be performed in a balanced manner across multiple columns even for input data with uneven distribution such as outliers.

[0034] The multidimensional sorting unit 121 performs multidimensional sorting of input data to the database system in a well-balanced manner across multiple columns, based on the conversion table to integer values ​​created by the integer value conversion unit 120 .

[0035] By multidimensionally sorting the input data provided by the user device 100 by the preprocessing unit 112 and then writing it to the memory 102 and storage 104 by the data writing unit 132, data with similar values ​​in multiple columns is more likely to be placed in physically close locations (e.g., the same segment).

[0036] Furthermore, before sorting by the multidimensional sorting unit 121, the integer value conversion unit 120 creates a conversion table from the values ​​of each column to integer values ​​while taking into account the bias in the distribution of input data. This makes it possible to apply multidimensional sorting to data other than integer values, and also suppresses the degradation of sorting performance due to bias in data distribution, such as outliers.

[0037] As a result, when a query provided by the user device 100 is executed by the query execution unit 113, unnecessary data reading by the data reading unit 131 is suppressed, thereby enabling the DBMS 110 to reduce the query response time.

[0038] FIG. 2 is a diagram showing the physical data structure in the storage 104.

[0039] The data of table 141 is distributed across one or more chunks 201. The data of chunk 201 is distributed across one or more segments 202. In addition, within a segment 202, some of the data of table 141 is aggregated and stored on a column-by-column basis, allowing data to be read efficiently from a specific column. A segment 202 is the reading unit.

[0040] FIG. 3 is a diagram showing the structure of the range index 142.

[0041] The range index 142 manages information on the range width of data contained in the chunks 201 and segments 202 on a column-by-column basis.

[0042] In the figure, 301 represents a range index for column A. For example, a range index 301 for column A exists for each chunk, and according to the range index 301 corresponding to chunk 2, the minimum value of chunk A is "301" and the maximum value is "600." Therefore, in chunk 2, only data in the range [301, 600] (i.e., the range from 301 to 600) exists as the value of column A. Therefore, in a data read process targeting a range that does not overlap this range width, the query execution unit 113 determines that there is no need to read the data of chunk 2 from storage 104, and can skip the data read.

[0043] That is, in such a range index 142, the smaller the value range width of each chunk 201, the higher the possibility of skipping reading of the chunk 201. Therefore, the more the preprocessing unit 112 can aggregate data with similar values ​​into physically closer locations, the more the query response time in the DBMS 110 can be shortened.

[0044] Figure 4 is a detailed explanatory diagram of each element of the database system. In the figure, the same components as those mentioned above are assigned the same numbers and their explanations are omitted.

[0045] In the figure, 401 is input data, 402 is sorted data, 403 is a query, 404 is a query plan, 405 is data, 406 is a query result, 411 is a sampling unit, 412 is sampling data, 421 is a histogram creation unit, 422 is a hierarchical histogram, 431 is an integer value conversion table creation unit, and 432 is an integer value conversion table.

[0046] The input data 401 is data given as input from the user device 100 to the DBMS 110. The input data 401 can be in any file format, including CSV (Comma Separated Values). The input data 401 may be a large amount of data input at regular intervals, such as every hour. The input data 401 may also be multidimensional data that includes data from multiple columns and has weak correlation between the columns.

[0047] The sorted data 402 is data resulting from multidimensional sorting of the input data 401 by the multidimensional sorting unit 121 while referring to the integer value conversion table 432 .

[0048] A query 403 is a query provided from the user device 100 to the DBMS 110. For example, an analytical query using range searches on various columns may be provided from the user device 100.

[0049] The query plan 404 is a query execution plan output as a result of interpretation and optimization of the query 403 in the query reception unit 111 , and is sent to the query execution unit 113 .

[0050] The data 405 is data that the data reading unit 130 reads from the storage 104 in order to execute the query plan 404 .

[0051] The query result 406 is the result of the query execution unit 113 executing the query plan 404 , and is sent to the user device 100 via the query reception unit 111 .

[0052] The sampling unit 411 is the first function to be processed in the integer value conversion unit 120. The sampling unit 411 outputs sampled data 412 by randomly sampling data from the input data 401. This reduces the processing cost of the histogram creation unit 421 and has the effect of improving the processing speed of the integer value conversion unit 120 as a whole. Here, the number of data to be sampled may be automatically calculated using, for example, Sturges's formula or a confidence interval formula that represents the ideal balance between the number of divisions for each column and the number of sampled data, or may be directly specified by the user based on the Service Level Agreement (SLA) for the application.

[0053] As described above, the sampling data 412 is data randomly sampled from the input data 401 by the sampling unit 411 .

[0054] The histogram creation unit 421 is a function that executes processing after the sampling unit 411. The histogram creation unit 421 creates a hierarchical histogram 422 by performing the following for each column of the sampling data 412. (Step 1) Create a fixed-width histogram for the region of interest. (Step 2) If there is an empty bin to which no data is assigned, the region is divided starting from the empty bin, and then the region with the largest range width is divided into two equal parts until the number of regions reaches the specified number. (Step 3) Repeat the above steps 1 and 2 recursively for each obtained region until it becomes indivisible.

[0055] However, the "prescribed number of regions" is set to an appropriate value depending on the algorithm used in the multidimensional sorting unit 121, such as the nth power of 2 in the case of Hilbert sorting. Recursively dividing into a number of regions appropriate for the multidimensional sorting algorithm increases the likelihood that data with similar values ​​will be assigned to the same region or the same bin, that is, data with similar values ​​as a result of sorting will be more likely to be placed in closer positions.

[0056] The hierarchical histogram 422 is a histogram created by the histogram creating unit 421 as described above.

[0057] The integer value conversion table creation unit 431 is a function that executes processing after the histogram creation unit 421. The integer value conversion table creation unit 431 creates an integer value conversion table 432 that maps the range width of the original data to be converted to integer values ​​after conversion, based on the hierarchical histogram 422. Because the histogram creation unit 421 creates the hierarchical histogram 422 based on the sampling data 412 rather than the entire input data 401, there may be a range width where no bin exists in the hierarchical histogram 422, which could result in some data in the input data 401 not being able to be converted to integer values. Therefore, the integer value conversion table creation unit 431 adjusts the range width of each bin so that all data in the input data 401 can be converted to integer values, and assigns integer values ​​to each bin such that data with similar values ​​during multidimensional sorting are more likely to be assigned to the same area.

[0058] As described above, the integer value conversion table 432 is a table created by the integer value conversion table creation unit 431, and is a table that maps the range width of the original data to be converted to integer values ​​after conversion.

[0059] The multidimensional sorting unit 121 sorts the input data 401 by referring to this integer value conversion table 432, thereby consolidating data with similar values ​​into closer positions. Then, the sorted data 402 is written to the memory 102 and storage 104 by the data writing unit 132, making it easier for data with similar values ​​to be arranged in the same segment 202. As a result, when a query provided by the user device 100 is executed by the query executing unit 113, unnecessary data reading by the data reading unit 131 can be suppressed, which has the effect of shortening the query response time of the DBMS 110.

[0060] FIG. 5 is a flowchart showing the processing of the integer value conversion unit 120.

[0061] First, in step S501, the sampling unit 411 receives the order d of each column i from the user device 100. i Column i receives the information of 2^(d i ) regions (i.e., 2 d i The order of each column i is d i This information may be associated with the input data 401.

[0062] In the following step S502, the sampling unit 411 sets an appropriate radix a according to the algorithm (multidimensional sorting algorithm) used in the multidimensional sorting unit 121. For example, in the case of Hilbert sorting, a=2.

[0063] In the following step S503, the sampling unit 411 outputs sampled data 412 by randomly sampling data from the input data 401. The sampled data 412 may include data randomly sampled for each column.

[0064] In the following step S504, the histogram creation unit 421 determines whether histograms have been created for all columns of the sampling data 412. If histograms have been created for all columns (S504: Yes), the process ends. If there are still columns for which histograms have not been created (S504: No), the histogram creation unit 421 designates one of those columns as a column of interest i, and the process proceeds to step S505.

[0065] In step S505, the histogram creation unit 421 performs preprocessing for creating a histogram for the region j of the currently focused column i by using the current order d i,j d i However, in the initial state, only region 0 exists, and its range width is equal to the range width of column i. i may be the same or different.

[0066] In the next step S506, the histogram creation unit 421 calculates the current order d i,j Determine whether the degree d of the entire region j is 0. i,j When the degree d becomes 0 (S506: Yes), the histogram creation unit 421 determines that creation of the histogram for the target column i is complete, and the process proceeds to step S515. i,j If there are any regions where j is not 0 (S506: No), the histogram creating unit 421 designates one of the regions as the region of interest j, and the process proceeds to step S507.

[0067] In step S507, the histogram creation unit 421 calculates the region of interest j as a^(d i,j ) to create an equal-width histogram. i,j )” is the d of a i,j Therefore, for example, a=2, d i,j If = 3, divide the region of interest j into 8 equal parts (2 3 =8) is created. The created equal-width histogram is a^(d i,j ) bins.

[0068] In the next step S508, the histogram creation unit 421 creates a histogram using the created a^(d i,j ) bins, it is determined whether there is any bin to which data has not been assigned (i.e., an empty bin). If there is even one empty bin (S508: Yes), further region division is required, and the process proceeds to step S509. If there are no empty bins (S508: No), further region division is not required, and the process proceeds to step S513.

[0069] In step S509, the histogram creation unit 421 creates a cluster area of ​​bins containing data, starting from an empty bin. A "cluster area" is a cluster of one or more consecutive bins containing data. In other words, the histogram creation unit 421 removes empty bins by dividing the histogram into multiple cluster areas. By removing empty bins in this way, it is possible to reduce integer values ​​that cannot be assigned to any data, that is, to maintain a high cardinality of integer values ​​after conversion. This has the effect of allowing for finer sorting granularity during multidimensional sorting (allowing sorting to take into account smaller value differences).

[0070] In the next step S510, the histogram creation unit 421 determines whether the number of regions (the number of cluster regions) obtained as a result of the division is a n Determine whether n is equal to 1≦n≦d. i,j Let the number of regions be any integer that satisfies a n If the number of regions is equal to a (S510: Yes), no further division is necessary, and the process proceeds to step S512. n If it is not equal to (S510: No), further region division is necessary, so the process proceeds to step S511. n By controlling the number of data to be arranged in units of 1, data with similar values ​​is more likely to be arranged in closer positions.

[0071] In step S511, histogram creation unit 421 divides the region with the largest width into two equal parts from among the set of partial regions obtained by dividing region of interest j. Then, the process proceeds to step S510.

[0072] In step S512, the histogram creation unit 421 calculates the order d of each partial region j' obtained by dividing the region of interest j. i,j´ is decreased by n. Then, the process proceeds to step S506.

[0073] In step S513, the histogram creation unit 421 sets the bin id to [0, a^(di,j )-1] range (i.e., 0 to a^(d i,j )-1). If this process is not performed, the integer value assigned to the data in column i will be [0,a^(d i,j -1)-1], and [a^(d i,j -1),a^(d i,j )-1] will no longer be used. i,j -1),a^(d i,j )-1] is assigned, the value of column i is less likely to be evaluated compared to other columns, resulting in a sort result that is biased towards a specific column. On the other hand, equalizing the bin ids in step S513 has the effect of allowing values ​​from multiple columns to be sorted in a balanced manner during multidimensional sorting. i,j -1)” is a (d i,j -1) power. For example, d i,j If is 3, then “a^(d i,j -1)” is the (3-1)th power of a, that is, a 2 means.

[0074] In the next step S514, the histogram creation unit 421 calculates the order d of the region of interest j. i,j is set to 0. Then, the process proceeds to step S506.

[0075] In step S515, the integer value conversion table creation unit 431 updates the bin IDs of the bins in the hierarchical histogram 422 for column i created by the previous processes to values ​​obtained by combining the IDs of each area and bin in a-ary notation in order from the highest hierarchical level and converting them to decimal numbers. This ensures that area IDs are evaluated in order from the highest hierarchical level during multidimensional sorting, which has the effect of preventing data with significantly different values ​​from being mixed together during sorting.

[0076] In the next step S516, the integer value conversion table creation unit 431 adjusts the range width of each bin so that there is no empty area in the hierarchical histogram for column i for which the bin id has been reset. Then, the process proceeds to step S504. This is a process for allocating data to the nearest bin in the hierarchical histogram 422 in cases where some data in the input data 401 may be allocated to an area where the bin was empty in the sampling data 412. Specifically, the process is as follows. First, the area of ​​bin k in the hierarchical histogram 422 is set to [min k ,max k ) (i.e., the range of bin k is min k more than max k less than max k and min k+1 If they do not match, the integer value conversion table creation unit 431 k and min k+1 Max k +(min k+1 -max k ) / 2. The integer value conversion table creation unit 431 also updates the minimum value of bin 0 (the bin with bin id=0) to −∞ and the minimum value of bin 2^(d i )-1(bin id=2^(d i )-1) is set to ∞. This makes it possible to uniquely assign integer values ​​to values ​​(data) in the input data 401 that do not exist in the sampling data 412.

[0077] The hierarchical histogram of column i obtained as a result of the processing in step S516 functions as an integer value conversion table 432 that maps the value range of column i to integer values ​​after conversion. This integer value conversion table 432 makes it possible to assign integer values ​​to data that take any value so that data that take similar values ​​during multidimensional sorting can be easily aggregated into the same segment.

[0078] FIG. 6 is a diagram showing an example of the operation of the integer value conversion unit 120. As shown in FIG.

[0079] As a premise, in step S501, the order d of each column i i It is assumed that radix a=3 is specified. Also, since the multidimensional sorting unit 121 uses Hilbert sorting, it is assumed that radix a=2 is set in step S502.

[0080] Then, in the following step S503, the sampling unit 411 randomly samples data from the input data 401, thereby obtaining sampled data 412 in which column 1 has data distribution 600. In the graph of data distribution 600, the horizontal axis corresponds to the value of column 1, and the vertical axis corresponds to the number of data points that take each value. In addition to the stationary value set 602 in the center of the graph, outlier sets 601 and 603 are present at both ends of the graph.

[0081] At this time, the histogram creation unit 421 has not yet created a histogram for column 1 (i=1), so it sets column 1 as the column of interest, and the process proceeds from step S504 to step S505. In step S505, the histogram creation unit 421 sets the order d 1,0 =3, region 0 is set as the region of interest (that is, j=0), and the process proceeds from step S506 to step S507.

[0082] Next, in step S508, the histogram creation unit 421 divides the area 0 into 2 3 A primary equal-width histogram 610 is created, dividing the range into 8 equal parts. Because an empty bin exists in the primary equal-width histogram 610, the process proceeds from step S508 to step S509. In step S509, the histogram creation unit 421 divides the primary equal-width histogram 610 into three regions, a [0,10) region 611, a [30,50) region 612, and a [70,80) region 613, starting from the empty bin. In other words, the histogram creation unit 421 defines one or more regions from the primary equal-width histogram 610 that has empty bins. For each of the one or more defined regions, the value range (value range width) of the region is equal to or greater than the minimum value and less than the maximum value of one or more consecutive bins containing data.

[0083] The number of regions obtained from the first-order equal-width histogram 610 is 3, which is 2. n Since n does not satisfy the condition 1≦n≦3, the process proceeds from step S510 to step S511. In step S511, histogram creation unit 421 divides region 612, which has the largest region width, in half. This results in two regions: region 614, which is in [30,40], and region 615, which is in [40,50]. The process proceeds again to step S510.

[0084] This makes the number of regions 4, 2 n (where n=2) is satisfied, the process proceeds from step S510 to step S512. In step S512, the histogram creation unit 421 calculates the order d 1,j´ d 1,0 -n=3-2=1 is set, and the process proceeds to step S506.

[0085] By similarly applying the processes from step S506 onwards to each of the regions 611, 614, 615, and 613, a secondary histogram 620 is completed. 1,j´ was set to 1, so each region is divided into 2 (=2 1 ) are equally divided. Here, since no empty bins are created in regions 614, 615, and 613, no further division is performed. However, in region 611, region 622 in [5,10) has become empty (i.e., an empty bin has been created), so the processes from step S506 onwards must be applied again to region 621 in [0,5). As a result, a cubic histogram 630 is generated. The cubic histogram 630 has region 631 in [0,2.5) and region 632 in [2.5,5). Since neither of these regions 631 and 632 is empty, no further division is performed.

[0086] Then, in step S515, integer value conversion table creation unit 431 sets bin ID 640 for hierarchical histogram 422 for column 1 obtained by the above process. For example, if we focus on region 623 (35, 40) in second-order fixed-width histogram 620, we get the following: This region of interest 623 belongs to second region 614 of four regions 611, 614, 615, and 613 in first-order fixed-width histogram 610, and this second region 614 has region ID = 01. Furthermore, since region of interest 623 is the second bin in region 614 in second-order fixed-width histogram 620, its bin ID = 1. Therefore, the bin ID for region of interest 623 is 011 in binary notation, or 3 in decimal notation. 6, the hierarchical histogram 422 (histograms 610, 620, and 630) has eight regions (regions 631, 632, 624, 623, 625, 626, 627, and 628, in order of decreasing min), and therefore the bin ids for these eight regions are, in decimal notation, 0 to 7. The bin id indicates the number of the bin in the hierarchical histogram 422, from the side with the smallest min.

[0087] Finally, in step S516, the integer value conversion table creation unit 431 adjusts the range width of each bin so that there are no empty bins in the hierarchical histogram 422 for column 1. For example, the original ranges of bin id=1 and 2 are [2.5, 5) and [30, 35), respectively, and [5, 30) is empty, so the integer value conversion table creation unit 431 updates max1=min2=5+(30-5) / 2=17.5. In other words, the range widths of the ranges (bins) of bin id=1 and 2 after adjustment become [2.5, 17.5) and [17.5, 35), respectively.

[0088] The hierarchical histogram 422 created by the above process functions as an integer value conversion table 432 for column 1, converting any data in column 1 into any integer value in [0, 7]. By similarly converting values ​​in other columns into integer values, any data can be uniquely mapped onto the converted grid 650. One axis of the grid 650 corresponds to the bin IDs 0 to 7 assigned to the bins of the hierarchical histogram 422 for column 1, and another axis of the grid 650 corresponds to the bin IDs assigned to the bins of the hierarchical histogram for another column. By performing multidimensional sorting based on the grid 650 (for example, by sorting the data along the Hilbert curve 651), the multidimensional sorting unit 121 can aggregate data with similar values ​​into nearby locations while taking into account the values ​​of multiple columns in a balanced manner. In particular, when creating a histogram for each layer, it is possible to divide the area into a n By dividing the data into pieces, data belonging to the same set among the data sets 601, 602, and 603 can be aggregated as much as possible, which has the effect of reducing the range width of the range index of each segment.

[0089] Although one embodiment has been described above, this is merely an example for explaining the present invention, and the scope of the present invention is not limited to this embodiment. The present invention can be implemented in various other forms.

[0090] The above description can be summarized, for example, as follows: The following summary may include supplementary explanations and explanations of variations of the above description.

[0091] A database management device (e.g., database management device 101) includes an integer value conversion unit (e.g., integer value conversion unit 120) and a multidimensional sorting unit (e.g., multidimensional sorting unit 121). For each column in input data (e.g., input data 401) having multiple columns, the integer value conversion unit constructs a hierarchical histogram (e.g., hierarchical histogram 422) of the data distribution for the column by repeatedly dividing the data into a predetermined number of regions based on the order and a radix appropriate for the multidimensional sorting algorithm and creating an equal-width histogram as long as an empty bin exists in the histogram. Based on the hierarchical histogram, the multidimensional sorting unit creates integer value conversion data (e.g., integer value conversion table 432) that maps the range width of the data in the input data to the converted integer values. The multidimensional sorting unit multidimensionally sorts the input data based on the integer value conversion data of each column in accordance with the multidimensional sorting algorithm, thereby arranging the data in the input data in a database (e.g., database 140) from which data is read in segments. This allows the cardinality of the converted integer values ​​to be kept large even if there is a bias in the data distribution, and prevents data with significantly different values ​​from being aggregated into the same database segment.

[0092] For each column, the converted integer value may be the bin ID of one of the bins in the hierarchical histogram for that column. The integer value conversion unit may assign bin IDs to the bins in the hierarchical histogram evenly within a range between a first integer value and a second integer value. The first integer value may be a predetermined integer value (e.g., 0). The second integer value may be based on a base and a given order of the column. This is expected to result in bin IDs that allow for balanced sorting across multiple columns. In other words, conversion to integer values ​​that balance the sorting granularity between the columns to be sorted is expected. For example, for each region, the first integer value may be 0, and the second integer value may be the base ^ (order of the region - 1).

[0093] For each column, steps (A) to (C) may be performed. An example of step (A) is step S507 in Fig. 5. An example of step (B) is step S508 in Fig. 5. An example of step (C) is step S509 to step S511 in Fig. 5. This is expected to reduce the number of empty bins as much as possible while keeping the range width of each segment small. (A) If there is a region j related to the column whose degree is 1 or greater, the integer value conversion unit creates an equal-width histogram for the region j by dividing the region j into X equal parts (where X is the base ^ the degree of the region j). (B) The integer value conversion unit determines whether there is an empty bin in the fixed-width histogram. If the determination results of (C) and (B) are true, the following (c1) and (c2) are performed. (c1) The integer value conversion unit divides the fixed-width histogram into a plurality of regions j' by excluding empty bins from the fixed-width histogram. Each of the plurality of regions j' is composed of one or more consecutive bins containing data. (c2) For each of the plurality of regions j', the integer value conversion unit reduces the degree of the region j' from the degree of the original region j of the region j', and then performs (A) with each region j' as region j.

[0094] In (c1), the integer value conversion unit may determine whether the number of regions j' is the nth power of the base (n is an integer greater than or equal to 1 and less than or equal to the degree of the original region j of region j'). An example of (c11) may be S510 in FIG. 5. If the number of regions j' is the nth power of the base, in (c2), the integer value conversion unit may reduce the degree of each of the multiple regions j' by n from the degree of the original region j of region j'. This allows the region division to end at an appropriate time, and it is expected that the range width of each segment can be appropriately kept small while reducing empty bins as much as possible.

[0095] If the number of regions j' is not the nth power of the radix, the integer value converter may divide the region j' with the largest width into two regions j' (for example, by performing S511 in FIG. 5) and then perform (c11). This makes it possible to keep the range width of each segment small while making the number of regions j' the nth power of the radix.

[0096] (D) may be performed for each column. An example of (D) is S513 and S514 in Fig. 5. This equalizes the bin IDs, allowing values ​​in multiple columns to be sorted in a balanced manner during multidimensional sorting. If the determination result of (D)(B) is false, the following (d1) and (d2) are performed. (d1) The integer value conversion unit assigns uniform integers in the range of 0 to the base ^(the degree of the region j-1) as bin IDs to the plurality of bins in the equal-width histogram. (d2) Change the degree of the region j to 0.

[0097] For each column, if the degrees of all regions j related to that column are 0 or less, the integer value conversion unit may update the bin ID of the bin in the hierarchical histogram of that column to a value obtained by combining, in a-ary notation, the bin IDs in the region including that bin, starting with the bin IDs of the higher hierarchical region that contains the bin, in order, and then converting the combined value to a decimal value (e.g., S515 in FIG. 5). This allows the region IDs of the higher hierarchical regions to be evaluated in order during multidimensional sorting, which is expected to prevent data with significantly different values ​​from being mixed together during sorting. For example, in a hierarchical histogram, the bin ID of the bin in the lowest hierarchical region may be determined as follows: The bin ID (in a-ary notation) in the region containing that bin is combined with the ID (in a-ary notation) of the region of the higher hierarchical region to which the bin belongs. The combined a-ary ID is converted to a decimal ID. The converted ID is the bin ID in the hierarchical histogram.

[0098] If there is an empty space in the hierarchical histogram, the integer value conversion unit may adjust the range width of each bin in the hierarchical histogram so that the empty space disappears (for example, S516 in FIG. 5). This allows a unique integer value to be assigned to each piece of data in the input data.

[0099] For each column, the integer value conversion unit may obtain sampled data (e.g., sampled data 412) by randomly sampling data from the input data. A hierarchical histogram of the data distribution for the column may be a histogram created from the sampled data. The number of randomly sampled data may be based on the order of the column. This allows the amount of calculation required for creating a histogram to be appropriately reduced. For example, the sampled data 412 may include data randomly sampled for each column. An order d is given as a parameter related to the number of divisions of the column, and a is assigned to the column. d If bins are created, the range of integer values ​​assigned to the column (the range of bin ids) is [0,a d -1] is sufficient. That is, the a obtained for the column d bins from 0 to a d Consecutive integer values ​​up to -1 may be assigned. k=1+log a m can be used. k can be the number of bins. m can be the number of samples (the number of data to be randomly extracted). a is a base suitable for the multidimensional sorting algorithm, and can be, for example, "2". m=a k-1 =a^(a d -1) is fine.

[0100] The database management device may have a query reception unit (e.g., query reception unit 111) and a query execution unit (e.g., query execution unit 113). The query reception unit 111 may receive a query (e.g., query 403), and the query execution unit may read from or write to the database in accordance with the query. For example, in data allocation in a database, data may be allocated to segments, and a value range for the segment may be described in a range index (e.g., range index 142). In executing a query, the query execution unit may identify segments in which the value specified in the query exists from the range index, and read only those segments in which the value specified in the query exists. In other words, the query execution unit may omit reading from segments in which the value specified in the query does not exist. In this way, improvement in data read performance is expected.

[0101] The multidimensional sorting unit may send a data write request (e.g., a data write query) to the query execution unit for data arrangement (arrangement of data in input data) according to the result of the multidimensional sorting. The query execution unit may perform data arrangement in the database according to the request according to the result of the multidimensional sorting.

[0102] As the multidimensional sorting, in addition to the Hilbert sorting, Z sorting (sorting according to Z order) may be adopted. The value of the base a may be a value according to the multidimensional sorting algorithm, such as 3 or another value.

[0103] Furthermore, one database management device does not necessarily have to include a query reception unit and a query execution unit in addition to an integer value conversion unit and a multidimensional sorting unit. That is, a database management device including an integer value conversion unit and a multidimensional sorting unit may be separate from a database management device including a query reception unit and a query execution unit. [Explanation of symbols]

[0104] 100: User device, 101: Database management device, 102: Memory, 103: Processor, 104: Storage, 110: DBMS, 111: Query reception unit, 112: Preprocessing unit, 113: Query execution unit, 120: Integer value conversion unit, 121: Multidimensional sorting unit, 130: Data reading unit, 131: Data reading unit, 141: Table, 142: Range index

Claims

1. It has an integer value conversion unit and a multidimensional sorting unit, The integer value conversion unit performs the following for each column in the input data having a plurality of columns: As long as there are empty bins in the histogram, the division into a predetermined number of regions based on the order and a base suitable for a multidimensional sorting algorithm and the creation of equal-width histograms are repeated to construct a hierarchical histogram of the data distribution for the column; generating integer value conversion data that maps a value range of the data in the input data to an integer value after conversion based on the hierarchical histogram; The multidimensional sorting unit multidimensionally sorting the input data according to the multidimensional sorting algorithm based on the integer value conversion data of each column, thereby arranging the data in the input data in a database from which data is read in segments; Database management device.

2. For each column, the converted integer value is a bin ID of any bin in the hierarchical histogram for the column; the integer value conversion unit assigns bin IDs of bins in the hierarchical histogram uniformly within a range of a first integer value or more and a second integer value or less; the first integer value is a predetermined integer value, the second integer value is based on the base and the given order of the column; The database management device according to claim 1 .

3. For each column, (A) when there is a region j with a degree of 1 or more as a region j related to the column, the integer value conversion unit creates an equal-width histogram for the region j by dividing the region j into X equal parts, where X is the base ^ the degree of the region j; (B) the integer value conversion unit determines whether there is an empty bin in the fixed-width histogram; (C) If the determination result of (B) is true, (c1) the integer value conversion unit divides the fixed-width histogram into a plurality of regions j' by excluding empty bins from the fixed-width histogram, and each of the plurality of regions j' is composed of one or more consecutive bins containing data; (c2) for each of the plurality of regions j', the integer value conversion unit reduces the degree of the region j' from the degree of the region j that is the original of the region j', and then performs (A) with each region j' as region j; The database management device according to claim 1 .

4. In (c1), the integer value conversion unit (c11) determining whether the number of regions j' is the nth power of the base (n is an integer greater than or equal to 1 and less than or equal to the degree of the original region j of the region j'); When the number of regions j' is the nth power of the base, in (c2), the integer value converter reduces the degree of each of the plurality of regions j' by n from the degree of the region j that is the original of the region j'.

4. The database management device according to claim 3.

5. If the number of regions j' is not an n-th power of the base, the integer value conversion unit further divides the region j' having the largest region width into two regions j', and performs (c11).

5. The database management device according to claim 4.

6. For each column, (D) If the determination result of (B) is false, the integer value conversion unit: (d1) assigning uniform integers in the range of 0 to the base ^(the degree of the region j−1) as bin IDs to the plurality of bins in the equal-width histogram; (d2) Change the degree of the region j to 0; 4. The database management device according to claim 3.

7. For each column, if the degrees of all regions j related to the column are 0 or less, the integer value conversion unit updates the bin IDs of the bins of the hierarchical histogram of the column to a value obtained by combining, in a-ary notation, the bin IDs within the region including the bin in order from the bin IDs of the higher layer that has the original region of the bin, and converting the combined value into a decimal number.

7. The database management device according to claim 6.

8. When there is an empty space in the hierarchical histogram, the integer value conversion unit adjusts the value range width of each bin in the hierarchical histogram so that the empty space disappears.

4. The database management device according to claim 3.

9. For each column, the integer value conversion unit obtains sampling data by randomly sampling data from the input data; a hierarchical histogram of the data distribution for the column is a histogram created from the sampled data; The number of data to be randomly sampled is based on the order of the column. The database management device according to claim 1 .

10. For each column in the input data having multiple columns, As long as there are empty bins in the histogram, the division into a predetermined number of regions based on the order and a base suitable for a multidimensional sorting algorithm and the creation of equal-width histograms are repeated to construct a hierarchical histogram of the data distribution for the column; generating integer value conversion data that maps a value range of the data in the input data to an integer value after conversion based on the hierarchical histogram; multidimensionally sorting the input data according to the multidimensional sorting algorithm based on the integer value conversion data of each column, thereby arranging the data in the input data in a database from which data is read in segments; A database management method that uses a computer.

Images