Data comparison method and device based on index clustering table

Abstract

The invention relates to the technical field of computers, in particular to a data comparison method and device based on an index clustering table, according to the data comparison method and device based on the index clustering table, the offset of a clustering primary key column value in a cache file serves as a virtual ROWID to replace a real ROWID, comparison is carried out, the virtual ROWIDs with different summary information are output after comparison is completed, and when a comparison report is generated, the comparison report is generated, and the data comparison efficiency is improved. Finding a corresponding clustering primary key column value in the cache file by using the virtual ROWID, and querying complete data from the to-be-compared clustering table through the clustering primary key column value to obtain a comparison result; when the data size of a clustering table is large and difference data exists, due to the fact that no first-level index exists on an ROWID column of the clustering table, full-table scanning is needed when complete difference data are queried in the clustering table through ROWID of a database, and efficiency is very low, however, according to the method, the complete data are queried by obtaining a clustering main key column through virtual ROWID mapping, and the efficiency is very high. And the performance of the whole comparison process is improved.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a data comparison method and apparatus based on indexed clustered tables. Background Technology

[0002] With the development of database technology, more and more databases are being used in various industries, and multiple databases are used simultaneously. These databases may be homogeneous or heterogeneous. In many cases, it is necessary to compare table data on these different databases, requiring an efficient and fast database data comparison solution.

[0003] Currently, some databases on the market support indexed clustered tables (hereinafter referred to as clustered tables). Clustered tables are different from ordinary tables. Ordinary tables use ROWID as a first-level index to organize data, while clustered tables use user-defined columns (which can be a single column or a combination of multiple columns) to organize data. ROWID becomes its data column, which makes it impossible to use ROWID to quickly locate data in clustered tables.

[0004] Currently, the comparison algorithm generally uses a combination of ROWID and the MD5 (Message-Digest Algorithm 5) value of the entire row of data for comparison. This is because the length of ROWID and the MD5 value of the entire row of data are fixed, and the memory consumption during comparison can be precisely controlled within a certain range. The comparison algorithm is relatively easy to implement. The difference results of the comparison are output in the form of ROWID. Then, based on ROWID, the difference data is retrieved from the left and right databases to generate a comparison report, because the comparison report needs to list the complete row data in order to analyze the data differences.

[0005] If the tables being compared include clustered tables, and clustered tables allow users to define custom columns as the primary key, the uncertainty of the data type and combination of these primary key columns cannot meet the requirements of current comparison algorithms. On non-clustered tables, the ROWID column is used as a primary index, allowing for precise data location and high efficiency when querying data via ROWID. However, on clustered tables, the ROWID column is not indexed, so querying differentiated data using ROWID requires a full table scan, which is extremely inefficient and negatively impacts the performance of the entire comparison process.

[0006] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0007] The technical problem to be solved by this invention is how to solve the problem of low efficiency when comparing data in clustered tables in the prior art.

[0008] The present invention adopts the following technical solution: Firstly, a data comparison method based on indexed clustered tables is provided, including: Obtain the cluster primary key column value and summary information for each row of data in the cluster table to be compared; The cluster primary key column value and data source are stored in a cache file, and the offset of the cluster primary key column value in the cache file is used as the virtual ROWID of different cluster tables. Compare the summary information of the clustered tables to be compared, and output the virtual ROWID and data source of the difference rows; The comparison results are obtained based on the virtual ROWID of the different rows and the data source.

[0009] Preferably, the method further includes: When the structures of the clustered primary key columns of the tables to be compared match, the clustered primary key column values ​​and data sources of the tables to be compared are stored in the same cache file. When the clustered primary key column structures of the clustered tables to be compared do not match, the clustered primary key column values ​​and data sources of the clustered tables to be compared are stored in different cache files.

[0010] Preferably, the step of comparing the summary information of the cluster tables to be compared and outputting the virtual ROWID and data source of the difference rows specifically includes: The virtual ROWID and summary information of the clustered tables to be compared are submitted to the comparison engine for collision. The comparison engine eliminates rows with consistent summary information and retains the virtual ROWID and summary information of rows with inconsistent summary information to output the virtual ROWID and data source of the different rows.

[0011] Preferably, obtaining the comparison result based on the virtual ROWID and data source of the different rows specifically includes: The clustered primary key column value is read from the corresponding cache file based on the virtual ROWID of the different rows; Based on the data source, determine the cluster table to be compared corresponding to the read cluster primary key column value; The comparison results are obtained by querying the complete row data in the corresponding cluster table to be compared based on the read cluster primary key column value.

