Rownum-based data synchronization method for Oracle

By using rownum pseudo-columns, SAMPLE functions and DBMS_RANDOM.VALUE random numbers in Oracle database, parallel data synchronization method is designed, and data synchronization is inefficient in scenarios that do not include primary key fields or primary key non-integer types, and efficient data is achieved synchronous.

CN118939730APending Publication Date: 2024-11-12CHINA ELECTRONICS CLOUD DIGITAL INTELLIGENCE TECH CO LTD
View PDF 0 Cites 1 Cited by

Patent Information

Application Number
CN202411004142.5
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-25
Publication Date
2024-11-12

AI Technical Summary

Technical Problem

Existing data synchronization tools are difficult to improve data synchronization efficiency and performance when dealing with scenarios where the primary key field does not contain or the primary key field is not an integer type.

Method used

Using Oracle's rownum pseudo-column as a slicing field, combining SAMPLE function and DBMS_RANDOM.VALUE random numbers, three query SQL statement templates are designed, and the slicing range is obtained through the rownum pseudo-column, and the data is read and written in parallel, making full use of the data. Multi-core processing capability.

Benefits of technology

It breaks through the limitations on primary key fields and applies to all Oracle types of source-side tables, improves the efficiency and performance of data synchronization, and can completely cover all data in source-side data tables.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939730A_ABST
    Figure CN118939730A_ABST
Patent Text Reader

Abstract

The invention relates to a rownum-based data synchronization method for an Oracle (Oracle). The method comprises the following steps: setting a data synchronization task parallelism degree; a rownum pseudo column is used as a segmentation field, an SAMPLE function is used for carrying out sample extraction on source end data, then a DBMSRANDOM.VALUE random number is used for sorting, and a rownum segmentation point array is obtained; constructing a source end data query SQL statement template; generating a condition query SQL (Structured Query Language) statement according to the rownum segmentation point array; and starting a corresponding number of threads according to the parallelism degree of the data synchronization task, executing condition query SQL statements by multiple threads in parallel, and realizing reading of source end data and writing of target end data. According to the method, the limitation on the main key requirement of the source end table is broken through, and the problem that the data synchronization efficiency and performance cannot be improved by using the segmentation field under the scene that the source end table does not contain the main key field or the main key field is not an integer type can be solved. The method is suitable for all Oracle type source end tables, the multi-core processing capacity of the server can be fully utilized, and the data synchronization performance is remarkably improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data synchronization, and in particular to an Oracle rownum-based data synchronization method and device. Background Art

[0002] At present, with the continuous development of information construction and the gradual emphasis on the value of data, more and more enterprises and government departments have realized that the "data island" phenomenon caused by the lack of business concentration, process interconnection, and data sharing has brought great difficulties to enterprises and government departments in data analysis and mining. The first step to solve the "data island" is to bring together the data of various separate business systems or departments to establish a unified data collection, processing, and sharing center. One of the data processing methods used in this process is data synchronization technology.

[0003] For data synchronization, as the amount of data increases, how to improve the efficiency of data synchronization becomes an urgent problem to be solved. For offline data synchronization, the mainstream data synchronization tools or frameworks on the market currently support specifying a segmentation field of the source table, and then using the field to construct and splice different query SQL statements, so that each concurrent reads data in different ranges, improves reading efficiency, and improves data synchronization performance. However, the above method has strict usage restrictions. Generally, the segmentation field is required to be the primary key field, and the field is required to be an integer type. Primary keys of floating point, string, date and other types are not supported. However, in actual applications, many source tables do not have primary key fields, or the primary key fields are not integer types, which leads to the above method cannot meet the application requirements of many actual scenarios. Therefore, for source tables that do not contain primary keys, or source tables whose primary key fields are not integer types, how to improve the efficiency and performance of data synchronization in the process of data synchronization when the segmentation field cannot be used has become an urgent problem to be solved. Summary of the invention

[0004] In order to overcome the above-mentioned defects of the existing data synchronization methods, the present invention proposes a new Oracle rownum-based data synchronization method, which can effectively solve the problem that the split field cannot be used to improve the data synchronization efficiency and performance when the Oracle source table does not contain the primary key field or the primary key field is not an integer.

