Data processing optimization method, information processing device and program

Abstract

A data processing optimization method that includes a first operation to generate an integrated table by joining a first table and a second table having a common key, and projecting the values ​​of one or more predetermined projection target columns of records into the integrated table, includes determining whether the content of the first operation satisfies a first condition and a second condition, and if the content of the first operation satisfies the first condition and the second condition, replacing the first operation with the second operation, wherein the first condition is that each of the values ​​of the common key in the second table is unique, the second condition is that all of the projection target columns are included in the first table, and the second operation includes identifying the value of the common key of a record in the second table as a target value, and projecting the value of the projection target column of a record having the target value onto the common key column in the first table.

Description

Technical Field

[0001] The present disclosure relates to a method for optimizing data processing, an information processing apparatus, and a program.

Background Art

[0002] In a relational database, data of different entities are stored across different tables in order to normalize the data (data normalization is for memory for data storage, avoidance of abnormalities in data processing, etc.). Depending on the query, useful analysis may be performed using records stored in different tables. In such a case, the "JOIN" function (hereinafter simply referred to as JOIN) is used.

[0003] JOIN is an operation that is very frequently used, such as in relational data analysis and combining different input files in preparing learning data for machine learning algorithms. There are various types of JOIN. For example, in SQL, which is one of the languages for controlling a database, there are functions such as INNER-JOIN and OUTER-JOIN. In INNER-JOIN, only the keys found in both tables (i.e., common keys) are the targets of the join.

[0004] For example, in a relational database having a first table and a second table, there is data processing in which the value of a target column (column) of a record that is a record of the first table and whose value of a specific column included only in the second table when associated satisfies a predetermined extraction condition is projected (output). When performing such data processing, first, the first table and the second table are joined by INNER-JOIN to generate a joined table. Then, from the joined table, records whose values of the specific column satisfy the above-described extraction conditions are extracted respectively. Then, the values of the target columns of the extracted records are projected.

[0005] However, conventional JOIN operations are computationally expensive. To reduce computational costs, algorithms such as HASH-JOIN, which do not require prior sorting of each table, have been proposed (see Non-Patent Document 1). [Prior art documents] [Non-patent literature]

[0006] [Non-Patent Document 1] Zhou, J. (2009). Hash Join. In: LIU, L., OEZSU, MT (eds) "Encyclopedia of Database Systems". Springer, Boston, MA, [online] [searched on February 16, 2023], Internet<URL: https: / / doi.org / 10.1007 / 978-0-387-39940-9_869> [Overview of the project] [Problems that the invention aims to solve]

[0007] The following analysis was performed by the inventors of this disclosure.

[0008] Among JOIN operations, hash joins, which have relatively low computational costs, are limited to cases where an equality condition is specified in the join condition. Furthermore, even with HASH-JOIN, the computational cost of the join operation increases as the size of the tables being joined increases.

[0009] Furthermore, in data analysis, it is common to perform a JOIN operation on two or more tables, then extract (project) the necessary columns, and use them to perform useful analysis. In other words, JOIN and PROJECT are often used together in data analysis.

[0010] Therefore, there is a need to reduce the computational cost of operations equivalent to JOIN-PROJECTs, which involve joining two or more tables to extract (or project) the necessary data.

[0011] This invention has been made in view of the above circumstances, and aims to provide a technology that contributes to reducing computational costs in data processing (data analysis) that involves joining two or more tables and extracting (or projecting) necessary data. [Means for solving the problem]

[0012] According to the first perspective of this disclosure, A method for optimizing data processing, including the first operation, The first operation includes an operation to create an integrated table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more predetermined projection target columns of the records into the integrated table. The aforementioned optimization method is The computer determines whether the content of the first operation satisfies the first and second conditions. If the content of the first operation satisfies the first and second conditions, replace the first operation with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is identified as the target value, A data processing optimization method is provided, which includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table.