[0012] Secondly, a data comparison method based on an indexed clustered table is provided, the method comprising: Retrieve the clustered primary key column value and summary information for each row of data in both the clustered and non-clustered tables; Store the cluster primary key column value of the clustered table and the data source into a cache file, and use the offset of the cluster primary key column value of the clustered table in the cache file as the virtual ROWID of the clustered table. Compare the summary information of the clustered table with the summary information of the non-clustered table, and output the virtual ROWID of the difference row in the clustered table, the real ROWID of the difference row in the non-clustered table, and the data source; The comparison results are obtained based on the virtual ROWIDs of the differing rows in the clustered table, the real ROWIDs of the differing rows in the non-clustered table, and the data source.

[0013] Preferably, the step of comparing the summary information of the clustered table and the summary information of the non-clustered table, and outputting the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source specifically includes: The summary information of the clustered table and the summary information of the non-clustered table are submitted to the comparison engine for collision. The comparison engine eliminates rows in the clustered and non-clustered tables whose summary information is consistent, while retaining the virtual ROWID, real ROWID, and summary information of rows with inconsistent summary information. This results in the output of the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source.

[0014] Preferably, the step of obtaining the comparison result based on the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source specifically includes: Read the clustered primary key column value from the cache file based on the virtual ROWID and data source; read the clustered primary key column value from the non-clustered table based on the real ROWID; Based on the data source, determine the clustered table and non-clustered table corresponding to the read clustered primary key column values; Based on the retrieved clustered primary key column value, retrieve the complete row data from the corresponding clustered or non-clustered table to obtain the comparison results.

[0015] Preferably, the method further includes: Write the entire row of data from the cluster table to be compared to each line in the cache file.

[0016] Thirdly, a data comparison device based on an indexed clustered table is provided, the data comparison device based on an indexed clustered table includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the data comparison method based on the indexed clustered table.

[0017] Fourthly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the data comparison method based on indexed clustered tables described in the first aspect.

[0018] Fifthly, a chip is provided, comprising: a processor and an interface for calling and running a computer program stored in memory from memory, performing a data comparison method based on an indexed clustered table as described in the first aspect.

[0019] In a sixth aspect, a computer program product containing instructions is provided that, when executed on a computer or processor, causes the computer or processor to perform the data comparison method based on an indexed clustered table as described in the first to fourth aspects and any one of them.

[0020] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention caches the clustered primary key column value of the clustered table in a cache file and uses the offset of the clustered primary key column value in the cache file as the virtual ROWID for different clustered tables. Then, it compares the summary information of the clustered tables to be compared, outputting the virtual ROWID and data source of the differing rows. Finally, it obtains the comparison result based on the virtual ROWID and data source of the differing rows. This invention uses the offset of the clustered primary key column value in the cache file as the virtual ROWID to replace the real ROWID for comparison. After comparison, it outputs virtual ROWIDs with different summary information. When generating the comparison report, it uses the virtual ROWID to find the corresponding clustered primary key column value in the cache file and retrieves the complete data from the clustered table to be compared using the clustered primary key column value to obtain the comparison result. When the clustered table has a large amount of data and contains differing data, since there is no first-level index on the ROWID column of the clustered table, retrieving the complete differing data from the clustered table using the database's own ROWID requires a full table scan, which is very inefficient. This invention improves the performance of the entire comparison process by using virtual ROWID mapping to obtain the clustered primary key column to retrieve the complete data. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 This is a flowchart illustrating a data comparison method based on an indexed clustered table provided in an embodiment of the present invention; Figure 2 This is a schematic diagram of a process for obtaining comparison results provided by an embodiment of the present invention; Figure 3 This is another flowchart illustrating a data comparison method based on an indexed clustered table provided in an embodiment of the present invention; Figure 4 This is another schematic diagram of a process for obtaining comparison results provided by an embodiment of the present invention; Figure 5 This is a schematic diagram of a clustering table provided in an embodiment of the present invention; Figure 6 This is a schematic diagram of another cluster table structure provided in an embodiment of the present invention; Figure 7 This is a schematic diagram of the structure of a cache file provided in an embodiment of the present invention; Figure 8 This is a schematic diagram of the combination of the MD5 value of the row data of the left table and the virtual ROWID R1 provided in an embodiment of the present invention; Figure 9 This is a schematic diagram of the combination of the MD5 value of the row data of the right table and the virtual ROWID R1 provided in an embodiment of the present invention; Figure 10 This is a schematic diagram of the results of a comparison of the difference rows provided in an embodiment of the present invention; Figure 11 This is a schematic diagram of a specific structure of a cluster table provided in an embodiment of the present invention; Figure 12 This is a schematic diagram of the structure of a non-clustered table provided in an embodiment of the present invention; Figure 13 This is a schematic diagram of the structure of a clustered table after calculating the MD5 value of row data, provided in an embodiment of the present invention; Figure 14 This is a schematic diagram of the structure of a non-clustered table after calculating the MD5 value of row data, provided by an embodiment of the present invention; Figure 15 This is a schematic diagram of another cache file structure provided in an embodiment of the present invention; Figure 16 This is a schematic diagram of the structure of a clustered table after combining the row data MD5 value and virtual ROWID R1, as provided in an embodiment of the present invention. Figure 17 This is a schematic diagram of the structure of a non-clustered table after combining the MD5 value of row data and the actual ROWID R1, provided by an embodiment of the present invention; Figure 18 This is a schematic diagram of another comparison result of the difference row provided in an embodiment of the present invention; Figure 19 This is a schematic diagram of a data comparison device based on an indexed clustered table provided in an embodiment of the present invention. Detailed Implementation