[0005] In order to achieve the above object, the present invention mainly makes the following designs:

[0006] (1) Based on Oracle's rownum pseudo column as a segmentation field, a data synchronization method and device are designed, which can be used in Oracle-type databases that do not contain a primary key field or the primary key field is not an integer to perform data segmentation and improve data synchronization efficiency.

[0007] (2) Based on the rownum pseudo column, use Oracle's SAMPLE function to extract samples of source data, and then use DBMS_RANDOM.VALUE random numbers to sort them to obtain the segmentation range.

[0008] (3) Three source data query SQL (Structured Query Language, a database language with multiple functions such as data manipulation and data definition) statement templates are designed and constructed using the rownum pseudo-column. Based on the obtained segmentation range integer array, all conditional query SQL statements are generated with the help of the template, which can completely cover all the data in the source table.

[0009] (4) Based on the set data synchronization parallelism, start an appropriate number of threads to execute data reading and writing in parallel, fully utilize the multi-core processing capabilities of the server, and improve the performance of data synchronization.

[0010] Specifically, the present invention provides an Oracle rownum-based data synchronization method, such as Figure 3 As shown, the method comprises the following steps:

[0011] S1. Set the parallelism of data synchronization tasks according to the data size of the source data table and the source database load;

[0012] S2. Use the rownum pseudo column of the Oracle database as the segmentation field, use Oracle's SAMPLE function to extract samples of the source data, and then use DBMS_RANDOM.VALUE random numbers to sort, and obtain the segmentation point array (segmentation range integer array) of the rownum pseudo column for conditional query;

[0013] S3. Use the rownum pseudo column to construct a source-side data query SQL statement template;

[0014] S4. Use the query SQL statement template to generate a specific conditional query SQL statement based on the segmentation point array of the rownum pseudo column for conditional query, which can completely cover all the data in the source data table;

[0015] S5. Start a corresponding number of threads according to the parallelism of the data synchronization task. Multiple threads execute conditional query SQL statements in parallel. Each thread executes a conditional query SQL statement to read the source data. After each thread obtains the query results, it writes the target data respectively.

[0016] Furthermore, the Oracle rownum-based data synchronization method of the present invention includes in step S2:

[0017] S21. First use the SAMPLE function to sample the records of the entire source data table;

[0018] S22. Based on step S21, use order by DBMS_RANDOM.VALUE to sort the DBMS_RANDOM.VALUE column of the result set to obtain a random result set;

[0019] S23. Based on step S22, use rownum<=p to filter the number of entries, where p is the set parallelism, and obtain a rownum result list corresponding to the number and parallelism value;

[0020] S24. Based on step S23, the final rownum result list is sorted in ascending order.

[0021] Furthermore, in step S21 of the Oracle rownum-based data synchronization method of the present invention, when using the SAMPLE function to sample the records of the entire source data table, the sample size is set to 99, indicating 99% of the random records in the sampled table.

[0022] Furthermore, in the Oracle rownum-based data synchronization method of the present invention, the query SQL for constructing the splitting point of the rownum pseudo column in step S2 is:

[0023] select*from(select*from(select rownum no from table_name sample(99.0)order by dbms_random.value)where rownum<=parallelism)t order by t.no asc;

[0024] Among them, table_name is the name of the source data table, and parallelism is the set parallelism;

[0025] By executing the above query SQL, a set of ascending rownum pseudo-column split points will be obtained and saved in an integer array splitPointArr.

[0026] Furthermore, in step S24 of the Oracle data synchronization method based on rownum of the present invention, the sorting of the final rownum result list is performed in the main query.

[0027] Furthermore, in step S3 of the Oracle data synchronization method based on rownum of the present invention, the source data query SQL statement template is constructed using the rownum pseudo-column, and the query SQL statement template includes the following three types:

[0028] The first type of query SQL statement template:

[0029] select rownum no,[column]①from[table_name]②where rownum<splitPointArr[0]③; Among them,