[0013] According to the second perspective of this disclosure, A query analysis unit analyzes the query to be processed and determines whether the first operation is included in the query to be processed, A query optimization unit that performs query optimization processing, An information processing apparatus comprising a query execution unit that executes the query to be processed after it has been optimized by the query optimization unit, The first operation includes an operation to create a combined table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more projection target columns of the records into the combined table. The query optimization process includes, when the query analysis unit determines that the query to be processed includes the first operation, determining whether the content of the first operation satisfies the first and second conditions, and if the content of the first operation satisfies the first and second conditions, replacing the first operation in the query to be processed with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is extracted as the target value, An information processing device is provided, which includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table.

[0014] According to the third perspective of this disclosure, A program that causes a computer to perform data processing optimization, which includes a first operation: creating an integrated table by joining a first table and a second table, each having a common key, and projecting the values ​​of one or more predetermined projection target columns of records into the integrated table, The steps include determining whether the content of the first operation satisfies the first and second conditions, When the content of the first operation satisfies the first condition and the second condition, replacing the first operation with a second operation, The first condition is that each value of the common key in the second table is unique, The second condition is that all the projection target columns are included in the first table, The second operation specifies the value of the common key of the record in the second table as the target value, and projects the values of the projection target columns of the record having the target value in the column of the common key in the first table. A program is provided.

[0015] These programs can be recorded on a computer-readable storage medium. The storage medium can be non-transient such as a semiconductor memory, a hard disk, a magnetic recording medium, an optical recording medium, etc. The present invention can also be embodied as a computer program product.

Effect of the Invention

[0016] According to the present invention, it is possible to contribute to reducing the calculation cost in data processing for combining two or more tables and extracting (or projecting) necessary data.

Brief Description of the Drawings

[0017] [Figure 1] (a) and (b) are explanatory diagrams for explaining a table of an embodiment of the present disclosure and an example of a problem statement. [Figure 2] (a) to (c) are explanatory diagrams for explaining operations of conventional data processing. [Figure 3] (a) to (c) are explanatory diagrams for explaining operations of data processing of an embodiment of the present disclosure. [Figure 4](a) and (b) are diagrams illustrating conditions under which the optimization process of one embodiment of the present disclosure cannot be applied. [Figure 5] (a) and (b) are a functional block diagram and a hardware configuration diagram of an information processing device according to one embodiment of the present disclosure. [Figure 6] This is a flowchart of an example of data analysis processing in one embodiment of the present disclosure. [Figure 7] This is a flowchart of an example of query optimization processing according to one embodiment of the present disclosure. [Figure 8] This is a flowchart of an example of the ISIN FILTER-PROJECT operation in one embodiment of the present disclosure. [Figure 9] (a) and (b) are diagrams showing the evaluation results. [Modes for carrying out the invention]

[0018] The following outline of one embodiment of this disclosure will be described with reference to the drawings. In the following outline, the reference numerals in the drawings are added for convenience to each element as an example to aid understanding, and are not intended to limit this disclosure to the illustrated embodiment. In addition, the connecting lines between blocks in the drawings and other references in the following description include both bidirectional and unidirectional lines. Unidirectional arrows schematically indicate the flow of the main signal (data) and do not exclude bidirectional flow.

[0019] Furthermore, although there are ports and interfaces at the input / output connection points of each block in the diagram, they are omitted from the illustration. Also, in the following explanation, "A and / or B" is used to mean either "A or B" or "A and B".

[0020] In this embodiment, we will explain using the example of a query whose computational cost is to be reduced, i.e., a query to be optimized, which is, for example, a JOIN-PROJECT in the SQL language. In the data analysis (data processing) of this embodiment, if the content of the JOIN-PROJECT operation satisfies predetermined conditions, the JOIN-PROJECT operation is replaced with an operation using the ISIN method and optimized. Hereinafter, the process of replacing the JOIN-PROJECT operation will be called the ISIN FILTER-PROJECT operation. Hereinafter, the JOIN-PROJECT operation will be simply called JOIN-PROJECT, and the ISIN FILTER-PROJECT operation will be simply called ISIN FILTER-PROJECT.

