CN102193938A - Method and device for acquiring incremental data - Google Patents
Method and device for acquiring incremental data Download PDFInfo
- Publication number
- CN102193938A CN102193938A CN 201010122947 CN201010122947A CN102193938A CN 102193938 A CN102193938 A CN 102193938A CN 201010122947 CN201010122947 CN 201010122947 CN 201010122947 A CN201010122947 A CN 201010122947A CN 102193938 A CN102193938 A CN 102193938A
- Authority
- CN
- China
- Prior art keywords
- incremental data
- original table
- preset time
- database
- buffer memory
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a device for acquiring incremental data, and belongs to the field of databases. The method comprises the following steps of: checking information, corresponding to an original table, in a cache at every preset time; and if incremental data subjected to preset target operation exists in the information, recording the incremental data. The invention also discloses a device. The device comprises a checking module, a recording module, an establishing module and a storage module. By the method and the device, the incremental data can be acquired without invading the original table, so the resources of the database can be saved; furthermore, when the database is operated frequently, a system can preset an interval of acquisition of the incremental data according to a running situation and automatically select the time for acquiring the incremental data; moreover, the load of the database can be balanced and the user experience is enhanced.
Description
Technical field
The present invention relates to database field, the method and apparatus that particularly a kind of incremental data is gathered.
Background technology
In database, raw data is inserted, deleted or operation such as renewal through regular meeting, the object that carries out these operations can be described as incremental data, in some cases, the incremental data of some original tables need be synchronized in some other table, the data of these tables are consistent; Also have certain situation, need the variation of some original tables of detail knowledge, be used for data analysis and excavation, generally can carry out the incremental data collection in these cases.
The following two kinds of methods of the general use of incremental data collection of the prior art:
First method is based on the incremental data collection of Trigger, and when the operation inserting in the database, upgrade or delete, database can automatically perform predefined stored Procedure, invades original table, and the incremental data in the original table is collected in the backup sheet;
Second method is based on the incremental data collection of timestamp field, is the timestamp field that representative is operated the time in the inquiry original table data, and the incremental data that produces in the fixed time section is collected in the backup sheet.
After prior art was analyzed, the inventor found that prior art has following shortcoming at least:
Above-mentioned dual mode all is that the original table in the database is carried out direct control, needs to invade original table and gathers, and influences the process of system, poor user experience,
Wherein, first kind of mode can increase the database load when operation is frequent, system is caused performance impact.
The second way requires original table must have the timestamp field, and limitation is big, and can't carry out record to the increment that has carried out deletion action.
Summary of the invention
The method and apparatus that the embodiment of the invention provides a kind of incremental data to gather.Described technical scheme is as follows:
The method that a kind of incremental data is gathered comprises:
Check in the buffer memory and the corresponding information of described original table every Preset Time;
If have the incremental data of carrying out the goal-selling operation in the described data, then write down described incremental data.
In Preset Time inspection buffer memory, specifically comprise with the corresponding information of described original table:
In Preset Time inspection buffer memory,, comprise the time that interior operation of being done of described Preset Time and described operation take place in the described version information with the corresponding version information of described original table.
Described inspection is finished by calling flashback query.
Also comprise after writing down described incremental data:
Described increment is stored in the described incremental data backup sheet.
Also comprise before storing into described increment in the described incremental data backup sheet: set up the incremental data backup sheet according to original table and default object run.
The device that a kind of incremental data is gathered comprises:
Check module, be used for checking buffer memory and the corresponding information of described original table every Preset Time;
There is the incremental data of carrying out the goal-selling operation in logging modle if be used for described data, then writes down described incremental data.
Described inspection module specifically is used for checking buffer memory and the corresponding version information of described original table every Preset Time, comprises the time that interior operation of being done of described Preset Time and described operation take place in the described version information.
Described inspection is finished by calling flashback query.
Described device also comprises:
Memory module is used for storing described increment into described incremental data backup sheet.
Described device also comprises:
Set up module, be used for setting up the incremental data backup sheet according to original table and default object run.
The beneficial effect of the technical scheme that the embodiment of the invention provides is:
By the data in the buffer memory being monitored so that original table is carried out the incremental data collection, do not need to invade original table and can carry out the incremental data collection, save the resource of database, and when database manipulation is frequent, because system can be according to the interval time of the default incremental data collection of ruuning situation, the system that makes can choose the right moment voluntarily and carry out the incremental data collection, but the load of equalization data storehouse has improved user experience.
Description of drawings
In order to be illustrated more clearly in the embodiment of the invention or technical scheme of the prior art, to do to introduce simply to the accompanying drawing of required use in embodiment or the description of the Prior Art below, apparently, accompanying drawing in describing below only is some embodiments of the present invention, for those of ordinary skills, under the prerequisite of not paying creative work, can also obtain other accompanying drawing according to these accompanying drawings.
Fig. 1 is the process flow diagram of a kind of incremental data that the embodiment of the invention 1 the provides method of gathering;
Fig. 2 is the process flow diagram of a kind of incremental data that the embodiment of the invention 2 the provides method of gathering;
Fig. 3 is the schematic representation of apparatus that a kind of incremental data that the embodiment of the invention 3 provides is gathered.
Embodiment
For making the purpose, technical solutions and advantages of the present invention clearer, embodiment of the present invention is described further in detail below in conjunction with accompanying drawing.
Embodiment 1
For carrying out improving system performance when incremental data is gathered, save database resource, the invention provides the method that a kind of incremental data is gathered, comprising:
101: check in the buffer memory and the corresponding information of this original table every Preset Time;
102:, then write down this incremental data if there is the incremental data of carrying out the goal-selling operation in these data.
By the data in the buffer memory being monitored so that original table is carried out the incremental data collection, do not need to invade original table and can carry out the incremental data collection, save the resource of database, and when database manipulation is frequent, because system can be according to the interval time of the default incremental data collection of ruuning situation, the system that makes can choose the right moment voluntarily and carry out the incremental data collection, but the load of equalization data storehouse has improved user experience.
Embodiment 2
For carrying out improving system performance when incremental data is gathered, save database resource, the invention provides the method that a kind of incremental data is gathered, specifically comprise:
201: the incremental data backup sheet is set up in original table and the default object run of carrying out the incremental data collection as required;
Wherein, this original table can be by default, also can be by user's appointment, and the embodiment of the invention is not done concrete qualification to this.
Wherein, this default object run can be the operations such as increase, deletion or modification that the field of original table is carried out.
202: check the corresponding version information of this original table that writes down in the buffer memory every Preset Time;
Wherein, this Preset Time is that system sets according to the running status of self, and when system was busy, this Preset Time can be adjusted into long slightly, and when system was idle, this Preset Time can be adjusted into short slightly.
Particularly, the process of this inspection can be finished by calling flashback query, this flashback function is one of database function, Flashback is the characteristic that Oracle just begins to provide from 9i, in 9i, utilize Oracle to inquire about the characteristics of many versions unanimity, realize from roll-back segment, reading the table data of operating in the certain hour, can be used to carry out the data comparison, perhaps revise the unexpected misdata that causes of submitting to, this characteristic is also referred to as Flashback Query.
Wherein, this version information is used for the data of submitting in the past period are monitored, and this version information comprises time of the operation done in the Preset Time and this operation generation or SCN (System Change Number, system's change number).
SCN is interpreted as an inner synchronous clock in Oracle, it is a very important data structure in the database.Its definition database is at certain definite version of submitting to constantly.When affairs were submitted to, it was endowed the SCN of unique sign affairs.
203:, then analyze this version information if version information changes;
Particularly, when analyzing version information, can check content in order to the row of recording operation, in database, this row can be VERSIONS_STARTSCN/VERSIONS_STARTTIME, SCN when these row are used for recording operation or time,, represent that this line item creates outside query context if be empty;
Can also check VERSIONS_ENDSCN/VERSIONS_ENDTIME, these row are used for writing down SCN or the time when losing efficacy, if be empty, the declare record current time exists in current table, and is perhaps deleted, can cooperate VERSIONS_OPERATION to be listed as, if the VERSIONS_OPERATION train value is D, illustrate that these row are deleted, if this classifies sky as, then declare record does not have operation during this period of time.
204: whether in this version information have the incremental data of carrying out default object run, if execution in step 205 if not, is returned execution in step 202 if judging;
205: write down this incremental data;
Particularly, this incremental data extracts from version information, and those skilled in the art can know that this extracting method can have multiple, does not do at this and gives unnecessary details.
206:, set up database store process according to the incremental data that is write down;
Particularly, in database, generally procedure is set up in this process utilization and realize setting up database store process, when setting up this storing process, can select the content that to store according to being provided with of user's requirement or system, determine the scope of this content, for example, the content of object run is that job number is the employee of 1-50, when setting up storing process, can delimit a storage scope or to the storage content limit, this scope can be the employee etc. of 1-25 for job number, and it can be the female employee etc. of 1-25 for job number that this content to storage limits.
207:, this incremental data is stored in the incremental data backup sheet of having set up according to database store process.
Need to prove that this incremental data backup sheet is to be used for representing that incremental data reaches the form of the operation that data are carried out, and does not have concrete prescribed form, the form of backup sheet can change according to the setting of database.
With the method for calling for Flashback Query is that example describes, for example:
An original table ORGIN_TABLE is arranged, field is respectively (ID NUMBER, NAME VARCHAR (20), SEXVARCHAR (10)), now this table is deleted (D), inserts (I), upgraded (U) operation, to delete, insert, upgrade operation and be made as object run, set up the incremental data backup sheet according to object run and original table.
Data manipulation SQL is as follows:
SQL＞delete?from?orgin_table；
SQL＞commit；
SQL＞insert?into?orgin_table?values(1，′name1′，′male′)；
SQL＞commit；
SQL＞insert?into?orgin_table?values(2，′name1′，′male′)；
SQL＞commit；
SQL＞update?orgin_table?set?name＝′name2′，sex＝′female′where?id＝2；
SQL＞commit；
According to above be stored in the buffer memory information as can be known, this original table has carried out the object run of appointment;
Inquire about SCN number this moment again:
SQL＞select?dbms_flashback.get_system_change_number?from?dual；
Obtain following result:
| GET_SYSTEM_CHANGE_NUMBER |
| 1213530 |
Just can come out to the change data extract of above database manipulation this moment:
SQL＞select?VERSIONS_STARTSCN，to_char(VERSIONS_STARTTIME，′yyyy-mm-ddhh24:mi:ss′)VERSIONS_STARTTIME，VERSIONS_OPERATION，id，name，sex?fromorgin_table?versions?between?scn?1212530?and?1213530；
The result who obtains is the incremental data that collects, and is listed as follows:
| ?VERSIONS_ST?ARTSCN | VERSIONS_STA RTTIME | VERSIONS_OPE RATION | id | name | sex |
| ?1213529 | 2009-12-18 10:17:22 | U | 2 | name2 | female |
| ?1213526 | 2009-12-18 10:17:22 | I | 2 | name1 | male |
| ?1213523 | 2009-12-18 | I | 1 | name1 | male |
| 10:17:19 | |||||
| 1213521 | 2009-12-18 10:17:16 | D | 2 | name2 | female |
| 1213521 | 2009-12-18 10:17:16 | D | 1 | name1 | male |
By the data in the buffer memory being monitored so that original table is carried out the incremental data collection, do not need to invade original table and can carry out the incremental data collection, save the resource of database, and when database manipulation is frequent, because system can be according to the interval time of the default incremental data collection of ruuning situation, the system that makes can choose the right moment voluntarily and carry out the incremental data collection, but the load of equalization data storehouse has improved user experience.
Embodiment 3
For carrying out improving system performance when incremental data is gathered, save database resource, the invention provides the device that a kind of incremental data is gathered, referring to Fig. 3, this device comprises:
Check module 301, be used for checking buffer memory and the corresponding information of this original table every Preset Time;
This inspection module 301 specifically is used for checking buffer memory and the corresponding version information of this original table every Preset Time, comprises the time that the interior operation of being done of this Preset Time and this operation take place in this version information.
Wherein, this inspection is finished by calling flashback query.
There is the incremental data of carrying out the goal-selling operation in logging modle 302 if be used for these data, then writes down this incremental data.
This device also comprises:
This device also comprises:
Set up module 300, be used for setting up the incremental data backup sheet according to original table and default object run.
By the data in the buffer memory being monitored so that original table is carried out the incremental data collection, do not need to invade original table and can carry out the incremental data collection, save the resource of database, and when database manipulation is frequent, because system can be according to the interval time of the default incremental data collection of ruuning situation, the system that makes can choose the right moment voluntarily and carry out the incremental data collection, but the load of equalization data storehouse has improved user experience.
The device that present embodiment provides belongs to same design with method embodiment, and its specific implementation process sees method embodiment for details, does not repeat them here.
The all or part of of the technique scheme that the embodiment of the invention provides can be finished by the relevant hardware of programmed instruction, described program can be stored in the storage medium that can read, and this storage medium comprises: various media that can be program code stored such as ROM, RAM, magnetic disc or CD.
The above only is preferred embodiment of the present invention, and is in order to restriction the present invention, within the spirit and principles in the present invention not all, any modification of being done, is equal to replacement, improvement etc., all should be included within protection scope of the present invention.
Claims (10)
1. the method that incremental data is gathered is characterized in that, comprising:
Check in the buffer memory and the corresponding information of described original table every Preset Time;
If have the incremental data of carrying out the goal-selling operation in the described data, then write down described incremental data.
2. method according to claim 1 is characterized in that, checks in the buffer memory every Preset Time specifically to comprise with the corresponding information of described original table:
In Preset Time inspection buffer memory,, comprise the time that interior operation of being done of described Preset Time and described operation take place in the described version information with the corresponding version information of described original table.
3. method according to claim 2 is characterized in that, described inspection is finished by calling flashback query.
4. method according to claim 1 is characterized in that, writes down described incremental data and also comprises afterwards:
Described increment is stored in the described incremental data backup sheet.
5. method according to claim 4 is characterized in that, described increment is stored in the described incremental data backup sheet also comprise before: set up the incremental data backup sheet according to original table and default object run.
6. the device that incremental data is gathered is characterized in that, comprising:
Check module, be used for checking buffer memory and the corresponding information of described original table every Preset Time;
There is the incremental data of carrying out the goal-selling operation in logging modle if be used for described data, then writes down described incremental data.
7. device according to claim 6, it is characterized in that, described inspection module specifically is used for checking buffer memory and the corresponding version information of described original table every Preset Time, comprises the time that interior operation of being done of described Preset Time and described operation take place in the described version information.
8. device according to claim 7 is characterized in that, described inspection is finished by calling flashback query.
9. device according to claim 6 is characterized in that, described device also comprises: memory module is used for storing described increment into described incremental data backup sheet.
10. device according to claim 9 is characterized in that, described device also comprises: set up module, be used for setting up the incremental data backup sheet according to original table and default object run.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN 201010122947 CN102193938A (en) | 2010-03-01 | 2010-03-01 | Method and device for acquiring incremental data |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN 201010122947 CN102193938A (en) | 2010-03-01 | 2010-03-01 | Method and device for acquiring incremental data |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN102193938A true CN102193938A (en) | 2011-09-21 |
Family
ID=44602018
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN 201010122947 Pending CN102193938A (en) | 2010-03-01 | 2010-03-01 | Method and device for acquiring incremental data |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN102193938A (en) |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102970373A (en) * | 2012-12-03 | 2013-03-13 | 亚信联创科技（中国）有限公司 | Data synchronization method, device and system |
| CN104216987A (en) * | 2014-09-04 | 2014-12-17 | 浪潮通用软件有限公司 | Timestamp-based method for capturing incremental data and supporting delete operation |
| CN105376314A (en) * | 2015-11-18 | 2016-03-02 | 深圳市博安达信息技术股份有限公司 | Method and device for extracting environmental monitoring and analyzing data into LIMS |
| CN105446824A (en) * | 2014-05-28 | 2016-03-30 | 无锡华润微电子有限公司 | Table increment obtaining method and remote data backup method |
| CN109086382A (en) * | 2018-07-25 | 2018-12-25 | 上海达梦数据库有限公司 | A kind of method of data synchronization, device, equipment and storage medium |
| CN110050268A (en) * | 2016-09-30 | 2019-07-23 | 深圳市华傲数据技术有限公司 | Data processing method and device based on increment |
| CN110968569A (en) * | 2019-12-19 | 2020-04-07 | 广州品唯软件有限公司 | Database management method, database management device, and storage medium |
| CN112883119A (en) * | 2021-04-30 | 2021-06-01 | 腾讯科技（深圳）有限公司 | Data synchronization method and device, computer equipment and computer readable storage medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101576918A (en) * | 2009-06-19 | 2009-11-11 | 用友软件股份有限公司 | Data buffering system with load balancing function |
| CN101635904A (en) * | 2008-07-22 | 2010-01-27 | 中国移动通信集团吉林有限公司 | System and method for processing data |
-
2010
- 2010-03-01 CN CN 201010122947 patent/CN102193938A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101635904A (en) * | 2008-07-22 | 2010-01-27 | 中国移动通信集团吉林有限公司 | System and method for processing data |
| CN101576918A (en) * | 2009-06-19 | 2009-11-11 | 用友软件股份有限公司 | Data buffering system with load balancing function |
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102970373A (en) * | 2012-12-03 | 2013-03-13 | 亚信联创科技（中国）有限公司 | Data synchronization method, device and system |
| CN102970373B (en) * | 2012-12-03 | 2015-11-11 | 亚信科技（中国）有限公司 | A kind of method of data synchronization, Apparatus and system |
| CN105446824B (en) * | 2014-05-28 | 2019-11-05 | 无锡华润微电子有限公司 | Table increment acquisition methods and long-distance data backup method |
| CN105446824A (en) * | 2014-05-28 | 2016-03-30 | 无锡华润微电子有限公司 | Table increment obtaining method and remote data backup method |
| CN104216987A (en) * | 2014-09-04 | 2014-12-17 | 浪潮通用软件有限公司 | Timestamp-based method for capturing incremental data and supporting delete operation |
| CN105376314B (en) * | 2015-11-18 | 2019-04-16 | 深圳博沃智慧科技有限公司 | A kind of method and device that environmental monitoring and analysis data are extracted to LIMS |
| CN105376314A (en) * | 2015-11-18 | 2016-03-02 | 深圳市博安达信息技术股份有限公司 | Method and device for extracting environmental monitoring and analyzing data into LIMS |
| CN110050268A (en) * | 2016-09-30 | 2019-07-23 | 深圳市华傲数据技术有限公司 | Data processing method and device based on increment |
| CN109086382A (en) * | 2018-07-25 | 2018-12-25 | 上海达梦数据库有限公司 | A kind of method of data synchronization, device, equipment and storage medium |
| CN109086382B (en) * | 2018-07-25 | 2021-10-15 | 上海达梦数据库有限公司 | Data synchronization method, device, equipment and storage medium |
| CN110968569A (en) * | 2019-12-19 | 2020-04-07 | 广州品唯软件有限公司 | Database management method, database management device, and storage medium |
| CN110968569B (en) * | 2019-12-19 | 2023-12-29 | 广州品唯软件有限公司 | Database management method, database management device, and storage medium |
| CN112883119A (en) * | 2021-04-30 | 2021-06-01 | 腾讯科技（深圳）有限公司 | Data synchronization method and device, computer equipment and computer readable storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN102193938A (en) | Method and device for acquiring incremental data | |
| CN101499069B (en) | Internal memory database file updating method and apparatus | |
| CA2279119C (en) | Heuristic-based conditional data indexing | |
| US8825601B2 (en) | Logical data backup and rollback using incremental capture in a distributed database | |
| CN110879813B (en) | Binary log analysis-based MySQL database increment synchronization implementation method | |
| US7580941B2 (en) | Automated logical database design tuning | |
| US11520772B2 (en) | Tracking change data in a database | |
| US9563538B2 (en) | Code path tracking | |
| US7099897B2 (en) | System and method for discriminatory replaying of log files during tablespace recovery in a database management system | |
| US20070150488A1 (en) | System and method for migrating databases | |
| US7406489B2 (en) | Apparatus, system and method for persistently storing data in a data synchronization process | |
| CN103345518A (en) | Self-adaptive data storage management method and system based on data block | |
| US20080140627A1 (en) | Method and apparatus for aggregating database runtime information and analyzing application performance | |
| US7698319B2 (en) | Database system management method, database system, database device, and backup program | |
| EP2797013B1 (en) | Database update execution according to power management schemes | |
| CN105630862A (en) | Set-oriented visibility state retrieval scheme | |
| US20020161735A1 (en) | Modification system for supporting localized data changes in a mobile device | |
| CN104142930A (en) | Universal Delta data loading technology | |
| CN109086382B (en) | Data synchronization method, device, equipment and storage medium | |
| CN105808653A (en) | User label system-based data processing method and device | |
| CN111367994A (en) | Method and system for synchronously backing up incremental data of database | |
| CN110175206A (en) | Intellectual analysis operational approach, system and medium for multiple database separation | |
| CN113094442B (en) | Full data synchronization method, device, equipment and medium | |
| CN109101368B (en) | Data processing method and device | |
| Zhou et al. | An ETL strategy for real-time data warehouse |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C12 | Rejection of a patent application after its publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20110921 |