[0030] ①column is a list of fields to be queried in the source data table, separated by commas;

[0031] ②table_name is the table name of the source data table;

[0032] ③splitPointArr[0] is the first element in the rownum split point integer array splitPointArr;

[0033] The second type of query SQL statement template:

[0034] select*from(select rownum no,[column]①from[table_name]②where rownum<splitPointArr[i+1]③)where no>=splitPointArr[i]④; Among them,

[0035] ①column is a list of fields to be queried in the source data table, separated by commas;

[0036] ②table_name is the table name of the source data table;

[0037] ③splitPointArr[i+1] represents the (i+1)-th element in the rownum split point integer array splitPointArr, where the value range of i is [0, splitPointArr array length-1), including 0, but not including splitPointArr array length-1;

[0038] ④splitPointArr[i] represents the i-th element in the rownum split point integer array splitPointArr, where the value range of i is [0, splitPointArr array length - 1), including 0 but excluding splitPointArr array length - 1;

[0039] In the range of [0, splitPointArr array length - 1) of the rownum split point integer array, the query SQL statement is constructed using this SQL template;

[0040] The third query SQL statement template:

[0041] select*from(select rownum no,[column]①from[table_name]②)where no>=splitPointArr[splitPointArr.length-1]③; among them,

[0042] ①column is a list of fields to be queried in the source data table, separated by commas;

[0043] ②table_name is the table name of the source data table;

[0044] ③splitPointArr[splitPointArr.length-1] represents the penultimate element in the rownum split point integer array splitPointArr.

[0045] Furthermore, the Oracle rownum-based data synchronization method of the present invention includes step S5:

[0046] S51. According to the parallelism of the data synchronization task, in the data reading module, a corresponding number of reading threads are opened, and multiple threads execute conditional query SQL statements in parallel. Each reading thread executes a conditional query SQL statement separately to read the source data, and the queried data is stored in the buffer of each thread;

[0047] S52. In the data writing module, the same number of writing threads are started. After obtaining the data from the reading thread buffer, each writing thread independently performs the data insertion operation to achieve the writing of the target end data.

[0048] On the other hand, the present invention also provides an Oracle rownum-based data synchronization device, which implements the steps of the above-mentioned Oracle rownum-based data synchronization method when running.

[0049] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned Oracle rownum-based data synchronization method are implemented.

[0050] In summary, the rownum-based data synchronization method of Oracle in the present invention has the following advantages:

[0051] (1) The present invention uses Oracle's rownum pseudo column as a segmentation field, which can solve the problem that the segmentation field cannot be used to improve the efficiency and performance of data synchronization when the source table does not contain a primary key field or the primary key field is not an integer.

[0052] (2) The data synchronization solution of the present invention breaks through the restriction on the primary key requirement of the source data table. It does not require the source data table to have a primary key or an auto-increment integer field. It has low requirements on the structure of the source data table and is applicable to all Oracle type source tables.

[0053] (3) The present invention uses Oracle's rownum pseudo column field, SAMPLE function and DBMS_RANDOM.VALUE random number to obtain the splitting point of the rownum pseudo column for conditional query, which has good performance and randomness and can obtain more uniform splitting points.

[0054] (4) The present invention designs and constructs three query SQL statement templates, based on which conditional query SQL statements with any degree of parallelism can be generated, which can completely cover all data in the source data table; by setting a reasonable degree of parallelism, the multi-core processing capability of the server is fully utilized, which significantly improves the performance of data synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0056] Figure 1 This is a schematic diagram of query segmentation based on the primary key field in the traditional method.

[0057] Figure 2 Schematic diagram of parallel execution of SQL in the method of the present invention.

[0058] Figure 3 The figure is a flow chart of the overall implementation of the method of the present invention. DETAILED DESCRIPTION

[0059] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. The present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0060] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0061] Embodiment: A data synchronization method based on rownum in Oracle

[0062] The method of the present invention realizes data synchronization based on the ora_rownum pseudo column. The rownum pseudo column is a pseudo column introduced by Oracle. This pseudo column does not exist physically. It is a column added after a result set is queried according to a certain condition and cannot be modified by a user.