[0021] The tables used for data analysis are assumed to be contained within a relational database (RDB). That is, each table has a common key to establish relationships. Furthermore, from now on, table rows will be referred to as records, and table fields as columns.

[0022] First, let's explain JOIN-PROJECT with a concrete example using the relational database (RDB) 900 shown in Figure 1(a). Simply put, JOIN-PROJECT is a process that joins two tables using a common key and a projection (extraction) target column. For example, JOIN-PROJECT is an operation that joins the first table (LEFT table) and the second table (RIGHT table) based on a common key to generate a joined table (integrated table), and then projects the value of a specific column (projection target column) in the joined table.

[0023] This JOIN-PROJECT will be explained with a concrete example.

[0024] As shown in Figure 1(a), the relational database (RDB) 900 of this embodiment comprises a first table and a second table. The first table is a student table 100 that stores the attributes and grades of students from various countries of origin. The second table is a country table 200. The common key for both tables is the country code, which will be described later.

[0025] The student table 100 has columns for student identification information (S_id) 101, student name (S_name) 102, grades (S_marks) 103, and country code (C_id) 104. In other words, the student table 100 is a table in which the student name, grades, and country code of origin are registered for each student.

[0026] The country table 200 has columns for country code (C_id) 201 and country name (C_name) 202. In other words, the country table 200 is a table that stores the country name corresponding to the country code.

[0027] In a system with such an RDB900, suppose we are given the problem statement, for example, as shown in Figure 1(b), "Get names and marks for students from INDIA or JAPAN." This problem statement means projecting the student name (S_name) 102 and grades (S_marks) 103 of records for students (S_id) 101 whose country name (C_name) 202 is either India or Japan. The extraction condition is understood to be, "In records that contain the columns student name (S_name) 102, grades (S_marks) 103, and country name (C_name) 202, the value of country name (C_name) 202 must match either India or Japan."

[0028] When using JOIN-PROJECT (conventional query) for the above problem statement, the following query is generated, as shown in "CONVENTIONAL QUERIES" in Figure 1(b). dc1: Extracts records from Country Table 200 where the value of Country Name 202 is either India or Japan. (dc1 = FILTER Country where C_name = INDIA or C_name = JAPAN) Figure 2(a) shows the extracted country table 210 created by this query. dc2: Joins student table 100 and extracted country table 210 using a common key (country code (C_id) 104 or 201) to generate integrated table 300. (dc2 = INNER-JOIN (Student, dc1, on=C_id)) Figure 2(b) shows the generated integrated table 300. dc3: Projects the values ​​of student name (S_name) 102 and grade (S_marks) 103 onto each record in the integrated table 300. (dc3 = PROJECT [S_name, S_marks] from dc2) Figure 2(c) shows an example of the projection result 301.

[0029] In this embodiment, when such a problem statement is given, if predetermined conditions are met, the JOIN-PROJECT (the queries for dc2 and dc3 mentioned above) is replaced with an ISIN FILTER-PROJECT to optimize the process.

[0030] ISIN is a method that checks whether each record in a table has a specific value from a given list, and returns a Boolean value (True or False) for each record. In other words, ISIN returns True for records that have the specific value, and False for all other records.

[0031] In this embodiment, when given the problem statement "Retrieve the names and grades of students from India or Japan," and if certain conditions are met, the user creates the following queries, as shown in "OPTIMIZED QUERIES" in Figure 1(b). do1: Extract records from Country Table 200 where the value of Country Name 202 is either India or Japan. (do1=FILTER Country where C_name =INDIA or C_name = JAPAN) The extracted country table created by this query is the same as the extracted country table 210 shown in Figure 2(a). do2: For each record in student table 100, check (true / false) whether it has one of the values ​​(C_03 and C_04) included in extracted country table 210, and extract (filter) records for which the result is true. (do2=FILTER (Student) where Student.C_id ISIN (do1.C_id)) An example of result 110 is shown in Figure 3(a), and an example of result 111 is shown in Figure 3(b). Result 110 returns true if the value of Student.C_id 104 is C_03 or C_04, and false otherwise. This is because extracted country table 210 only contains C_03 and C_04 as values ​​for country code (C_id) 201. do3: Project the values ​​of student name (S_name) 102 and grade (S_marks) 103 onto each record in the extracted result 111. (do3=PROJECT [S_name, S_marks] from do2) Figure 3(c) shows an example of the projection result 112. Note that the projection result 112 shown in Figure 3(c) is the same as the projection result 301 obtained by the JOIN-PROJECT shown in Figure 2(c).