[0023] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0024] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0025] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0026] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0027] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0028] Example 1: When at least one of the two tables to be compared is a clustered table, existing comparison engines typically use data columns with fixed precision for comparison when locating data rows in the output results. These columns can be the database's own ROWID column or a numeric primary key column with fixed precision. However, clustered tables allow users to use custom columns as the clustered primary key. The uncertainty in the data type and combination of these clustered primary key columns cannot meet the requirements of current comparison engines. When the comparison engine uses the database's own ROWID column as the output result for the clustered table, querying differentiated data using ROWID is extremely inefficient, thus impacting the performance of the entire comparison process.

[0029] Since some databases do not support clustered tables, this embodiment does not require the clustered primary key structures of the two tables to be compared to match. Both sides can consist of the same custom columns, or one side can consist of a custom column and the other side can consist of the database's own ROWID column. This includes at least two comparison scenarios: one is that both tables to be compared are clustered tables, and their clustered primary key columns can both be custom columns; the other is that clustered tables and non-clustered tables are being compared, where the clustered primary key column of the clustered table can be a custom column, while the clustered primary key column of the non-clustered table is a fixed ROWID column.

[0030] This embodiment first describes the comparison method when both tables to be compared are clustered tables. To address the problems of existing technologies, this embodiment proposes a data comparison method based on indexed clustered tables. In one embodiment, such as... Figure 1 As shown, it includes: Step 101: Obtain the cluster primary key column value and summary information for each row of data in the cluster table to be compared.

[0031] The cluster table to be compared includes two cluster tables. For ease of description, in this embodiment, the two tables to be compared are referred to as the left table and the right table, respectively.

[0032] In one embodiment, a full table query is performed on each clustered table to be compared. For each row of data retrieved, its cluster primary key column value is extracted. The cluster primary key column value is a column specified when the clustered table is defined, which determines the distribution and storage location of the data on physical blocks (rows with the same cluster key value are stored together). For each row of data retrieved, a summary of all columns in that row is calculated, where the summary information uniquely reflects the data in that row.

[0033] In one embodiment, the digest information can be an MD5 value. Specifically, the MD5 message digest algorithm can be used to calculate the MD5 value of the row data to obtain a fixed-length MD5 value.

[0034] In one embodiment, each cluster table corresponds to a result set, which includes record information for each row and column of data. Each record contains: the cluster primary key column value, summary information, and the data source (i.e., from the left table or the right table).

[0035] Step 102: Store the cluster primary key column value and data source into a cache file, and use the offset of the cluster primary key column value in the cache file as the virtual ROWID of different cluster tables.

[0036] First, a cache file (which can be a disk file or a memory-mapped file) is created for sequential writing throughout the comparison process.

[0037] In one embodiment, when the clustered primary key columns of the tables to be compared match in structure, the clustered primary key column values ​​and data sources of both tables are stored in the same cache file. By sharing a single cache file and storing the clustered primary key column values ​​in an append-only manner, the sequential write ratio of disk I / O (Input / Output) can be improved, thereby increasing write efficiency. When the clustered primary key columns of the tables to be compared do not match in structure, the clustered primary key column values ​​and data sources of both tables are stored in separate cache files.

[0038] In this context, matching the clustered primary key column structure of the clustered tables to be compared means that the column name and column type definitions of the clustered primary key column of the left and right tables are the same. For example, if the clustered primary key column of the left and right tables is "ID column" and the data type of the column is "VARCHAR(100)", then the clustered primary key column structures of the two clustered tables match. Otherwise, they do not match.