[0063] The rownum pseudo column has the following characteristics:

[0064] 1) rownum does not belong to any table and cannot be prefixed with the name of any table;

[0065] 2) rownum is based on the result set;

[0066] 3) rownum always starts from 1;

[0067] 4) rownum is generally only used with < and <=;

[0068] For Oracle, you can use the rownum pseudo column to perform conditional queries.

[0069] The rownum pseudo column is used as follows, assuming that the Oracle sample table data is:

[0070] Table 1 Oracle sample table employees

[0071] NAME AGE DEPARTMENT John Doe 30 IT Alice Smith 24 Teacher Bob Johnson 18 Designer

[0072] 1) For queries where rownum is less than a certain value:

[0073] For the condition that rownum < n (where n is a natural number greater than 1), it always holds. Therefore, the result can be directly queried.

[0074] select rownum,t.*from employees t where rownum<=2;

[0075] The query results are as follows:

[0076] Table 2 Query results of rownum for values less than a certain value

[0077] ROWNUM NAME AGE DEPARTMENT 1 John Doe 30 IT 2 Alice Smith 24 Teacher

[0078] 2) Query of rownum for values greater than a certain value:

[0079] When directly using rownum > n (where n is a natural number greater than 1), no data can be retrieved because rownum is a pseudo-column that always starts from 1. Therefore, the condition rownum > n does not hold. To achieve a conditional query for values greater than a certain value, a subquery needs to be used, and rownum must have an alias; otherwise, no records can be retrieved.

[0080] select*from(select rownum no,name,age,department from employees)whereno>=2;

[0081] Among them, no is the alias of the rownum pseudo-column field.

[0082] The query results are as follows:

[0083] Table 3 Query results of rownum for values greater than a certain value

[0084]

[0085]

[0086] 3) Query of rownum for an interval:

[0087] For querying data where rownum is within a certain interval, a subquery must be used. For example, to query data where rownum is between the second and third rows and includes the second and third rows, we can only achieve this through the following statement. First, let it return the record rows where rownum is less than or equal to 3, and then in the outer query, filter the record rows where the new rownum is greater than or equal to 2.

[0088] select*from(select rownum no,name,age,department from employees whererownum<=3)where no>=2;

[0089] Here, no is an alias for the rownum pseudo-column field.

[0090] The query results are as follows:

[0091] Table 4. Query results of rownum for intervals

[0092] NO NAME AGE DEPARTMENT 2 Alice Smith 24 Teacher 3 Bob Johnson 18 Designer

[0093] Get the segmentation range

[0094] Data segmentation means dividing the original full query into many small conditional queries according to a certain condition, and executing these small conditional queries in parallel through program control. This can improve query efficiency and reduce the time consumption of overall data synchronization.

[0095] In actual application scenarios, many source tables do not have primary key fields, or the primary key fields are not integer types, so the above conditional queries cannot be completed through the primary key. This method uses Oracle's rownum pseudo column (the rownum pseudo column is a feature of all Oracle data tables) to perform conditional queries, which can break through the primary key requirements of the source data table and does not require the source data table to have a primary key or auto-increment integer field, thereby achieving data segmentation to improve concurrency and achieve the purpose of improving data synchronization efficiency.

[0096] To be able to perform conditional query, the core is to determine the screening range of the conditional query, that is, to determine the segmentation range. In traditional practices, such as Figure 1 As shown, when splitting data, it is often necessary to query a self-incrementing numeric field in the source table in advance (such as Figure 1 The minimum and maximum values ​​of the ID field in the source table are obtained, and then each split range is determined based on "step size = (maximum value - minimum value) / number of concurrent connections". This approach requires that the maximum value of a certain auto-increment numeric field in all the data in the current source table be queried in advance. Since there is a period of time between the query time and the actual execution of the data synchronization task, a batch of new data may be inserted into the source table during this period. Therefore, the split range obtained by the advance query cannot cover all the data in the source data table when the data synchronization task is actually executed.

[0097] In order to avoid the above problems, the method adopted by the present invention is to use Oracle's SAMPLE function + Oracle's DBMS_RANDOM package, use the SAMPLE function and DBMS_RANDOM.VALUE random number for sampling to determine the rownum pseudo-column splitting point.

[0098] Oracle's SAMPLE function is a tool used in Oracle database to randomly extract sample data from a table. Its working principle is as follows:

[0099] 1) The SAMPLE function takes a table as input and determines the total number of rows in the table.