[0032] Furthermore, in order to optimize the JOIN-PROJECT, the table and extraction conditions must satisfy the specified conditions, as described above. These specified conditions are the first and second conditions.

[0033] The first condition is that the values ​​of the common key column in the second table are unique. In the example above, this means that there are no duplicate values ​​for the common key (country code (C_id) 201) in the extracted country table 210.

[0034] For example, country table 200 shown in Figure 1(a) satisfies the first condition. On the other hand, country table 200a shown in Figure 4(a) does not satisfy the first condition. In country table 200a, the same value is assigned to the country code (C_id) 201 for each region. As shown in Figure 4(a), in country table 200a, C_01 is assigned to CANADA and USA, and C_04 is assigned to JAPAN and KOREA. In this case, the value of the common key (country code (C_id) 201) does not have a one-to-one correspondence with the country name (C_name) 202. Therefore, truth determination cannot be made using the value of the common key. If C_04 is determined to be true, not only the records of students from Japan but also the records of students from Korea will be determined to be true.

[0035] The second condition is that all columns to be projected are included in the first table. Note that the columns to be projected in this second condition are non-key columns (columns other than common keys). In the example query created from the above problem statement, the columns to be projected are student name (S_name) 102 and grade (S_marks) 103, both of which are included in student table 100.

[0036] On the other hand, for example, if the problem statement is "Get names and marks with country name for students from INDIA or JAPAN," the columns to be projected are student name (S_name) 102, grades (S_marks) 103, and country name 202. Therefore, in this case, the second condition is not met. In ISIN FLITER-PROJECT, only the first table (student table 100 in the above example) is used for truth determination, and the second table (extracted country table 210) itself is not used in the determination process. That is, as shown in Figure 4(b), the determination result 110 (and extraction result 111) does not include information (values) from the columns of the second table (extracted country table 210). Therefore, information from other tables is not projected.

[0037] The following describes the information processing device 500 of this embodiment that realizes the data analysis described above.

[0038] [Functional Block Diagram] Figure 5(a) is a diagram showing an example of the functional configuration of the information processing device 500 of this embodiment. As shown in Figure 5(a), the information processing device 500 of this embodiment includes a query analysis unit 510, a query optimization unit 520, a query execution unit 530, and an RDB storage unit 540.

[0039] The RDB storage unit 540 stores each table included in the RDB 900.

[0040] The query analysis unit 510 loads the tables necessary for processing from the tables stored in the RDB storage unit 540 into the work area, and analyzes the program that codes the problem statement (the set of queries generated from the problem statement) to determine whether it contains a JOIN-PROJECT. The query analysis unit 510 notifies the query optimization unit 520 of the determination result.

[0041] The query optimization unit 520 performs query optimization.

[0042] When the query optimization unit 520 receives a determination result indicating that a JOIN-PROJECT is included in the query, the query optimization unit 520 analyzes the processing content of the JOIN-PROJECT and determines whether it can be replaced by an ISIN-FILTER-PROJECT. Here, the query optimization unit 520 determines whether the processing content of the JOIN-PROJECT satisfies the first and second conditions described above. If the query optimization unit 520 determines that the processing content of the JOIN-PROJECT satisfies both the first and second conditions, that is, if the common key values ​​of all records in the second table (extracted country table 210) are unique and all projection target columns are included in the first table, it determines that it can be replaced.