[0039] In one embodiment, each row of data corresponds to a record (clustered primary key column value and data source), and each record is written to a cache file sequentially. Simultaneously with writing each record, the byte offset of that record in the cache file is recorded, and this byte offset is defined as the virtual ROWID for that record in this comparison task. The file offset (virtual ROWID) corresponding to the same record is unique and constant throughout the comparison process, solving the problem that the physical ROWID can change (due to data being physically reorganized according to the clustered key value, such as insertion / update or deletion, causing changes in the row's storage location). The virtual ROWID allows for quick location of the original clustered primary key column value and data source.

[0040] In one embodiment, the cache file stores only key identification information (clustered primary key column value, data source, and virtual ROWID) instead of the entire row of data, saving space and I / O.

[0041] Step 103: Compare the summary information of the cluster tables to be compared, and output the virtual ROWID and data source of the difference rows.

[0042] In one embodiment, the virtual ROWID and summary information of the clustered tables to be compared are submitted to the comparison engine for collision. The comparison engine eliminates rows with consistent summary information and retains the virtual ROWID and summary information of rows with inconsistent summary information to output the virtual ROWID and data source of the different rows.

[0043] The comparison engine is an algorithm module that performs collision comparisons based on the table ROWID and its corresponding row data summary information. The input consists of the ROWIDs of the two tables (marked with the data source) and their corresponding row data summary information. The comparison engine performs collision comparisons based on the row data summary information of the two tables, eliminating rows with the same summary information but different data sources. Finally, it outputs the ROWIDs of the remaining rows with different summary information as the comparison result.

[0044] In one embodiment, the comparison engine determines the data based on the summary information corresponding to the left and right table rows. If the summary information of the two rows is exactly the same, the row is considered consistent and immediately eliminated, without proceeding to the next step. Otherwise, the row is considered inconsistent and the remaining data (including different summary information, their corresponding virtual ROWIDs, and the data source) is output.

[0045] Step 104: Obtain the comparison results based on the virtual ROWID and data source of the difference row.

[0046] The comparison results are the row data in the cluster table to be compared.

[0047] In one embodiment, such as Figure 2 As shown, the comparison results obtained based on the virtual ROWID and data source of the difference row specifically include: Step 1041: Read the clustered primary key column value from the corresponding cache file based on the virtual ROWID of the difference row.

[0048] Specifically, the initial clustered primary key column value is read from the cache file based on the virtual ROWID of the difference row and the data source. If the comparison table consists of two clustered tables, the two clustered primary key column values ​​corresponding to the difference row data are read respectively.

[0049] Step 1042: Determine the cluster table to be compared corresponding to the read cluster primary key column value based on the data source; query the complete row data in the corresponding cluster table to be compared based on the read cluster primary key column value to obtain the comparison result.

[0050] Specifically, the two cluster primary key column values ​​read in step 1041 are used to search back in their respective cluster tables to obtain the row data that differ between the two cluster tables (i.e., the comparison results).

[0051] In summary, this embodiment first caches the clustered primary key column value of the clustered table in a cache file, and uses the offset of the clustered primary key column value in the cache file as the virtual ROWID for different clustered tables; then, it compares the summary information of the clustered tables to be compared, and outputs the virtual ROWID and data source of the difference rows; finally, it obtains the comparison result based on the virtual ROWID and data source of the difference rows. This embodiment uses the offset of the clustered primary key column value in the cache file as the virtual ROWID to replace the real ROWID and performs comparison. After the comparison is completed, it outputs the virtual ROWIDs with different summary information. When generating a comparison report, the virtual ROWID is used to find the corresponding clustered primary key column value in the cache file. The complete data is then retrieved from the cluster table to be compared using the clustered primary key column value to obtain the comparison results. When the amount of data in the cluster table is large and there are discrepancies, it is much less efficient than using the database's own ROWID to retrieve the complete data, which requires a full table scan (whereas the database's own ROWID column cannot be indexed in the cluster table, a full table scan is necessary). This embodiment improves the performance of the entire comparison process by using virtual ROWID mapping to obtain the clustered primary key column to retrieve the complete data.

[0052] Example 2: This embodiment describes a comparison method when the two tables to be compared are a clustered table and a non-clustered table. In one embodiment, such as Figure 3 As shown, the data comparison method based on indexed clustered tables further includes: Step 201: Obtain the clustered primary key column value and summary information for each row of data in the clustered table and the non-clustered table respectively.

[0053] For clustered tables, the clustered primary key column value for each row of data is customizable, while for non-clustered tables, the clustered primary key column value for each row of data is fixed, namely the ROWID column of the database itself.

[0054] Step 202: Store the clustered primary key column value and data source of the clustered table into a cache file, and use the offset of the clustered primary key column value in the cache file as the virtual ROWID of the clustered table.