[0100] 2) The syntax is SAMPLE(P), where P is the sample size specified by the user, that is, the sampling percentage, and the function calculates the number of rows to be returned. The sample size value must be greater than or equal to 0.000001 and less than 100. If the value is 10, it means sampling 10% of the records in the table at random. If the user does not specify the sample size, the function will return approximately 1% of the rows in the table.

[0101] 3) After determining the number of rows to return, the function uses a random number generator to select those rows.

[0102] 4) The SAMPLE function returns the selected rows to the user as a result set.

[0103] Oracle's DBMS_RANDOM package is a program package used by Oracle database to generate random values ​​or strings. The DBMS_RANDOM.VALUE function returns a random number between 0 and 1 by default when no parameters are entered. The value range of the number is [0,1), including 0 but not 1, and there are 38 digits to the right of the decimal point.

[0104] The specific method of obtaining the rownum segmentation point in the present invention is as follows:

[0105] 1) First, use the SAMPLE function to sample the records of the entire table. In order to make the sampling range cover all data rows in the source table as much as possible, the sample size is set to 99, which means 99% of the random records in the sampling table, that is, sampling is approximately based on all the data in the source table;

[0106] 2) Based on the first step, use order by DBMS_RANDOM.VALUE to sort the DBMS_RANDOM.VALUE column of the result set to get a random result set;

[0107] 3) Based on the second step, use rownum<=p (p is the set parallelism) to filter the number of entries and obtain the rownum result list corresponding to the number and parallelism value;

[0108] 4) Based on the third step, the final rownum result list is sorted in ascending order. Since directly using order by rownum will cause disorder, the final result set must be sorted in the main query.

[0109] Since the above method uses both the SAMPLE function and the DBMS_RANDOM.VALUE random number, its query performance is better and the randomness is also better, and the splitting points of rownum can be obtained more evenly.

[0110] Based on the above design, the query SQL for the split point is constructed as follows:

[0111] select*from(select*from(select rownum no from table_name sample(99.0)order by dbms_random.value)where rownum<=parallelism)t order by t.no asc;

[0112] Among them, table_name is the name of the source Oracle table, and parallelism is the set degree of parallelism.

[0113] Assuming the parallelism is set to 10, the example query SQL is constructed as follows:

[0114] select*from(select*from(select rownum no from employees sample(99.0)order by dbms_random.value)where rownum<=10)t order by t.no asc;

[0115] By executing the above query SQL, a set of ascending rownum split points will be obtained and saved in an integer array splitPointArr, whose array length is 10, such as [103230,206460,309690,412920,516150,619380,722610,825840,929070,1032300].

[0116] Constructing query SQL

[0117] After obtaining the split point of the rownum pseudo column, the SQL statement for concurrent query is constructed next.

[0118] According to the characteristics of rownum and the method of using rownum, in the solution of the present invention, we designed three SQL statement templates to generate all conditional query SQL statements.

[0119] The first SQL template:

[0120] select rownum no,[column]①from[table_name]②where rownum <splitPointArr[0]③;

[0121] Among them, ①column is the list of fields to be queried in the source table, separated by commas, such as name, age, department;

[0122] ②table_name is the name of the source table, such as employees;

[0123] ③splitPointArr[0] is the first element in the rownum split point integer array splitPointArr.

[0124] Combined with the first SQL template, the first query SQL statement example of this solution is constructed as follows:

[0125] select rownum no,name,age,department from employees where rownum<103230;

[0126] This SQL indicates that, in this example, records with rownum in the range of [1,103230) will be filtered out from the source table employees, where rownum always starts from 1 and includes 1 but does not include 103230.