[0043] Then, if the query optimization unit 520 determines that a replacement is possible, it replaces the JOIN-PROJECT in the query with an ISIN FILTER-PROJECT. If the query optimization unit 520 does not determine that a replacement is possible, or if the query optimization unit 520 receives a determination from the query analysis unit 510 that the JOIN-PROJECT is not included, no replacement is performed.

[0044] The query execution unit 530 executes the query after query optimization and projects the results.

[0045] [Data Analysis Processing] The data analysis process flow of the information processing device 500 of this embodiment will now be described. Figure 6 is a flowchart of an example of the data analysis process performed by the information processing device 500 of this embodiment. Here, it is assumed that the query (program code) is generated in advance from the given problem statement, and the necessary databases are loaded.

[0046] The query analysis unit 510 analyzes the query (step S1101). Here, the query analysis unit 510 determines whether or not the query contains a JOIN-PROJECT and notifies the query optimization unit 520 of the determination result.

[0047] The query optimization unit 520 performs query optimization (step S1102). Details of the query optimization process will be described later.

[0048] Then, the query execution unit 530 executes the query (step S1103), projects the results (step S1104), and terminates the process.

[0049] [Query optimization process] The query optimization process performed by the query optimization unit 520 when it receives a determination result from the query analysis unit 510 indicating that a JOIN-PROJECT is included will be explained with reference to Figure 7. The query to be optimized is the "JOIN-PROJECT" listed as "CONVENTIONAL QUERIES" in Figure 1(b). The first table is the student table 100 shown in Figure 1(a), and the second table is the extracted country table 210 shown in Figure 2(a).

[0050] The query optimization unit 520 first determines whether the "JOIN-PROJECT" detected by the query analysis unit 510 satisfies the first condition.

[0051] The query optimization unit 520 identifies the common key of the detected JOIN-PROJECT (step S1201). Here, the query optimization unit 520 identifies it by referring to the description of the common key of the INNER-JOIN query. That is, it is C_id in "dc2 = INNER-JOIN (Student, dc1, on=C_id)".

[0052] Then, the query optimization unit 520 determines whether each value of the common key in the second table (extracted country table 210) is unique (step S1202).

[0053] Next, if each value of the common key is unique (S1202; Yes), the query optimization unit 520 determines whether the detected JOIN-PROJECT satisfies the second condition.

[0054] The query optimization unit 520 identifies the columns to be projected in the detected JOIN-PROJECT (step S1203). Here, the query optimization unit 520 refers to and identifies the columns to be projected in the PROJECT query. Specifically, these are S_name and S_marks in "dc3 = PROJECT [S_name, S_marks] from dc2".

[0055] Then, the query optimization unit 520 determines whether all the columns to be projected are included in the first table (step S1204).

[0056] If all columns to be projected are included in the first table (S1204; Yes), the query optimization unit 520 replaces the JOIN-PROJECT in the query with an ISIN FILTER-PROJECT (step S1205) and terminates the optimization process. However, if in step S1202 the values ​​of the common key in the second table are not unique (S1202; No), or if in step S1204 all columns to be projected are not included in the first table (S1204; No), the query optimization unit 520 does not replace the JOIN-PROJECT and terminates the process. Furthermore, the process in step S1204 is executed if the number of columns to be projected is two or more. If the number of columns to be projected is one, the process in step S1204 is not executed, and the process proceeds to step S1205.

[0057] [ISIN FILTER-PROJECT Operation] Here, we will briefly explain the flow of the ISIN FILTER-PROJECT operation executed by the query execution unit 530. Figure 8 is a flowchart of an example of the ISIN FILTER-PROJECT operation. The following is the processing flow when executing the following set of queries created in response to the problem statement, "Retrieve the names and grades of students from India or Japan." ·do1= FILTER Country where C_name =INDIA or C_name = JAPAN ·do2= FILTER (Student) where Student.C_id ISIN (do1.C_id) ·do3= PROJECT [S_name, S_marks] from do2

[0058] First, the query execution unit 530 extracts records that meet the extraction criteria from the country table 200 and generates the extracted country table 210 shown in Figure 2(a). That is, the query execution unit 530 extracts records where the value of country name (C_name) 202 is either India or Japan (step S1301).