[0055] Step 202 is similar to step 102 in Example 1, except that cache files are built only for clustered tables, and no caching is required for non-clustered tables.

[0056] In one embodiment, if the clustered primary key column of either the left or right table is its actual ROWID column, then there is no need to create a corresponding cache file for that table; that is, no cache file needs to be created for non-clustered tables. In other words, when comparing clustered and non-clustered tables, only one cache file needs to be created to store the clustered primary key column value, summary information, and data source of the clustered table.

[0057] Step 203: Compare the summary information of the clustered table and the summary information of the non-clustered table, and output the virtual ROWID of the difference row in the clustered table, the real ROWID of the difference row in the non-clustered table, and the data source.

[0058] In this context, the true ROWID of a non-clustered table refers to the ROWID of the database itself. For non-clustered tables, the ROWID is fixed and can therefore be directly used for subsequent comparisons and row data queries.

[0059] In one embodiment, comparing the summary information of the clustered table and the summary information of the non-clustered table to output the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source specifically includes: submitting the summary information of the clustered table and the summary information of the non-clustered table to a comparison engine for collision; eliminating rows in the clustered table and non-clustered table with consistent summary information through the comparison engine, and retaining the virtual ROWID, real ROWID, and summary information of rows with inconsistent summary information, so as to output the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source.

[0060] For the specific comparison method, please refer to Example 1, which will not be repeated here.

[0061] The final output includes the virtual ROWIDs corresponding to rows with different summary information in the clustered table, the real ROWIDs corresponding to rows with different summary information in the non-clustered table, and their respective data sources.

[0062] Step 204: Obtain the comparison results based on the virtual ROWID of the difference rows in the clustered table, the real ROWID of the difference rows in the non-clustered table, and the data source.

[0063] In one embodiment, such as Figure 4 As shown, the comparison results are obtained based on the virtual ROWIDs of the differing rows in the clustered table, the real ROWIDs of the differing rows in the non-clustered table, and the data source. Specifically, these include: Step 2041: Read the clustered primary key column value from the cache file based on the virtual ROWID and data source; read the clustered primary key column value from the non-clustered table based on the real ROWID.

[0064] For clustered tables, the corresponding clustered primary key column value (denoted as clustered primary key column value 1) needs to be read from the cache file in step 202 based on the virtual ROWID of the difference row and the data source.

[0065] For non-clustered tables, the corresponding clustered primary key column value (denoted as clustered primary key column value 2) is directly read from the non-clustered table based on the actual ROWID of the difference row and the data source.

[0066] Step 2042: Determine the clustered table and non-clustered table corresponding to the read clustered primary key column value based on the data source.

[0067] Step 2043: Based on the retrieved clustered primary key column value, return to the corresponding clustered table or non-clustered table to query the complete row data to obtain the comparison results.

[0068] Specifically, based on the clustered primary key column value 1, the complete row data 1 is read from the clustered table; based on the clustered primary key column value 2, the complete row data 2 is directly read from the non-clustered table; and finally, the comparison result is obtained based on row data 1 and row data 2. Row data 1 and row data 2 can represent one or more rows.

[0069] In summary, this embodiment caches the clustered primary key column value of the clustered table in a cache file and uses the offset of the clustered primary key column value in the cache file as the virtual ROWID for different clustered tables. It then obtains the real ROWID and summary information of the row data in the non-clustered table. By comparing the summary information of the clustered table and the non-clustered table, it outputs the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source, ultimately obtaining the comparison result. When the data volume in the clustered table is large and there are differences between it and the non-clustered table, querying the complete data using the ROWID of the clustered table itself requires a full table scan, which is very costly. This embodiment improves the performance of the entire comparison process by querying the complete data using the virtual ROWID corresponding to the clustered table.

[0070] Example 3: In one embodiment, for comparing clustered tables in Embodiment 1 and comparing clustered and non-clustered tables in Embodiment 2, the data comparison method based on indexed clustered tables further includes: judging the amount of data in the clustered table to be compared; when the amount of data in the clustered table to be compared is less than a preset value, the ROWID of the database itself is directly sampled as the virtual ROWID, without the need to create a cache file. The preset value can range from 2500 to 3500 rows. This is because using clustered primary key column value caching mapping itself incurs certain performance overhead. In scenarios where the amount of data in the clustered table to be compared is very small (e.g., less than 2500 rows), obtaining the corresponding row data through a full table scan of the clustered table using the database's own ROWID is also very efficient. Therefore, using the clustered primary key column value caching mapping scheme (i.e., this scheme) would be inefficient, and using the database's own ROWID as the data row location is more efficient.