[0127] The second SQL template:

[0128] select*from(select rownum no,[column]①from[table_name]②where rownum<splitPointArr[i+1]③)where no> = splitPointArr[i]④;

[0129] Among them, ①column is the list of fields to be queried in the source table, separated by commas, such as name, age, department;

[0130] ②table_name is the name of the source table, such as employees;

[0131] ③splitPointArr[i+1] represents the i+1th element in the rownum split point integer array splitPointArr, where the value range of i is [0, splitPointArr array length - 1), including 0 but excluding splitPointArr array length - 1;

[0132] ④splitPointArr[i] represents the i-th element in the rownum split point integer array splitPointArr, where the value range of i is [0, splitPointArr array length - 1), including 0 but excluding splitPointArr array length - 1.

[0133] In the range of index subscript [0, splitPointArr array length - 1) of the rownum split point integer array, the query SQL statement is constructed using the SQL template.

[0134] Combined with the second SQL template, the second query SQL statement example of this solution is constructed as follows:

[0135] select*from(select rownum no,name,age,department from employees whererownum <206460)where no> =103230;

[0136] This SQL indicates that in this example, records with rownum in the range of [103230,206460) will be filtered out from the source table employees, which is left closed and right open, including 103230 but not including 206460. In this example, the query SQL statement is constructed according to the second SQL template syntax from the index subscript [0,9) of the array splitPointArr.

[0137] The third SQL template:

[0138] select*from(select rownum no,[column]①from[table_name]②)where no>=splitPointArr[splitPointArr.length-1]③;

[0139] Among them, ①column is the list of fields to be queried in the source table, separated by commas, such as name, age, department;

[0140] ②table_name is the name of the source table, such as employees;

[0141] ③splitPointArr[splitPointArr.length-1] represents the penultimate element in the rownum split point integer array splitPointArr;

[0142] Combined with the third SQL template, the third query SQL statement example of this solution is constructed as follows:

[0143] select*from(select rownum no,name,age,department from employees)whereno>=1032300;

[0144] This SQL indicates that in this example, records with rownum in the range of [1032300, +∞) will be filtered out from the source table employees, where rownum is greater than or equal to 1032300 to positive infinity.

[0145] Through the above three SQL templates, the original full query can be split into multiple small conditional queries. It should be noted that this solution can be used to generate conditional query SQL statements under any degree of parallelism, and because the value range of the third SQL is to positive infinity, as long as the data inserted before executing the query SQL is obtained, there will be no data omissions, and the query range can cover all data in the source table.

[0146] Parallel Execution

[0147] After obtaining the conditional query SQL statement after splitting, the next step is to synchronize the data.

[0148] In order to improve the efficiency and performance of data synchronization, the present invention adopts a parallel execution method, that is, multiple threads are started at the same time, and the query of source-side data and the insertion of target-side data are completed separately in each thread.

[0149] like Figure 2As shown, the scheme designed by the present invention is that, according to the number of set parallelism, in the data reading module, a corresponding number of reading threads are opened, each thread executes a conditional query SQL statement separately, and stores the queried data in the buffer of each thread; in the data writing module, the same number of writing threads are also opened, and after obtaining the data from the previous buffer, the data insertion operation is executed separately in each thread. Through the above scheme, multi-threading can give full play to the multi-core processing power of the server, while improving the concurrency of data synchronization, speeding up the overall query and write speed of data synchronization, and reducing the overall synchronization time.

[0150] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. An Oracle rownum-based data synchronization method, characterized in that: The method comprises: S1. Set the parallelism of data synchronization tasks according to the data size of the source data table and the source database load; S2. Use the rownum pseudo column of the Oracle database as the segmentation field, use the SAMPLE function to extract samples of the source data, and then use the DBMS_RANDOM.VALUE random number to sort, and obtain the segmentation point array of the rownum pseudo column for conditional query; S3. Use the rownum pseudo column to construct a source-side data query SQL statement template; S4. Generate a conditional query SQL statement using the query SQL statement template according to the split point array of the rownum pseudo column for conditional query; S5. Start a corresponding number of threads according to the parallelism of the data synchronization task. Multiple threads execute conditional query SQL statements in parallel. Each thread executes a conditional query SQL statement to read the source data. After each thread obtains the query results, it writes the target data respectively.