[0059] Next, the query execution unit 530 extracts the value of the common key (C_id) from the extracted country table 210 as the target value (step S1302).

[0060] The query execution unit 530 determines whether the Student.C_id column 104 contains the target value for each record in the student table 100 (step S1303). Here, if the record contains the target value, the query execution unit 530 marks it as True, and otherwise marks it as False, obtaining the determination result 110 shown in Figure 3(a).

[0061] Then, the query execution unit 530 extracts the records marked as True (step S1304) and obtains the extraction result 111 shown in Figure 3(b).

[0062] The query execution unit 530 projects the values ​​of the target column from the extraction result 111 (step S1305), obtains the projection result 112 shown in Figure 3(c), and terminates the process.

[0063] [Hardware configuration] Furthermore, the information processing device 500 of this embodiment can be implemented using, for example, a so-called general-purpose information processing device (computer), the configuration of which is illustrated in Figure 5(b).

[0064] As shown in Figure 5(b), the information processing device 500 of this embodiment includes, for example, a CPU (Central Processing Unit) 591, a main memory 592, an auxiliary memory 593, a communication I / F 594, and an expansion I / F 595, all interconnected by an internal bus.

[0065] The CPU 591, for example, loads a program stored in the auxiliary storage device 593 into the main memory device 592 and executes it, thereby realizing each of the above functions and comprehensively controlling the entire information processing device 500. Alternatively, one or more processors, such as an MPU (Micro Processing Unit), may be used instead of the CPU 591.

[0066] The main memory 592 is a type of memory such as RAM (Random Access Memory). The main memory 592 is the work area where the CPU 591 processes programs and other data executed by the information processing device 500.

[0067] The auxiliary storage device 593 stores various programs executed by the information processing device 500. In this embodiment, the auxiliary storage device 593 also stores each table included in the RDB 900 (student table 100 and country table 200).

[0068] Each of the above functions of the information processing device 500 in this embodiment is realized by the CPU 591 loading a program stored in the auxiliary storage device 593 into the main storage device 592 and executing it. Data generated during processing is stored in the main storage device 592 or the auxiliary storage device 593.

[0069] The auxiliary storage device 593 may include, for example, a ROM (Read Only Memory), an HDD (Hard Disk Drive), or an SSD (Solid State Drive). The auxiliary storage device 593 may also include storage media such as a flexible disk, a hard disk, an optical disk, a CD-ROM, a CD-R, a magnetic tape, a non-volatile memory card, or a DVD.

[0070] Furthermore, programs stored in the auxiliary storage device 593 can be provided as program products recorded on a non-transitory computer-readable storage medium. The auxiliary storage device 593 can be used to store various programs recorded on non-transitory computer-readable storage media for medium to long term.

[0071] The communication interface 594 transmits and receives signals and data via wired or wireless means. The information processing device 500 may include, for example, a NIC (Network Interface Card) as the communication interface 594.

[0072] The extended I / F595 is an interface for connecting output devices such as display devices and input devices such as input devices. The display device is, for example, an LCD monitor. The input device is, for example, a device that accepts user input such as a keyboard or mouse. In this embodiment, the input query is received, for example, via the input device, and the processing result is displayed on the display device.

[0073] The hardware configuration of the information processing device 500 is not limited to this. The information processing device 500 may include hardware not shown in Figure 5(b).

[0074] Furthermore, the programs that realize each function of the information processing device 500 in this embodiment can be recorded on a computer-readable storage medium. The storage medium can be a non-transient material such as semiconductor memory, hard disk, magnetic recording medium, or optical recording medium. The present invention can also be embodied as a computer program product.

[0075] As described above, in the process of joining two tables and extracting (filtering, extracting) the necessary data, the information processing device 500 of this embodiment replaces the normally used JOIN-PROJECT with an ISIN FILTER-PROJECT when predetermined conditions are met. The computational cost of the ISIN FILTER-PROJECT to be replaced is smaller than that of the JOIN-PROJECT. The replacement is performed when the contents of the JOIN-PROJECT satisfy the first and second conditions. The first condition is that each value of the common key in the second table is unique. The second condition is that all projection target columns are columns in the first table.

