CN101493842A - Method for deleting data in bulk - Google Patents
Method for deleting data in bulk Download PDFInfo
- Publication number
- CN101493842A CN101493842A CNA2009101055462A CN200910105546A CN101493842A CN 101493842 A CN101493842 A CN 101493842A CN A2009101055462 A CNA2009101055462 A CN A2009101055462A CN 200910105546 A CN200910105546 A CN 200910105546A CN 101493842 A CN101493842 A CN 101493842A
- Authority
- CN
- China
- Prior art keywords
- data
- retention
- database
- former table
- retention data
- 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
Abstract
The invention discloses a method for data deletion in batch in an ORACLE database, which has the following steps: A. a where condition for retention data is analyzed according to deletion conditions; B. an exp command is used for exporting retention data meeting the where condition in a compression way; C. the original table is cleared; and D. the retention data is exported back to the database. In step C, the original table is cleared in the way of a drop original table and a truncate original table, compression of high water line and recovery of database table space can be realized. The method deletes data in batch under the conditions of batch data, has high deletion efficiency, performs disk efragmentation in the data table, and completes the compression operation of the high water line in the process of data deletion in one step without any additional database operations.
Description
Technical field
The invention belongs to database data deletion field, be specifically related in a kind of oracle database the method for deleted data in enormous quantities under the big data quantity situation.
Background technology
In the communications field, performance management and alarm management are two important functions of telecom network management system, also are to produce two more functions of data volume in the network management system.After webmaster operation a period of time (such as 2 years), on-the-spot webmaster need carry out edition upgrading, wherein the upgrading to the network management data storehouse is a most important link the most consuming time in the webmaster edition upgrading link, the time that a large amount of history alarm data that exist and performance statistic can increase database upgrade greatly in the network management data storehouse, occupy a large amount of disk spaces, yet major part may not have use value in these historical datas, therefore generally before network management upgrading, some cleaning works are carried out to historical data in the capital, the data that only keep nearest a period of time (usually being three months), so both discharged database space, promote upgrading efficient again greatly, reduced the upgrading failed probability.
For the oracle database, existing big data quantity delet method has following several:
1. deletion is in batches submitted to one by one
The shortcoming of this method is that efficient is lower, and the database high-water line can't directly compress.
2. the data of Bao Liuing are transferred to earlier in the temporary table, and the former table of truncate is retracted data from temporary table, the deletion temporary table
The shortcoming of this method is to reach hundreds of thousands when the data volume that keeps, and when comprising BLOB, CLOB field, efficient is lower, when raw data is transferred in the temporary table, can take bigger database table space.
In summary, in the database deleted data method under the existing big data quantity situation, there is low and other deficiency and the defective that can't directly compress as the database high-water line of deletion efficiency, so be necessary to be improved.
Summary of the invention
The object of the present invention is to provide the method for deleted data in enormous quantities in a kind of ORACLE database, it is low to solve in the prior art deletion efficiency effectively, the problem that the deletion time is long, and can realize that in delete procedure disk fragments is put in order and the compression of high-water line.
For solving the problems of the technologies described above, the present invention is achieved by the following technical solutions:
The method of deleted data in enormous quantities comprises the steps: in a kind of ORACLE database
A, go out the where condition of retention data according to the deletion condition analysis;
B, use exp order are derived with the retention data that compress mode will satisfy the where condition;
C, empty former table;
D, retention data is led back in the database.
In the said method, described steps A is specially: the where condition (all data are complete or collected works in the table, and retention data is the supplementary set of deleted data set) that goes out retention data according to the situation analysis of the condition of the data that will delete and available data.
In the said method, described step B is specially: with the where condition of the steps A query options parameter as the exp order, make exp command selection item parameter c ompress=y simultaneously, retention data is derived.
In the said method, described step C is specially: the mode with former table of drop or the former table of truncate empties former table, and dual mode efficient is all very high, can both realize the compression of high-water line and the recovery in database table space.
In the said method, described step D is specially: if step C adopts the mode of the former table of drop to empty former table, then use the former list structure in imp order data reconstruction storehouse, and retention data is led in the former table of getting back to reconstruction; If step C adopts the mode of the former table of truncate to empty former table, then use the imp order that retention data is led to get back in the former table of database and get final product, do not need table to rebuild.
Said method also comprises step e: after successfully leading back retention data, if do not need to preserve the retention data of derivation, then delete the retention data of deriving.
Use the method for deleted data in enormous quantities in the ORACLE database of the present invention to have following beneficial effect:
The inventive method is deleted data in enormous quantities under the big data quantity situation, the deletion efficiency height, and with the disk fragments arrangement of database table, the high-water line squeeze operation once realizes finishing in the process of deleted data, need not carry out extra database manipulation.
Description of drawings
Fig. 1 is the schematic flow sheet of data-erasure method of the present invention;
Fig. 2 is the flow direction and the view of database table data of the present invention.
Embodiment
In order to understand the present invention better, the present invention is done to describe further below in conjunction with the drawings and specific embodiments.
See also Fig. 1, data-erasure method flow process of the present invention comprises the steps:
100, data deletion is prepared: carry out the record sum statistics of current delete list and the record quantity statistics that will delete, with the data volume of clear and definite current deletion task;
102, whether be suitable for the judgement of this method: have the deletion efficiency of following two kinds of situation the inventive method not high, should adopt other delet method of 104 flow processs;
104, if the deletion total data is then directly used the truncate statement, efficient is higher; If the data volume that will delete is less, then directly can realize with the delete statement;
106, obtain the where condition of retention data in the analytical table: the where condition (all data are complete or collected works in the table, and retention data is the supplementary set of deleted data set) that goes out retention data according to the situation analysis of the condition of the data that will delete and available data; Such as deleting the history alarm data of 2008-7-01 before day, original cancel statement is:
Delete?from?HISTORYALARM?where?happentime＜to_date(‘2008-07-01 00:00:00’，‘YYYY-MM-DD?HH24:MI:SS’)；
The where condition that can obtain retention data is:
where?happentime＞＝to_date(‘2008-07-01 00:00:00’，‘YYYY-MM-DD?HH24:MI:SS’)；
108, use the table backup mode of exp order to satisfy to drill the record of making the where condition that obtains to derive: the where condition of 106 steps is as query options parameter, with compress mode (options parameter c ompress=y), the retention data in the deleted data table is exported on the disk space simultaneously; Export (swf) command is as follows:
Exp user/pwd query=where condition tables=table name compress=y file=export;
It should be noted that in the where condition generally all can have the space, and will be seen as several command line parameters under the order line, need whole where condition be bracketed with single quotation marks or double quotation marks;
110, the former table of drop or empty former table record: emptying former table has several modes, and efficient is all very high, can both realize the compression of high-water line and the recovery in database table space, as
1) mode of the direct former table of drop when retention data is led back in operation, is used the former list structure in imp order data reconstruction storehouse earlier, uses the sql:drop table HISTORYALARM of DDL;
2) mode of the former table of use truncate when retention data is led back in operation, as long as import data, does not need table to rebuild, the sql:truncate table HISTORYALARM of use DDL;
112, use the imp order that retention data is led back database:
Imp user/pwd fromuser=user tables=table name file=export ignore=y;
114, according to circumstances whether decision deletes the retention data of derivation;
If 116 do not need to preserve the retention data of derivation, then delete the retention data of deriving.
118, deleted data success, and the compression of successful fulfillment database table high-water line and table space defragmentation.
See also Fig. 2, this figure is the flow direction and the view of database table data of the present invention, and it is explained as follows in detail:
State before state 1, the deletion, data are divided into the data (being retention data) of the data (being deleted data) that will delete and needs reservation in the table, and wherein the amount of data total amount and deleted data all is very big data volume;
State 2, retention data is derived, owing to use the mode of compression, and do not need additionally to take the database data table space, as long as take the operating system disk space, efficient is higher, failed probability is lower;
Behind the former table of state 3, truncate or drop, the database table space is reclaimed;
State 4, retention data is led back database table, because the database table space reclamation is finished, the table space that imports Database Requirements is enough certainly;
Only surplus retention data in state 5, the database table, and high-water line has obtained compression.
Data deletion in enormous quantities under the big data quantity situation uses traditional delet method in the oracle database, and deletion efficiency is lower.Use the inventive method instead, improvement deletion speed that can be bigger improves deletion efficiency, and realized the compression of the high-water line of database table in delete procedure.
The above only is preferred embodiment of the present invention;, not should be pointed out that for the person of ordinary skill of the art in order to restriction the present invention; any modification of being done within every the spirit and principles in the present invention, be equal to and replace or improvement etc., all should be included within protection scope of the present invention.
Claims (5)
1. the method for deleted data in enormous quantities in the ORACLE database is characterized in that, comprises the steps:
A, go out the where condition of retention data according to the deletion condition analysis;
B, use exp order are derived with the retention data that compress mode will satisfy the where condition;
C, empty former table;
D, retention data is led back in the database.
2. the method for deleted data in enormous quantities in the ORACLE database according to claim 1 is characterized in that described step B is specially:
With the where condition of steps A query options parameter as the exp order, make exp command selection item parameter c ompress=y simultaneously, retention data is derived.
3. the method for deleted data in enormous quantities in the ORACLE database according to claim 1 and 2 is characterized in that described step C is specially:
Mode with former table of drop or the former table of truncate empties former table.
4. the method for deleted data in enormous quantities in the ORACLE database according to claim 3 is characterized in that described step D is specially:
If step C adopts the mode of the former table of drop to empty former table, then use the former list structure in imp order data reconstruction storehouse, and retention data is led in the former table of getting back to reconstruction; If step C adopts the mode of the former table of truncate to empty former table, then use the imp order that retention data is led and get back in the former table of database.
5. the method for deleted data in enormous quantities is characterized in that in the ORACLE database according to claim 4, also comprises step e: after successfully leading back retention data, if do not need to preserve the retention data of derivation, then delete the retention data of deriving.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2009101055462A CN101493842A (en) | 2009-02-20 | 2009-02-20 | Method for deleting data in bulk |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2009101055462A CN101493842A (en) | 2009-02-20 | 2009-02-20 | Method for deleting data in bulk |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN101493842A true CN101493842A (en) | 2009-07-29 |
Family
ID=40924444
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CNA2009101055462A Pending CN101493842A (en) | 2009-02-20 | 2009-02-20 | Method for deleting data in bulk |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101493842A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105095004A (en) * | 2015-06-29 | 2015-11-25 | 联想(北京)有限公司 | Message processing method and electronic device |
| CN106383897B (en) * | 2016-09-28 | 2018-02-16 | 平安科技（深圳）有限公司 | Database volume computational methods and device |
| WO2018127116A1 (en) * | 2017-01-09 | 2018-07-12 | 腾讯科技（深圳）有限公司 | Data cleaning method and apparatus, and computer-readable storage medium |
| CN110825413A (en) * | 2019-11-04 | 2020-02-21 | 江苏金智教育信息股份有限公司 | Database upgrading method and device and application deployment upgrading method and device |
| CN111400321A (en) * | 2020-03-23 | 2020-07-10 | 上海新炬网络技术有限公司 | Method for automatically recycling high water level based on ORAC L E database |
| CN113468167A (en) * | 2020-03-31 | 2021-10-01 | 中国移动通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
| CN116257531A (en) * | 2023-05-16 | 2023-06-13 | 广州图灵科技有限公司 | Database space recovery method |
-
2009
- 2009-02-20 CN CNA2009101055462A patent/CN101493842A/en active Pending
Cited By (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105095004A (en) * | 2015-06-29 | 2015-11-25 | 联想(北京)有限公司 | Message processing method and electronic device |
| CN105095004B (en) * | 2015-06-29 | 2021-02-19 | 联想(北京)有限公司 | Information processing method and electronic equipment |
| CN106383897B (en) * | 2016-09-28 | 2018-02-16 | 平安科技（深圳）有限公司 | Database volume computational methods and device |
| WO2018127116A1 (en) * | 2017-01-09 | 2018-07-12 | 腾讯科技（深圳）有限公司 | Data cleaning method and apparatus, and computer-readable storage medium |
| CN108287835A (en) * | 2017-01-09 | 2018-07-17 | 腾讯科技（深圳）有限公司 | A kind of data clearing method and device |
| US11023448B2 (en) * | 2017-01-09 | 2021-06-01 | Tencent Technology (Shenzhen) Company Limited | Data scrubbing method and apparatus, and computer readable storage medium |
| CN108287835B (en) * | 2017-01-09 | 2022-06-21 | 腾讯科技（深圳）有限公司 | Data cleaning method and device |
| CN110825413A (en) * | 2019-11-04 | 2020-02-21 | 江苏金智教育信息股份有限公司 | Database upgrading method and device and application deployment upgrading method and device |
| CN111400321A (en) * | 2020-03-23 | 2020-07-10 | 上海新炬网络技术有限公司 | Method for automatically recycling high water level based on ORAC L E database |
| CN111400321B (en) * | 2020-03-23 | 2023-05-26 | 上海新炬网络技术有限公司 | ORACLE database-based method for automatically recovering high water level |
| CN113468167A (en) * | 2020-03-31 | 2021-10-01 | 中国移动通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
| CN116257531A (en) * | 2023-05-16 | 2023-06-13 | 广州图灵科技有限公司 | Database space recovery method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101493842A (en) | Method for deleting data in bulk | |
| US8832020B2 (en) | Method and system for safely transporting legacy data to an object semantic form data grid | |
| CN103995855B (en) | The method and apparatus of data storage | |
| CN104021145B (en) | The method and apparatus that a kind of mixed service concurrently accesses | |
| CN106126753A (en) | The method of increment extractions based on big data | |
| CN104615710B (en) | A kind of electronic map frame data update method | |
| US7711716B2 (en) | Optimizations for a background database consistency check | |
| CN109597850A (en) | Tobacco integrated information data mart modeling stores platform and data processing method | |
| CN104318481A (en) | Power-grid-operation-oriented holographic time scale measurement data extraction conversion method | |
| CN101067818A (en) | Data incremental backup and recovery method applied to data bank | |
| CN108228740A (en) | Electric power full-service uniform data centre data analysis domain comparing tool | |
| CN102982186B (en) | Based on maintaining method and the system of the range partition table of oracle database system | |
| CN105279158A (en) | Hotel price ordering method based on complex query condition | |
| CN107016123A (en) | File management method and terminal device | |
| CN103064908B (en) | A kind of method by the quick duplicate removal list of internal memory | |
| CN115794929B (en) | Data management system and data management method for data marts | |
| CN101593197B (en) | Method for processing mass data based on SQL like function of file | |
| CN101692641B (en) | High effective HLR/AUC data daily auditing method | |
| CN110472854A (en) | A kind of marketing management system and method towards multi-product by all kinds of means | |
| CN105868365A (en) | Hadoop-based traditional network management data processing method | |
| Canfora et al. | A case study of applying an eclectic approach to identify objects in code | |
| CN100520781C (en) | Main memory data centre data processing method | |
| CN109214906A (en) | Tax data assets classes management method and system based on the layering of big data warehouse | |
| CN100403308C (en) | SQL load mining-based automatic design method for physical database | |
| CN104573107A (en) | Network security application NoSQL database and relational database fusion interface method |
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: 20090729 |