[0071] To prevent the clustered table from being modified during the comparison process, which would prevent the complete row data from being found using the original clustered primary key column value, in one embodiment, the entire row of data from the clustered table to be compared is written to each line of the cache file to improve the speed and accuracy of comparison result generation.

[0072] Appending entire rows of data improves the speed of generating the final comparison results and the accuracy of the comparison report. This is because when generating the comparison report, it is not necessary to retrieve the complete row data from the original cluster table again based on the cluster primary key column value. Instead, the difference row data can be found directly in the cache file based on the virtual ROWID of the difference row to generate the comparison results. This also prevents the difference row data from being modified during the comparison process, which could lead to inconsistencies between the latest row data retrieved after the comparison is completed and the row data at the time of comparison. However, the disadvantage is that it increases the amount of data that needs to be stored, thereby reducing the overall comparison performance.

[0073] Example 4: To further illustrate the data comparison method based on indexed clustered tables proposed in Example 1 (comparing clustered tables with each other), this example will be provided for specific explanation.

[0074] In one embodiment, such as Figure 5 As shown, the left table is a clustered table: L(ID VARCHAR(100) CLUSTERPRIMARY KEY, C1 INT); (The rest of the text appears to be a table table and doesn't translate directly.) Figure 6 As shown, the right table is a clustered table: R(ID VARCHAR(100) CLUSTER PRIMARYKEY, C1 INT); both the left and right tables are clustered tables (for ease of explanation, the summary information of each row of data has been calculated here in the figure, and the MD5 value is calculated here).

[0075] If both tables have an ID column as their clustered primary key, then the structures of the clustered primary key columns in the left and right tables match. The clustered primary key column values ​​and data sources for both tables are stored in a single cache file. Without considering the data size of the clustered tables, the data comparison method based on indexed clustered tables proposed in Example 1 is used to compare the left and right tables.

[0076] In order to obtain the clustered primary key column value, the query statement for the left table is: SELECT ID,C1 FROM L, and the query statement for the right table is: SELECT ID,C1 FROM R.

[0077] like Figure 7 As shown, the clustered primary key column value of the current row of data is appended to the same cache file F, and the offset address of the row of data in the cache file F is recorded as the virtual ROWID R1 of the row of data.

[0078] like Figure 8 and Figure 9 As shown, the MD5 values ​​of the row data in both the left and right tables are combined with the virtual ROWID R1 of that row data. After combination, the MD5 values ​​and virtual ROWID R1 of the row data in both the left and right tables are submitted to the comparison engine for collision comparison. The comparison engine eliminates rows with the same MD5 values ​​from both sides, retaining only the virtual ROWID R1 and the row data MD5 values ​​with different MD5 values. Figure 10 As shown, the comparison engine will eliminate the two rows of data with MD5 value A1 on the left and right, leaving the two rows of data with MD5 values ​​B2 and C3.

[0079] Based on the MD5 hashes of row data B2 and C3, the corresponding cache file F and the corresponding cluster table are located. Specifically, the table containing the virtual ROWID R1 of 10 corresponding to the row data MD5 hash of B2 is the left table L, and the table containing the virtual ROWID R1 of 30 corresponding to the row data MD5 hash of C3 is the right table R.

[0080] Convert the two virtual ROWIDs R1 into offsets in the cache file F, and read the cached clustered primary key column values ​​from the corresponding offsets. Specifically, virtual ROWID R1 of 10 maps to the clustered primary key column value B of the left table L, and virtual ROWID R1 of 30 maps to the clustered primary key column value C of the right table R.

[0081] Using the clustered primary key column value as a condition, query the complete data row of the corresponding row in the clustered table of its corresponding database and output the comparison results. The query statements for the differences between the two tables are as follows: Left table difference data query: SELECT ID,C1 FROM L WHERE ID='B'; Query the differences in the right table: SELECT ID, C1 FROM R WHERE ID='C'. The final output will show the comparison results.

[0082] As can be seen from the above process, when caching the clustered primary key column values ​​of the clustered table, since the clustered primary key column structures of the left and right tables match (both are "ID"), they can share a single cache file F. The offset of the clustered primary key column values ​​in the cache file F is used to map and associate them with the clustered primary key column values, thereby achieving a mapping from non-fixed precision data to fixed precision data to meet the requirements of the comparison engine. After the comparison engine completes the comparison output file F, it then extracts the clustered primary key column values ​​stored at the corresponding offsets in the cache file to retrieve the complete data from the corresponding clustered table to obtain the comparison results. When the table data volume is large and there are discrepancies, this method is much faster than using the clustered table's own ROWID, because using the ROWID to query the complete data requires a full table scan, which is very costly.

[0083] Example 5: To further illustrate the data comparison method based on indexed clustered tables proposed in Example 2 (comparing clustered and non-clustered tables), this example will be provided for specific explanation.

[0084] In one embodiment, such as Figure 11 As shown, the left table is a clustered table: L(ID VARCHAR(100) CLUSTERPRIMARY KEY, C1 INT); (The rest of the text appears to be a table table and doesn't translate directly.) Figure 12 As shown, the right table is a non-clustered table (ordinary table): R(ID VARCHAR(100), C1INT). Similarly, without considering the data size of the clustered table, the left and right tables are compared using the data comparison method based on indexed clustered tables proposed in Example 2.

[0085] To retrieve the clustered primary key column value, the left table query is: `SELECT ID, C1 FROM L`. The right table is a regular table, requiring the addition of a `ROWID` query item; the query is: `SELECT ROWID, ID, C1 FROM R`. Figure 13 and Figure 14 As shown, the data in each row of both tables is converted into strings and then the MD5 values ​​of the row data are calculated according to the query item order.

[0086] In one embodiment, such as Figure 15As shown, the clustered primary key column value of the left table row data is appended to the cache file F, and the offset address of the row data in file F is recorded as the virtual ROWID R1 of the row data; the clustered primary key column of the right table is a fixed-precision ROWID column, so it does not need to be cached.

[0087] like Figure 16 and Figure 17 As shown, the MD5 value of the row data in the left table is combined with the virtual ROWID R1 of that row data, and the MD5 value of the row data in the right table is combined with the real ROWID R1 of that row data. After combination, it is submitted to the comparison engine for collision comparison. The comparison engine eliminates rows with the same MD5 value from both sides, and only retains the virtual ROWID R1, real ROWID R1, and row data MD5 values ​​with inconsistent MD5 values. Figure 18 As shown, the two data lines with MD5 values ​​of A1 in the row data are removed, leaving the two data lines with MD5 values ​​of B2 and C3 in the row data.

[0088] Based on the row data MD5 value of B2 and the accompanying data source, the corresponding cache file F and the corresponding cluster table are located. Specifically, the table to which the virtual ROWID R1 is 10, corresponding to the row data MD5 value of B2, belongs is the left table L, and the table to which the real ROWID R1 is 2, corresponding to the row data MD5 value of C3, belongs is the right table R.

[0089] Convert the virtual ROWID R1 to an offset in the cache file F, and read the cached clustered primary key column value from the corresponding offset. Here, virtual ROWID R1 of 10 maps to the clustered primary key column value B of the left table L. Use the real ROWID R1 to directly query the corresponding complete row data in the right table to output the comparison results. The query statements for the differences between the two tables are as follows: Left table difference data query: SELECT ID,C1 FROM L WHERE ID='B'; Query the differences in the right table: SELECT ID, C1 FROM R WHERE ROWID=2. The final output will show the comparison results.

[0090] As can be seen from the above process, when caching the clustered primary key column value of the clustered table, since the primary key column of the right table is the database's own ROWID column, it does not need to be cached. Therefore, only the left table needs to use the cache file F. The left table uses the offset of the clustered primary key column value in the cache file F to map and associate it with the clustered primary key column value, thereby realizing the conversion of non-fixed precision data to fixed precision data to meet the requirements of the comparison engine. The right table directly uses the ROWID column. After the comparison engine completes the comparison output file F offset, it then retrieves the clustered primary key column value stored at the corresponding offset in the cache file to obtain the complete data from the corresponding clustered table to get the comparison result. When the table data volume is large and there are differences, this method is much faster than using the ROWID of the left table's clustered table itself, because using the left table's own ROWID to query the complete data requires a full table scan, which is very costly.

[0091] Example 6: In Embodiment 1, a data comparison method based on an indexed clustered table was provided. In this embodiment, a data comparison device based on an indexed clustered table will be proposed. The data comparison device based on an indexed clustered table includes: a processor and a memory for storing processor-executable instructions; wherein, the processor is configured to execute the data comparison method based on an indexed clustered table described in Embodiment 1.

[0092] like Figure 19 As shown, the data comparison device based on the indexed cluster table includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0093] Processor 21 can be a Central Processing Unit (CPU). Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0094] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the data comparison method based on indexed clustered tables in Embodiment 1 of this invention. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0095] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform actions such as... Figure 1 The data comparison method based on indexed clustered tables in Example 1 is illustrated. For specific details regarding the above-described data comparison method based on indexed clustered tables, please refer to the relevant documentation. Figure 1 , Figure 2 and Figure 3 The relevant descriptions and effects in the embodiments shown are for reference only and will not be repeated here.