[0076] As mentioned above, ISIN only performs a few if checks on the target value of each record in the first table. Therefore, ISIN is much lighter than a HASH-JOIN operation. For this reason, the ISIN FILTER-PROJECT will be much faster than the JOIN-PROJECT. According to this embodiment, it can contribute to reducing computational costs in data processing that involves joining two or more tables and projecting the necessary data.

[0077] <Example 1> In the above embodiments and modifications, each table is stored in the information processing device 500, but is not limited thereto. For example, each table may be stored in network storage such as NAS (Network Attached Storage) or SAN (Storage Area Network). Each table may also be stored in a different storage device.

[0078] <Modification 2> In the above embodiments and modifications, each table was described as being included in an RDB, but this is not the only way to go.

[0079] In the flowcharts used in the above description, multiple steps (processes) are shown in order, but the execution order of each step in each embodiment and modified example is not limited to the order in which they are shown. In each embodiment and modified example, the order of the illustrated steps can be changed to the extent that it does not impede the content, for example, by executing each process in parallel.

[0080] [Experimental evaluation] The evaluation results for the JOIN-PROJECT and ISIN FILTER-PROJECT are shown in Figures 9(a) and 9(b). For the evaluation, the lineitem(size:59986052.), supplier(size:100000), and nation(size:25) tables from the TPC-H benchmark were used with a Scale Factor of 10. TPC-H is a decision support benchmark consisting of a set of business-oriented ad-hoc queries with concurrent data modification. As shown in Figures 9(a) and 9(b), the ISIN FILTER-PROJECT is up to 9 times faster and on average about 3.5 times faster than the JOIN-PROJECT.

[0081] Some or all of the above embodiments may also be described as follows, but are not limited to the following: (Note 1) The optimization method for data processing, including the first operation, is: The first operation includes an operation to create an integrated table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more predetermined projection target columns of the records into the integrated table. The aforementioned optimization method is The computer determines whether the content of the first operation satisfies the first and second conditions. If the content of the first operation satisfies the first and second conditions, replace the first operation with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is identified as the target value, This includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table. (Note 2) The data processing optimization method described in Appendix 1 is: In the second operation, the value of the projection target column of the record in the first table whose common key value is the target value is projected. (Note 3) The data processing optimization methods described in Appendix 1 or 2 are: The computer executes the first operation if at least one of the first and second conditions is not met. (Note 4) The data processing optimization method described in any of the appendices 1 to 3 is: The second operation mentioned above is the ISIN FILTER-PROJECT operation. (Note 5) The data processing optimization method described in any of the appendices 1 to 4 is: The aforementioned first operation is a JOIN-PROJECT operation. (Note 6) Information processing equipment, A query analysis unit analyzes the query to be processed and determines whether the first operation is included in the query to be processed, A query optimization unit that performs query optimization processing, The system comprises a query execution unit that executes the query to be processed after it has been optimized by the query optimization unit, The first operation includes an operation to create a combined table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more projection target columns of the records into the combined table. The query optimization process includes, when the query analysis unit determines that the query to be processed includes the first operation, determining whether the content of the first operation satisfies the first and second conditions, and if the content of the first operation satisfies the first and second conditions, replacing the first operation in the query to be processed with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is extracted as the target value, This includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table. (Note 7) The program is A program that causes a computer to perform data processing optimization, which includes a first operation: creating an integrated table by joining a first table and a second table, each having a common key, and projecting the values ​​of one or more predetermined projection target columns of records into the integrated table, The steps include determining whether the content of the first operation satisfies the first and second conditions, If the content of the first operation satisfies the first and second conditions, the step of replacing the first operation with the second operation is included. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is identified as the target value, This includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table. Furthermore, the forms described in appendices 6 and 7 can be expanded into the forms described in appendices 2-5, similar to appendice 1.