2. The Oracle rownum-based data synchronization method according to claim 1, characterized in that: Step S2 includes: S21. First use the SAMPLE function to sample the records of the entire source data table; S22. Based on step S21, use order by DBMS_RANDOM.VALUE to sort the DBMS_RANDOM.VALUE column of the result set to obtain a random result set; S23. Based on step S22, use rownum<=p to filter the number of entries, where p is the set parallelism, and obtain a rownum result list corresponding to the number and parallelism value; S24. Based on step S23, the final rownum result list is sorted in ascending order.

3. The Oracle rownum-based data synchronization method according to claim 2, characterized in that: In step S21, the sample size is set to 99 when the SAMPLE function is used to sample the records of the entire source data table.

4. The Oracle rownum-based data synchronization method according to claim 3, characterized in that: The query SQL for constructing the split point of the rownum pseudo column in step S2 is: select*from(select*from(select rownum no from table_name sample(99.0)order by dbms_random.value)where rownum<=parallelism)t order by t.no asc; Among them, table_name is the name of the source data table, and parallelism is the set parallelism; By executing the above query SQL, a set of ascending rownum pseudo-column split points will be obtained and saved in an integer array splitPointArr.

5. The Oracle rownum-based data synchronization method according to claim 2, characterized in that: The sorting of the final rownum result list in step S24 is performed in the main query.

6. The Oracle rownum-based data synchronization method according to claim 1, characterized in that: In step S3, the rownum pseudo column is used to construct a source-side data query SQL statement template, and the query SQL statement template includes the following three types: The first query SQL statement template: select rownum no,[column]①from[table_name]②where rownum<splitPointArr[0]③; Among them, ① column is a list of fields to be queried in the source data table, separated by commas; ② table_name is the table name of the source data table; ③ splitPointArr[0] is the first element in the integer array splitPointArr of rownum split points; The second query SQL statement template: select*from(select rownum no,[column]①from[table_name]②where rownum<splitPointArr[i+1]③)where no>=splitPointArr[i]④; Among them, ① column is a list of fields to be queried in the source data table, separated by commas; ② table_name is the table name of the source data table; ③ splitPointArr[i+1] represents the (i+1)-th element in the integer array splitPointArr of rownum split points, where the value range of i is [0, splitPointArr array length - 1), including 0 but not including splitPointArr array length - 1; ④ splitPointArr[i] represents the i-th element in the integer array splitPointArr of rownum split points, where the value range of i is [0, splitPointArr array length - 1), including 0 but not including splitPointArr array length - 1; The third query SQL statement template: select*from(select rownum no,[column]①from[table_name]②)where no>=splitPointArr[splitPointArr.length-1]③; Among them, ① column is a list of fields to be queried in the source data table, separated by commas; ② table_name is the table name of the source data table; ③ splitPointArr[splitPointArr.length-1] represents the last element in the integer array splitPointArr of rownum split points.

7. The Oracle rownum-based data synchronization method according to claim 1, characterized in that: Step S5 includes: S51. According to the parallelism of the data synchronization task, in the data reading module, start the corresponding number of reading threads, and execute the conditional query SQL statements in parallel by multiple threads. Each reading thread executes a section of the conditional query SQL statement alone to realize the reading of the source data, and store the queried data into the buffer of each thread; S52. In the data writing module, the same number of writing threads are started. After obtaining the data from the reading thread buffer, each writing thread independently performs the data insertion operation to achieve the writing of the target end data.

8. An Oracle rownum-based data synchronization device, characterized in that: When the device is running, the steps of the Oracle rownum-based data synchronization method described in any one of claims 1 to 7 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the steps of the Oracle rownum-based data synchronization method according to any one of claims 1 to 7 are implemented.

Citation Information

Cited By

  • Method, device and computer program product for reading database data

    CN119149574A