[0096] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to perform the data comparison method based on an indexed clustered table as described in Embodiment 1.

[0097] The computer storage medium stores computer-executable instructions, which can execute the data comparison method based on the indexed clustered table in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0098] The specific steps of the data comparison method based on the indexed clustered table are described in Example 1, and will not be repeated in this example.

[0099] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A data comparison method based on indexed clustered tables, characterized in that, include: Obtain the cluster primary key column value and summary information for each row of data in the cluster table to be compared; The cluster primary key column value and data source are stored in a cache file, and the offset of the cluster primary key column value in the cache file is used as the virtual ROWID of different cluster tables. Compare the summary information of the cluster tables to be compared, and output the virtual ROWID and data source of the difference rows; The comparison results are obtained based on the virtual ROWID of the different rows and the data source.

2. The data comparison method based on indexed clustered tables according to claim 1, characterized in that, The method further includes: When the structures of the clustered primary key columns of the clustered tables to be compared match, the clustered primary key column values ​​and data sources of the clustered tables to be compared are stored in the same cache file. When the clustered primary key column structures of the clustered tables to be compared do not match, the clustered primary key column values ​​and data sources of the clustered tables to be compared are stored in different cache files.

3. The data comparison method based on indexed clustered tables according to claim 1, characterized in that, The process involves comparing the summary information of the cluster tables to be compared, and outputting the virtual ROWID and data source of the differing rows, specifically including: The virtual ROWID and summary information of the clustered tables to be compared are submitted to the comparison engine for collision. The comparison engine eliminates rows with consistent summary information and retains the virtual ROWID and summary information of rows with inconsistent summary information to output the virtual ROWID and data source of the different rows.