[0082] Furthermore, each of the non-patent document disclosures cited above shall be incorporated into this book by reference. Within the scope of this disclosure (including the claims), further modifications and adjustments to the embodiments or variations are possible based on the fundamental technical concept. Furthermore, within the framework of this disclosure, various combinations or selections (or partial deletions) of various disclosed elements (including each element of each claim, each element of each embodiment or modification, each element of each drawing, etc.) are possible. In other words, this disclosure includes the entire disclosure, including the claims, and of course, any modifications and alterations that a person skilled in the art could make in accordance with the technical idea. In particular, the numerical ranges described in this book should be interpreted as including any specific numerical values ​​or sub-ranges within those ranges, even if not otherwise stated. For example, the configurations shown in the drawings are used only as examples to aid in understanding this disclosure. In other words, this disclosure is not limited to the configurations shown in the drawings. [Explanation of symbols]

[0083] 100: Student Table 101: Student identification information (S_id) 102: Student Name (S_name) 103: Grades (S_marks) 104: Country code (C_id) 110: Judgment result 111: Extraction result 112: Projection result 200: Country Table 201: Country code (C_id) 202: Country name (C_name) 200a: Country Table 210: Extraction Country Table 300: Integrated Table 301: Projection result 500: Information Processing Device 510: Query Analysis Department 520: Query Optimization Department 530: Query execution unit 540:RDB storage section 591:CPU 592: Main memory 593:Auxiliary storage device 594: Communication I / F 595: Expansion I / F 900: Relational database (RDB)

Claims

1. A method for optimizing data processing, including the first operation, The first operation includes an operation to create an integrated table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more predetermined projection target columns of the records into the integrated table. The aforementioned optimization method is The computer determines whether the content of the first operation satisfies the first and second conditions. If the content of the first operation satisfies the first and second conditions, replace the first operation with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is identified as the target value, A data processing optimization method comprising projecting the value of the projection target column of a record having the target value onto the common key column of the first table.

2. A method for optimizing data processing according to claim 1, A data processing optimization method in which, in the second operation, the value of the projection target column of the record in the first table, whose common key value is the target value, is projected.

3. A method for optimizing data processing according to claim 1 or 2, A data processing optimization method wherein the computer performs the first operation if at least one of the first and second conditions is not met.

4. A method for optimizing data processing according to any one of claims 1 to 3, The aforementioned second operation is the ISIN FILTER-PROJECT operation, which is a data processing optimization method.

5. A method for optimizing data processing according to any one of claims 1 to 4, The aforementioned first operation is a JOIN-PROJECT operation, which is a method for optimizing data processing.

6. A query analysis unit analyzes the query to be processed and determines whether the first operation is included in the query to be processed, A query optimization unit that performs query optimization processing, An information processing apparatus comprising a query execution unit that executes the query to be processed after it has been optimized by the query optimization unit, The first operation includes an operation to create an integrated table by joining a first table and a second table, each having a common key, and to project the values ​​of one or more projection target columns of the records into the integrated table. The query optimization process includes, when the query analysis unit determines that the query to be processed includes the first operation, determining whether the content of the first operation satisfies the first and second conditions, and if the content of the first operation satisfies the first and second conditions, replacing the first operation in the query to be processed with the second operation. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is extracted as the target value, An information processing device that includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table.

7. A program that causes a computer to perform data processing optimization, which includes a first operation: creating an integrated table by joining a first table and a second table, each having a common key, and projecting the values ​​of one or more predetermined projection target columns of records into the integrated table, The steps include determining whether the content of the first operation satisfies the first and second conditions, If the content of the first operation satisfies the first and second conditions, the step of replacing the first operation with the second operation is included. The first condition is that each value of the common key in the second table is unique. The second condition is that all of the columns to be projected are included in the first table, The aforementioned second operation is, The value of the common key in the record of the second table is identified as the target value, A program that includes projecting the value of the projection target column of a record having the target value onto the common key column of the first table.

Images