4. The data comparison method based on indexed clustered tables according to claim 1, characterized in that, The comparison results obtained based on the virtual ROWID and data source of the difference row specifically include: The clustered primary key column value is read from the corresponding cache file based on the virtual ROWID of the different rows; Based on the data source, determine the cluster table to be compared corresponding to the read cluster primary key column value; The comparison results are obtained by querying the complete row data in the corresponding cluster table to be compared based on the read cluster primary key column value.

5. A data comparison method based on indexed clustered tables, characterized in that, The method includes: Retrieve the clustered primary key column value and summary information for each row of data in both the clustered and non-clustered tables; Store the cluster primary key column value and data source of the clustered table into a cache file, and use the offset of the cluster primary key column value in the cache file as the virtual ROWID of the clustered table. Compare the summary information of the clustered table with the summary information of the non-clustered table, and output the virtual ROWID of the difference row in the clustered table, the real ROWID of the difference row in the non-clustered table, and the data source; The comparison results are obtained based on the virtual ROWIDs of the differing rows in the clustered table, the real ROWIDs of the differing rows in the non-clustered table, and the data source.

6. The data comparison method based on indexed clustered tables according to claim 5, characterized in that, The comparison of the summary information of the clustered table and the summary information of the non-clustered table, and the output of the virtual ROWID of the difference row in the clustered table, the real ROWID of the difference row in the non-clustered table, and the data source, specifically includes: The summary information of the clustered table and the summary information of the non-clustered table are submitted to the comparison engine for collision. The comparison engine eliminates rows in the clustered and non-clustered tables whose summary information is consistent, while retaining the virtual ROWID, real ROWID, and summary information of rows with inconsistent summary information. This results in the output of the virtual ROWID of the differing rows in the clustered table, the real ROWID of the differing rows in the non-clustered table, and the data source.

7. The data comparison method based on indexed clustered tables according to claim 5, characterized in that, The comparison results, obtained based on the virtual ROWIDs of the differing rows in the clustered table, the real ROWIDs of the differing rows in the non-clustered table, and the data source, specifically include: Read the clustered primary key column value from the cache file based on the virtual ROWID and data source; read the clustered primary key column value from the non-clustered table based on the real ROWID; Based on the data source, determine the clustered table and non-clustered table corresponding to the read clustered primary key column values; Based on the retrieved clustered primary key column value, retrieve the complete row data from the corresponding clustered or non-clustered table to obtain the comparison results.

8. The data comparison method based on an indexed clustered table according to any one of claims 5 to 7, characterized in that, The method further includes: Write the entire row of data from the cluster table to be compared to each line in the cache file.

9. A data comparison device based on an indexed clustered table, characterized in that, The data comparison device based on the indexed clustered table includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the data comparison method based on an indexed clustered table as described in any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the data comparison method based on an indexed clustered table as described in any one of claims 1-8.

Images